Mysql行锁、表锁 (2)—mysql进阶（六十九)

前面说了解决并发事务访问有两种解决办法，一种用mvcc的版本链解决，读

的时候用版本链readView控制，写的时候加锁。一种是读写都加锁，比如只允许读取后数据的银行业务。锁又分为共享锁（s锁）和排它锁（x锁），锁的颗粒度分为表锁和行锁，所以当向上表的排他锁的时候，必须里面的行没有上x锁或者s锁，当然不是遍历所有行，于是在上行锁的时候，会有一个is和ix的锁，代表当前表上了行锁。

行锁、表锁

我们主要说的是innoDB存储引擎的锁，其他存储引擎也对锁支持，但是不一样，我们简单介绍下。

对于myISAM，memory，merge这些存储引擎来说，他们只支持表级锁，而且都不支持事务，所以这些存储引擎的锁一般只针对当前会话。比方说在session1中对一个表执行select操作，就相当于对这个表级别的加了个s锁，当session2要执行update操作，需要等session1的事务提交完，释放s锁，才能update给表上x锁。

（注意：因为myISAM，memory，merge这些存储引擎同一时刻只允许一个会话对表进行写的操作，所以这些存储引擎好用在只读，或者大部分读的操作或者单用户的场景。另外myISAM有一个concurrent inserts可以在读取的同时提升新增的速度）

innoDB存储引擎的锁

innoDB存储引擎即支持表锁，也支持行锁，表锁的颗粒度大，如果因为修改几条数据，而锁住整个表，这样肯定会影响性能，所以有行锁。

innoDB中表级别的锁

表级别的s锁和x锁，其实在对某个表select，update，delete，select并不会对这个表上s锁或者x锁。

另外，在对表执行一些alert table，drop table这类ddl语句时，其他事务对这个表并发执行的select，update，delete，select会发生阻塞，同理，其他事务在增删查改的时候，如果另一个事务执行ddl语句也会阻塞，但这不是表级别的锁造成的，实际是通过service层一种元数据锁（metadata locks，简称mdl）来实现的，一般情况下不会用innoDB提供的表级别锁。

其实这些表级别的锁非常鸡肋，只有一些特殊情况下会用到，比方说崩溃恢复的过程中。不过也可以手动获取，比方在autocommit=0，innodb_table_locks=1时，手动获取innoDB存储引擎表t的s锁和x锁可以这么写：

Lock table t read：innoDB存储引擎会对表t加表级别的s锁。

Lock table t write：innoDB存储引擎会对表t加表级别的x锁。

不过尽量避免手动获取表级别的锁，他们并不会提供什么额外的保护，反而还会降低并发能力。innoDB提供了颗粒度更细的行锁。

表级别的IS锁，IX锁

表级别的is锁和ix锁主要功能就是为了在给表级别上锁的时候，判断行锁是否存在而准备的，前面已经详细说过，就不赘述了。

表级别的AUTO-INC锁

在mysql过程中，我们可以为某个列添加auto_increment，之后插入记录，这个值就可以不写值，字节可以自动递增（这些默认大家都知道）。

正常都是id来让他自增，意味着插入的时候不需要给id赋值，系统实现它自增的原理主要两个方面：

1、采用auto_inc锁时候，也就是插入语句的时候增加一个auto_inc锁，然后为每条待插入auto_increment修饰的列分配一个值，在该语句执行完之后，在吧auto_inc锁释放。这样可以锁的过程中，其他事务阻塞，保证自增的值是连续的。

如果我们插入语句的时候不能确定插入多少值，比方说insert。。。select。。。一般使用auto_inc锁为auto_increment修饰的列生成对应的值。

注意重点：这个auto_inc是对单个插入语句上锁的，插入语句执行完就会释放，和之前说的事务执行完吧锁释放是不一样的。

2、采用一个轻量级的锁，在插入语句生成自增值的时候获取这个轻量级的锁，然后本次插入语句需要用到auto_increment列的值，就把该轻量级别的锁释放，并不需要等待整个插入语句执行完才释放锁。

如果我们在插入之前就知道插入多少数据，这时候就可以采用轻量级的锁对auto_increment列进行赋值，这样方式可以避免锁表，提升插入性能。

innoDB提供了系统变量innodb_autoinc_lock_mode来控制上诉两种方法哪种来auto_increment修饰的列进行赋值，当值为0的时候，一律采用auto_inc锁。当值为2的时候，一律采用轻量级的锁。当值为0的时候，两种方式混着来，也就是上面说的，确定插入数量的时候用轻量级别的锁，如果不确定，就用auto_inc来锁住表。不过当值为2的时候，会造成不同事务的插入值会交叉，主从复制的场景下是不安全的。

innoDB中的行级锁

好了，上面的铺垫了解之后，接下来我们才能读本篇文章的重点。

行锁，也称为记录锁，顾名思义就在记录上加锁。innoDB吧行锁分成了各种类型，换句话说，对同一条记录加锁，如果类型不同，那么起到的效果也是不同的。为了故事的顺利发展，我们还是吧之前mvcc用到的表在贴一遍。

CREATE TABLE hero (

number INT,

name VARCHAR(100),

country varchar(100),

PRIMARY KEY (number)

) Engine=InnoDB CHARSET=utf8;

INSERT INTO hero VALUES

(1, 'l刘备', '蜀'),

(3, 'z诸葛亮', '蜀'),

(8, 'c曹操', '魏'),

(15, 'x荀彧', '魏'),

(20, 's孙权', '吴');

我们吧数据取出来，隐藏列先省略：

Number列： 1 ， 3， 8 ， 15， 20

Name列：l刘备，z诸葛亮，c曹操，x荀彧，s孙权

Country列：属，属，魏，魏，吴

接下来看看哪些行锁：

Record locks：

我们前面提到的记录锁就是这种类型，也就仅仅吧一条记录锁上，于是这种记录锁 就叫做正经记录锁，官方名称叫：lock_rec_not_gap。比方说我们吧number为8的记录加上一个正经记录锁。

Record locks是有s锁和x锁之分，当给number为8的记录上了s锁，则可以访问，不可以获取x锁的记录锁。当给number为8的记录上了x锁，则其他事物即不可以获取number为8的s锁，也不可以获取number为8的x锁。

Gap locks：

前面说过在repeatable read的隔离级别下，解决幻读的方式有两种，一个是mvcc方式，一种是读写都加锁。但是在读锁的时候有个问题，就是幻读的时候，哪些幻读的记录不存在，我们无法给那些不存在的数据加上record locks。所以为了解决这个问题，innoDB提出了gap locks的锁，官方称为lock_gap，我们可以称为gap锁。比方我们吧number为8的值加个gap锁。

当number为8的值加gap锁后，因为这不允许别的事务在number为8的记录前的间隙插入新的数据，其实就是number【3，8】之间不允许插入新的数据。比方另一个事务想插入number为4的数据，这时候则会阻塞插入操作，直到拥有gap锁的事务提交后，才可以重新被插入。

但是gap锁仅仅是为了防止幻读提出来的，如果你对一条记录加了gap锁，并不会限制其他事务继续加gap锁或者record lock锁。

这样可以防止在gap锁记录前面的区间插入，那么大number为20后面的区间如何防止插入呢，前面我们说过有两条伪数据：

Infimum记录：表示页面中小数字。

Supremum记录：表示页面中大数字。

为了实现组织其他事务插入（20，+∞）的区间新纪录，我们可以给索引后一条记录，也就是number为20那条记录所在页面的supremum加个gap锁。

Next-key locks：

加入我们想锁住那条数据，又想把这个数据前的间隙禁止插入，这时候innoDB提出了next-key locks的锁，官方名称为lock_ordinary,简称next-key锁，如果给number为8的记录加一个next-key锁，则就是record 锁和 gap锁的合体。

Insert intention locks：

我们说了事务在插入之前，需要判断一下是不是被别的事务加了gap锁，如果有的话，插入时候需要等待，直到gap锁的事务提交完毕。但是innoDB规定在事务等待的时候需要在内存中生成一个锁结构，表明事务想在某个间隙中插入新纪录。这种锁结构就叫insert intention locks，官方类型名称就叫 lock_insert_intention,我们也可以叫做 插入意向锁。

比如我们现在想number为8的记录加一个 插入意向锁：

当t1事务 获取了gap锁之后，就会阻塞其他事务插入，这时候t1 的is_waiting就是true。

T2和t2事务的is_waiting如果获取的是插入意向锁，这时候的is_Waiting就是false。

一直到t1的事务提交之后，t2和t3的事务才会按顺序执行插入数据，而且插入意向锁不会阻塞别的事务获取该记录上任何类型的锁（感觉有点鸡肋，可能就是记录插入顺序的）。

隐式锁

我们前面说过一个事务执行insert操作的时候，如果插入的间隙被其他事务上了gap锁，则本次insert会被阻塞，并且会在该事务加上一个 插入意向锁，否则一般insert是不加锁的。

那么如果一个事务首先插入一条数据(此刻该事务并没有关联锁结构)，然后另一个事务：

立即使用select。。。lock in share mode语句读取这条数据，也就是要获取这条记录的s锁，或者使用select。。。for update 语句读取这条记录，也要获取这条记录的x锁，该咋办？

这种情况如果发生了，那说明结果可能会是 脏读问题。

立即修改这条数据，也就是获取这条记录的x锁，该咋办？

这种情况如果发生了，那说明结果可能会是 脏写问题。

这时候我们说过的事务id就可以解决这个问题了，我们吧主键索引和二级索引的情况分开看：

主键索引的情况：对于主键索引来说，数据列有一个隐藏的trx_id，该列记录着后改动记录的事务id。如果其他事务想对这个记录加一个s锁或者x锁，首先会看下该记录的trx_id隐藏列代表的事务是否是当前活跃事务，如果是，则会给他上个x锁，给他的is_Waiting为false，然后给自己进入等待状态，is_waiting为true。

二级索引的情况：因为二级索引本身没有trx_id，但他的page header有一个page_max_trx_id属性，这个就是改动当前记录的大事务id，如果这个page_max_trx_id属性小于当前活跃的id，说明该页面对修改的事务已经提交了，否则需要需要定位到对应的二级索引记录，然后回表找到对应的主键索引记录，然后重复主键索引的情况。

通过上面的情况，一个事务对新插入的记录可以不显示加锁，但是因为事务id的存在，所以给他加了个隐式锁。别的事务想访问的时候，因为隐式锁的存在，会给当前事务加个is_waiting，然后让自己的事务进入等待状态。

innoDB锁的内存结构

我们前面说过，一条记录加锁的本质，就是内存中创建锁的结构与之关联，那么一个事务对多条记录加锁，就要创建多个锁结构吗？比方说t1执行下面这个语句，

# 事务T1

SELECT * FROM hero LOCK IN SHARE MODE;

很显然这个语句要为hero表中的所有记录加锁，那怎么可能生成那么多表结构呢，于是对不同记录加锁时，如果满足一些条件，则可以吧这些记录放在同一个锁结构里。

1. 在同一个事务中进行加锁操作。
2. 被加锁的记录在同一个页面。
3. 加锁的类型是一样的。
4. 等待的状态一样的。

当满足上面这些条件，就放在同一个索结构，说了那么久的索结构，那锁结构是什么组成的呢？

1. 锁所在的事务信息：不论是 表锁 还是 行锁，都是在事务里进行的，这里就记录着事务的信息（一个地址值，指针）。
2. 索引信息：对于行锁来说，需要记录一下在哪个索引。
3. 表锁、行锁信息：表锁则记载着哪个表。行锁则记载着三个重要信息，space id表示所在表空间，page number表示记录所在页号，n_bits对于行锁来说，一条记录对应一个比特位，一个页面包含多个记录，用不同比特位区分到底那条记录加了锁。为此在行锁的末尾加了一堆比特位，这个n_bits就代表用了多少比特位。
4. Type_mode：这是32位的数，被分为lock_mode，lock_type和rec_lock_type三个部分。Lock_mode锁的模式，占用低4位，可选的值有lock_is，也就是共享意向锁is锁，lock_ix，代表独占意向锁ix锁，lock_S代表共享锁，s锁，lock_X代表独占锁，x锁，lock_Auto_inc代表auto_inc锁。（注意：前面的都是表级锁的模式，而lock_s和lock_x可以行锁也可以表级锁）

Lock_type锁的类型，占用第5~8位，目前只有5和6位被使用，一个是lock_table，代表表锁，lock_rec，代表行锁。

Rec_lock_type行锁的类型，只有在lock_type为lock_rec行锁的时候，这个字段才会分为更多的类型。

Lock_opdinary：表示next-key锁。

Lock_gap：当第10个比特位是1 的时候，表示gap锁。

Lock_rec_not_gap：也就是当11个比特位是1的时候，表示record 锁，行锁，常用锁。

Lock_insert_intention：当12个比特位是1的时候，表示插入意向锁。

1. 其他信息：为了更好的管理锁的结构而设计了很多hash表和链表等。
2. 一堆比特位：如果是行锁结构，该结构末尾还放了一堆比特位，比特位的数量由上面提到的n_bits属性表示的。我们前面说到innoDB表结构的时候说过，页面每条 记录头信息 中都包含heap_no属性，伪记录infimum的heap_no为0，supermum的heap_no为1，之后每插入一条数据，heap_no值就增1。锁结构的后一个比特位就对应着一个页面的记录，一个比特位映射一个heap_no，不过为了编码方便，映射有点怪，不过只要记得一个比特位映射页面的一条记录。

我们可以举个例子说明一下，比方说现在T1和T2两个事务对hero表中的记录加锁，hero表中的记录太少，假设这些记录都存储在所在表空间号为67，页号为3的页面上，那么：

T1想对number值为15的这条记录加 s型的正常记录锁，在对记录加锁之前，需要先加表级别的is锁，也就是生成一个表级锁的内存结构。接下来我们主要分析行锁的过程：

事务T1要进行加锁，所以锁结构的 锁所在事务信息 指的就是T1。

直接对聚簇索引进行加锁，所以索引信息指的其实就是primary索引。

由于是行锁，所以接下来记录三个重要信息：

SpaceId：表空间号为67。

Page number ：页号为3.

N_bits：我们现在hero插入了五条数据，但是在初始分配比特位时会多分配一些，这主要是为了之后新增记录时候不用频繁分配比特位。其实计算n_bits有一个公式：

N_bits = （1+（（n_recs+lock_page_bitmap_margin）/8））*8

其中n_recs指的是当前页面中一共多少条记录(算上伪记录和在垃圾链表中的记录)，比方说hreo现在有7条记录（5条真实记录加2条伪记录），所以n_recs记录就是7，lock_page_bitmap_margin是一个固定的值64，所以本次n_bits的值就是：

N_bits = （1+（（7 + 64）/8））*8 = 72

Type_mode是由三个部分组成：

Lock_mode，这是对记录加s锁，它的值就是lock_s。

Lock_type，这是对记录进行加锁，也就是行锁，所以它的值就是lock_rec。

Rec_lock_type，这是对记录加的record 锁，也就是类型为lock_Rec_not_gap锁。另外由于当前没有其他事务对该记录进行加锁，所以应当获取到锁，也就是lock_wait代表二进制应该是0。

综上所述，此次加锁的type_mode的值应该是：

Type_mode = locks | lock_Rec | lock_rec_not_gap

2 | 32 | 1024 = 1058

综上所谓，t1的number值为5的记录加锁生成的锁结构如下：

锁所在的事务信息：T1

索引信息：primary

表锁、行锁：space id 67，page number 3，n_bits 72.

Type_mode：1058

其他信息：

一堆比特位：00100000000..

T2想对number值为3，8，15这三条记录加x型的next-key锁，加之前，先给表加ix锁，我们这里还是着重看行锁。

现在T2要为3条记录加锁，number为3，和8的两条记录由于没有其他事务加锁，所以可以成功获取这条记录的x型next-key锁，也就是生成索结构is_waiting = false。但是number为15的记录已经被T1加了s型正经record锁，所以T2不能获取到15的x型next-key锁，也就是生成锁的结构is_waiting为true。因为等待状态不相同，所以这时候会生成两个锁结构。所以这时候属性如下：

事务T2要进行加锁，所以锁结构的 锁所在事务信息 指的就是T2.

直接对聚簇索引进行加锁，所以索引信息指的就是primary索引。

由于是行锁，所以接下来需要记录三个重要信息：

Space id为67。

Page number：页号为3.

N_bits：此属性生成策略同T1中一样，该属性的值为72.

Type_mode由三个部分组成：

Lock_mode，这是对记录加x锁，这是lock_x。

Lock_type，这是对记录加锁，也就是行锁，所以他的值是lock_rec。

Rec_lock_type，这是对行记录的类型，next-key锁，也就是类型为lock_ordinary的锁。

不同属性如下：

Number为3和8记录生成的索结构如下：

Type_mode值：由于可以获取到锁，所以is_waiting为false，也就是lock_wait代表二进制为0，所以：

Type_mode = lock_X|lock_Rec|lock_opdinary

也就是

Type_mode = 3 | 32 | 0 = 35

所以综上所述，T2的number为3和8的两条记录加锁生成的锁结构如下：

对应事务信息：T2

索引信息：primary

行锁，表锁：spaceid 67，page number 3，n_bits为72。

Type_mode：35

其他信息：

一堆比特位：00011000000…

Numer为15的记录加锁生成的锁结构如下：

对应事务信息：T2

索引信息：primary

行锁，表锁：spaceid 67，page number 3，n_bits为72。

Type_mode：291

其他信息：

一堆比特位：00010000000…

所以综上所述，t1先获取number为15的s型正常record锁，t2获取number为，3，8，15的x型正常record锁，共需要生成三个锁结构。

文章来源：知乎平台  原文地址：https://zhuanlan.zhihu.com/p/424593582