[转载]mysql锁小结

向右看齐 2022-01-12 01:13 144阅读 0赞

# 转自https://www.cnblogs.com/liang545621/p/9434016.html #

# [MySQL锁小结][MySQL] #

锁的作用：避免并发请求时对同一个数据对象同时修改，导致数据不一致。

怎么加锁：

1.事务T1在对某个数据对象R1操作之前，先向系统发出请求，对其加锁L1.

2.之后，事务T1对该数据对象R1有了相应的控制，在T1释放L1之前，其它事务不能修改R1.

锁类型：

1.排它锁（X）。

2.共享锁（S）。

通常的锁范围：

1.全局锁（global lock)。

2.表锁（table lock）。

3.行锁（row lock）。

InnoDB行锁范围（粒度）：

1.record lock.  单个记录上的锁

2.gap lock.  间隙锁，锁定一个范围，但不包含记录本身

3.next-key lock = record lock + gap lock.   锁定一个范围，并且锁定记录本身

Record Lock 总是会去锁住索引记录，如果innodb存储引擎表在建立的时候没有设置任何一个索引，而且查询的时候没有使用到索引，那么这时就会导致表锁。  
Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，innodb对于行的查询都是采用这种锁定算法。例如一个索引有10,11,13,20这4个值，那么该索引可能被Next-Key Locking的范围为：  
(- &，10\]  
(10,11\]  
(13,20\]  
(20,+ &)

采用Next-Key Lock的锁定技术称为Next-Key Locking。这种设计的目的是为了解决幻读（Phantom Problem）。利用这种锁定技术，锁定的不是单个值，而是一个范围。

注：当查询的索引含有唯一属性时，innodb存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即锁住索引记录本身，而不再是范围。

注意：  
对于唯一索引，其加上的是Record Lock，仅锁住记录本身。但也有特别情况，那就是唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么加锁的情况依然是Next-key lock。  
  
对于辅助索引，其加上的是Next-Key Lock，锁定的是范围，包含记录本身。  
另外如果使用相等的条件给一个不存在的记录加锁，innodb也会使用Next-key lock  
  
特别注意：  
innodb存储引擎是通过给索引上的索引项加锁来实现，这意味着：只有通过索引条件检索数据，innodb才会使用行锁，否则，innodb将使用表锁。当然这种说法是在表没有主键或者没有任何索引的情况下。如果一个表有主键，没有其他的索引，检索条件又不是主键，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

RC级别下，只有record lock，没有next key lock。

RR级别下，除非primary key或unique key是record lock，其余都是next key lock。

加锁对数据库的影响：

1.锁等待，锁L1锁定某个对象R1，锁L2等待该锁释放，如果不释放，会一直等待，或者达到系统预设的超时阈值后告错（回滚整个事务或只回滚当前SQL）。

2.死锁，锁资源请求产生了回路，例如：L1等待L2释放，L2等待L3释放，L3等待L1释放，死循环。

**MyISAM锁**

默认是表锁，读写互斥，仅只读共享。

读锁，LOCK TABLE user READ,自身只读，不能写；其他线程仍可读，不能写。多个线程都可提交read lock。

写锁，LOCK TABLE user WRITE,自身可读写；其他线程完全不可读写。

写锁优先级高于读锁。

SELECT自动加读锁（共享锁）。

其他DML、DDL自动加写锁（排他锁）。

释放锁，UNLOCK TABLES。

特例：单线程往MyISAM表最后空闲位置串行写入新数据不被锁（而写入中间的空洞位置还是会加锁的）。

**InnoDB锁**

默认是行锁（row lock）。

InnoDB是通过在索引记录上加锁，实现行锁。

因此，没有索引时就无法实现行锁，而升级成全表记录锁，等同于表锁。

锁类型：

1.共享锁。

2.排他锁。

3.意向锁，InnoDB特有，加载在表级别上的锁。

**其他锁**

全局锁(1.global read lock,2.query cache lock.)

MDL表锁。

自增互斥量（mutex），用来管理Auto-increment

InnoDB自旋锁，spinlock。

**全局锁**

global read lock

加锁：FTWRL,FLUSH TABLES WITH READ LOCK。

关闭实例下的所有包，并加上全局读锁，防止被修改，直到提交UNLOCK TABLES。

一般用于备份，mysqldump、xtrabackup都会发起。

xtrabackup时可分开备份InnoDB和MyISAM，或者不执行 --master-data。

query cache lock

全局query cache锁（mutex），最好关闭query cache。

对QC中的数据有更新时，都会引发query cache lock。

状态：watiting for query cache lock。

关闭query cache：

query\_cache\_type=0 （实例启动前设置）

query\_cache\_size=0。

**MDL锁**

MDL,meta data lock

事务内的表级锁

5.5开始引入

5.6.6前，事务开启后，会锁定表的meta data lock，其他会话对表有DDL操作时，均需等待MDL释放后方可继续。

5.6.6后，不再阻塞其他会话执行DDL，但原来的会话再次访问数据表时，会有error提示：Table definition has changed,please retry transaction

超时阈值定义：lock\_wait\_timeout。

**自增锁**

其实是个轻量级的互斥量（MUTEX）

相关选项 innodb\_autoinc\_lock\_mode

1，默认值，可预判行数时使用新方式，不可预判时仍旧使用表锁，会造成autoinc列自增空洞，不过影响很小。

0，即沿用旧的表级锁模式，每次请求都会等待表锁，不过也非常快。不会影响整个事务，只影响当前的INSERT语句。

2，直接全部使用新方式，不安全，不适合replication环境。

**InnoDB spin lock，自旋锁**

保护共享资源而提出的一种锁机制，和互斥锁类型，任何时刻下都只能有一个持有者，控制事务并发时的CPU时间片分配。

用于控制InnoDB内部线程调度而生的轮询检测。

InnoDB\_spin\_wait\_delay，控制轮询间隔，默认6秒。

事务并发非常高，CPU忙不过来的时候，事务处于sleep状态，spin round可能也会很高。

show engine innodb status

Mutex spin waits 5970888,rounds 19812448,OS wait 375285

**InnoDB锁之共享锁**

共享锁，不允许其他事务修改被锁定的行，只能读。

SELECT ...LOCK IN SHARE MODE。

不在事务中的SELECT 是一致性非锁定读，不加锁。

**InnoDB锁之排他锁**

对一行记录进行DML时，需至少加上排它锁。

锁范围视情况而定，可能是record lock、next-key lock，或者可能只有gap lock。

执行DML，或者SELECT ...FOR UPDATE。

**InnoDB锁之意向锁**

IS，事务T想要获得表中某几行的共享锁。

IX，事务T想要获得表中某几行的排它锁。

意向锁是加载在数据表B+树结构的根节点，也就是对整个表加意向锁。

意向锁的作用：避免在执行DML时，对表执行DDL操作，导致数据不一致。

**InnoDB锁**

转载于:https://www.cnblogs.com/infaaf/p/10524450.html

[MySQL]: https://www.cnblogs.com/liang545621/p/9434016.html

[转载]mysql锁小结

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL 表锁和行锁机制-转载

相关 MySQL日志小结

相关 [转载]MySQL各类SQL语句的加锁机制

相关 [转载]MySQL锁机制

相关 MySQL 学习小结

相关 Redis分布式锁，转载学习

相关 [转载] Redisson分布式锁介绍

相关 Mysql索引小结

相关 [转载]mysql锁小结

相关 Mysql数据库小结