[转载]MySQL锁机制

╰半橙微兮° 2022-09-02 05:17 146阅读 0赞

> 本文系转载，方便自己整理和查看，源文地址为 https://www.cnblogs.com/leohahah/p/8862216.html

本文参考自MySQL官网5.6版本参考手册的14.5.1,此小节说明MySQL的锁分类，此外还有14.5.2小节和14.5.3小节详述事务隔离级别和各SQL语句的加锁模式，后两节将单独写2篇笔记。

https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html

[https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html][https_dev.mysql.com_doc_refman_5.6_en_innodb-transaction-isolation-levels.html]

https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

https://dev.mysql.com/doc/refman/5.7/en/glossary.html\#glos\_rw\_lock

[MySQL各类SQL语句的加锁机制][MySQL_SQL]

[MySQL事务隔离级别][MySQL]

**第一部分：概述**

Myisam的锁比较容易理解，无论是读还是写都只会加表锁，表锁又分为read锁和write锁，可以使用如下方式手动加锁：

--加表锁语句（同样适用于InnoDB）：
    lock tables
        tbl_name [[AS] alias] lock_type
        [, tbl_name [[AS] alias] lock_type] ...
    lock_type:
        READ [LOCAL]
      | [LOW_PRIORITY] WRITE
    或者
    flush tables with read lock;#其实加的不是表锁而是针对所有表的一个全局读锁。
    --解表锁语句：
    unlock tables;
    --如何观察表的元数据锁:
    showopen tables [FROM db_name] [like_or_where]

Myisam的read、write表锁其实可以看做一种元数据锁，这种锁对其他存储引擎例如innodb表也可以加。

由于Myisam这样的锁机制，导致Myisam是一款读性能较好，并发写性能较差的存储引擎，本文主要讨论如今的MySQL默认存储引擎InnoDB的锁机制。

**第二部分：InnoDB锁分类**

--如何观察InnoDB锁：
    set @@global.innodb_status_output_locks=on;
    --这样show engine innodb status\G可以显示InnoDB额外的锁信息（锁太多时也无法完全显示），标准情况下只显示锁数目，不过如果请求的锁被阻塞那么标准情况下也会显示请求的锁的信息。

InnoDB没有页锁，只有**表锁**和**行锁**。

**一、InnoDB表锁有以下几种：**

InnoDB也可以使用lock tables … read/write来添加元数据表锁。（其实是取决于`innodb_table_locks参数，默认为1表示innodb可以感知mysql层的表锁`）

InnoDB支持的事务表锁有：

\*\*S ：\*\*即lock tables … read添加的S锁。

\*\*X ：\*\*即lock tables … write添加的X锁。

\*\*IS：\*\*表级意向共享锁，即表示事务有向底层资源加共享行锁的意向。如select … lock in share mode语句，在加行锁之前会在表上现加IS锁，这样可以提高锁冲突检测的效率，同时也可以避免事务在表级添加会使其他事务行锁失效的表级锁。

\*\*IX：\*\*表级意向独占锁，即表示事务有向底层资源加独占行锁的意向。一般来说delete、update语句和select … for update语句都会在加行锁之前先加表级IX锁，除非未用到索引（此时直接加表级X锁）。

**表锁的兼容性图：**

![img][]

\*\*此外表级锁还有一种比较特殊的锁：\*\***AUTO-INC Locks**

这种锁只在向自增主键中插入记录时出现，由于自增主键在MySQL中较为常见，因此也算是经常会遇到的锁，这种锁是为自增主键设计的，无需和以上4钟锁检测冲突。

AUTO-INC Locks的锁机制：

在向自增主键中插入记录时，其他insert事务都需要等待直到本事务的插入完成才能继续插入自增记录，注意是**插入完成**而不是**本事务完成**。这很好理解，因为需要保证自增主键的连贯性。但是如果你有超高的插入并发，那么肯定会带来性能问题。

因此InnoDB也提供了折中的方案，innodb\_autoinc\_lock\_mode参数可以控制你是否使用这种锁，如果你的自增主键不需要严格连贯而且需要更高的insert并发，那么可以禁用掉这种锁。

但是如果你做了主从复制，而且使用的是statement模式的binlog，那么禁用innodb\_autoinc\_lock\_mode后可能造成主从自增主键不一致，尤其是遇到insert … select … from table\_name;这种语句。此时需要改为row模式或mixed模式的binlog主从复制，因为row模式对SQL执行顺序不敏感，而mixed模式也会将可能影响主从复制的statement改为row模式传输。

那么最后还有个问题就是既需要超高插入并发又需要连贯自增，那该怎么办？

凉拌~

**二、InnoDB行锁有以下四种：**

Ps：四种行锁在show engine innodb status\\G里的显示分别是：

Record lock：RECORD LOCKS <rec物理位置和事务id等信息> lock_mode <锁模式：XorS> locks rec butnot gap [waiting] --如果处于等待状态就会有waiting后缀
    
    Gap lock：RECORD LOCKS <rec物理位置和事务id等信息> lock mode <锁模式：XorS> locks gap before rec
    
    Next-key lock：RECORD LOCKS <rec物理位置和事务id等信息> lock_mode <锁模式：XorS> [waiting]
    插入意向锁（Insert Intention Locks）：RECORD LOCKS <rec具体位置> lock_mode <锁模式：X/S> locks gap before rec insert intention [waiting]

示例里带waiting是因为innodb\_status\_output\_locks参数未开启时这些锁都只在请求被阻塞时会显示，正好我是用的一个有大量锁等待的库来示例的，所以带了waiting.

**1.Record lock**

即在索引记录上加的行锁，lock\_mode分为S和X两种模式。

例如：Start transaction;SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;就会c1列的索引上添加X类型的Record lock。

– 请注意默认事务隔离级别下select for update虽然是锁定读，但是会自动提交导致锁立马释放，所以必须开启事务或者设置@@session\_autocommit=0才能看到行锁。

Record lock一定是加在索引记录上的，即便是一个没有定义主键的表，InnoDB也会创建一个隐式的聚集索引GEN\_CLUST\_INDEX，在用到此主键索引时加Record lock。

**2.Gap lock**

即间隙锁，官方定义是：Gap lock用于锁定2个索引记录之间、或第一个索引记录之前、或最后一个索引记录之后的范围，即：锁定不存在的索引记录，也因此如果要查询的索引是单列的唯一索引，那么是不会出现gap锁的（多列唯一索引除非查询条件包含了所有索引列，否则依然可能出现gap锁）。

通常我们会把Record lock和其之前的Gap lock合起来称为Next-key lock，这点在Next-key lock部分解释。Gap锁很不容易被观察到，基本都是在某些死锁情况下才能看到，一般阻塞时都是以next-key的形式出现。

之所以设计Gap lock主要是为了解决幻读问题的，参考SQL Server的键范围锁，所谓幻读就是防止同一个事务内两次读到的结果集数目不一样，其避免幻读的原理就是通过gap锁阻止在指定范围内新插入记录。

Gap锁是可以禁用的，你可以将数据库的全局隔离级别设置为read committed或者将innodb\_locks\_unsafe\_for\_binlog(未来版本会弃用)参数设置为1来禁用Gap lock，只是这样就会出现幻读，不过幻读一般并不是什么大问题，比如Oracle数据库的默认隔离级别下就无法避免幻读。如果你想禁用gap锁，请参考官网关于事务隔离级别的页面以便厘清其优劣。

另外必须要说的是：

同一个gap上的Gap lock的S和X模式效果完全一样的，就算你加了一个X模式的gap lock，其他事务也能在同一个gap上再加一个X模式的gap lock而不会阻塞，因为gap锁被设计的唯一目(only purpose)就是为了防止其他事务向gap插入数据的，或者说其唯一目的就是为了防止幻读。

mysql允许在gap上存在互斥锁的原因主要是当gap依赖的索引记录被删除时这些gap锁会被合并（这句的官网原文为：The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged）。

**3.Next-key lock**

即Record lock和Gap lock的合体，不过next-key中的gap必须是索引记录前的gap，记录之后的gap不能与这个gap之前的key合并为next-key。一个特例是如果锁定的gap范围没有上限那么会用一个名为supremum的伪上限代表无穷大，那么此虚拟key值的行锁和小于此值的gap也可以组合为一个next-key锁。所以在内存中看到supremum就要想到是这种next-key锁。

一个示例：

假设有如下表，此表无主键i列上有索引：

mysql> select* from t;
    +------+
    | i    |
    +------+
    |   1 |
    |   4 |
    |   5 |
    |   6 |
    |   9 |
    +------+
    mysql>set @@session.autocommit=0;
    mysql>set @@global.innodb_status_output_locks=on;
    mysql> select* from t where i between6 and 11 for update;
    
    # show engine innodb status\G 的事务部分显示的加锁模式如下：
    ---TRANSACTION598438, ACTIVE5 sec
    3 lock struct(s), heap size1136,5 row lock(s)--显示一共有5个行锁（lock strcuts的含义我至今不明白，先放着）
    MySQL threadid 163, OS thread handle140513269520128, queryid 538 localhost root
    TABLE LOCK table `test`.`t` trxid 598438 lock mode IX --一个表级意向锁，没啥问题
    RECORD LOCKS spaceid 686 page no4 n bits80 index IX_1 of table `test`.`t` trxid 598438 lock_mode X --某位置有如下3个next-key锁
    Record lock, heap no1 PHYSICAL RECORD: n_fields1; compactformat; info bits0
     0:len 8;hex 73757072656d756d; asc supremum;; -- 这是一个包含了上限supremum的next-key锁
     
    Record lock, heap no5 PHYSICAL RECORD: n_fields2; compactformat; info bits0
     0:len 4;hex 80000006; asc     ;;
     1:len 6;hex 000000000825; asc     %;;
     
    Record lock, heap no6 PHYSICAL RECORD: n_fields2; compactformat; info bits0
     0:len 4;hex 80000009; asc     ;;
     1:len 6;hex 000000000826; asc      &;;
     
    RECORD LOCKS spaceid 686 page no3 n bits80 index GEN_CLUST_INDEX of table `test`.`t` trxid 598438 lock_mode X locks rec butnot gap --这里有2个GEN_CLUST_INDEX的非gap行锁
    Record lock, heap no4 PHYSICAL RECORD: n_fields4; compactformat; info bits0
     0:len 6;hex 000000000826; asc      &;;
     1:len 6;hex 00000009219d; asc     ! ;;
     2:len 7;hex ee000001430110; asc     C  ;;
     3:len 4;hex 80000009; asc     ;;
     
    Record lock, heap no6 PHYSICAL RECORD: n_fields4; compactformat; info bits0
     0:len 6;hex 000000000825; asc     %;;
     1:len 6;hex 00000009219c; asc     ! ;;
     2:len 7;hex ed0000016b0110; asc     k  ;;
     3:len 4;hex 80000006; asc     ;;

上述5个行锁中，前3个锁定的范围是IX\_1索引上的：(5,6\]、(6,9\]、(9,supremum\]，后2个锁定的范围是GEN\_CLUST\_INDEX的6和9两个记录对应的主键。

**4.插入意向锁（Insert Intention Locks）**

这个锁也是一个InnoDB的奇葩例子，不知道大家发现没InnoDB在谈IX IS还有行锁这些锁的时候基本不用insert语句来举例，这点如果是熟悉Oracle和SQL Server的人就会很困惑，因为增删改全都是DML语句，大家加锁机制基本相似的，无非就是表级意向锁+页级or行级锁的套路，但是InnoDB不是这样！！！insert语句和delete、update完全不是一路人！！关于Insert语句的加锁模式可以参考http://www.cnblogs.com/leohahah/p/8863422.html中的INSERT说明部分。

这个锁用于表明：只要不是插入相同的index record，多个事务向同一个gap插入记录是不会冲突的。虽然插入意向锁之间不会互相阻塞，但是插入意向锁与涉及本区间的行锁们可是不兼容的，会互相阻塞，这也是造成死锁的一大主因。

Insert语句的基本加锁模式为：表级IX锁–行级插入意向锁–行级锁(行级插入意向锁释放)。

插入意向锁其实是行级别的一种意向gap锁（从show engine innodb status的输出也可以看出），既然有意向两字那么可以认定就是用于检测锁冲突的，是为在行级别获取X模式的record lock锁提前做检测。

用一个例子来解释更为明了：

--会话A执行：
    CREATE TABLE child (idint(11)NOT NULL,PRIMARY KEY(id)) ENGINE=InnoDB;
    INSERT INTO child (id)values (90),(102);
    STARTTRANSACTION;
    SELECT *FROM childWHERE id > 100FOR UPDATE;
    --会话B执行：
    INSERT INTO child (id)VALUES (101);

可以看到会话B被阻塞了，而show engine innodb status\\G看到的锁等待如下：

![img][img 1]

即insert语句想在(90,102)的gap上加个lock\_mode=X的gap锁，也就是Insert Intention Lock，但是会话A的select for update语句已经在(90,102\]的gap上添加了X模式的next-key锁，要获取的插入意向锁与此锁不兼容，于是被阻塞无法获取。

**三、Innodb内存锁**

除以上事务锁之外，innodb还有内存锁，可以分为两大类：Mutex和rw-locks，统称为latches。

*  An `s-lock` provides read access to a common resource.
 *  An `x-lock` provides write access to a common resource while not permitting inconsistent reads by other threads.
 *  An `sx-lock` provides write access to a common resource while permitting inconsistent reads by other threads. `sx-locks` were introduced in MySQL 5.7 to optimize concurrency and improve scalability for read-write workloads.

rw-lock在5.6之前只包含S和X两种模式，5.7之后新增了SX模式，其兼容机制如下：

![img][img 2]

**四、总结**

MySQL的锁机制基本就如上所示了，但是了解InnoDB锁只是初步的，还必须结合事务隔离级别的概念去判断各种SQL的具体加锁机制，因为事务隔离级别会影响SQL的默认加锁模式。

MySQL的事务隔离级别定义也是遵循ANSI SQL92标准的，不过但凡是家数据库厂商都会说自己遵循SQL92标准，而事实是早已加料加的面目全非。当然这全都是为了能够提供更好的并发性能。例如Oracle也说自己遵循SQL92标准，结果四大隔离级别只支持2个，SQL Server也说自己支持，结果又多造出来2个事务隔离级别。

同样的MySQL也提供了4大基本的事务隔离级别，不同的隔离级别下加锁机制区别很大，参考：http://www.cnblogs.com/leohahah/p/8857124.html。

[https_dev.mysql.com_doc_refman_5.6_en_innodb-transaction-isolation-levels.html]: https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html#isolevel_read-committed
[MySQL_SQL]: https://www.cnblogs.com/leohahah/p/8863422.html
[MySQL]: https://www.cnblogs.com/leohahah/p/8857124.html
[img]: /images/20220829/4b2a9b576d0c44dfa9fa70789ada925f.png
[img 1]: /images/20220829/23ca6561aa64438fafb8e1e94f9af97b.png
[img 2]: /images/20220829/91ef6a5ec4d048d8ae9ac7e5006d46a7.png