认真学习MySQL中锁机制（二）

接上文​​认真学习MySQL中锁机制（一）​​我们继续学习MySQL中的锁机制。

【5】按加锁的方式划分：显示锁、隐式锁

① 隐式锁

一个事务在执行insert操作时，如果即将插入的间隙已经被其他事务加了gap锁，那么本次insert操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁，否则一般情况下insert操作是不加锁的。那如果一个事务首先插入了一条记录（此时并没有在内存生成与该记录关联的锁结构），然后另一个事务：

• 立即使用 ​​select ... lock in share mode​​语句读取这条记录，也就是要获取这条记录的S锁，或者使用 ​​select ... for update​​ 语句读取这条记录，也就是要获取这条记录的X锁，怎么办？如果允许这种情况的发生，那么可能产生脏读问题。
• 立即修改这条记录，也就是要获取这条记录的X锁，怎么办？如果允许这种情况的发生，那么可能产生脏写问题。

这时候我们前边提过的事务id又要起作用了。我们把聚簇索引和二级索引中的记录分开看一下：

• 情景一：对于聚簇索引记录来说，有一个​​trx_id​​​隐藏列，该隐藏列记录着最后改动该记录的事务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的存在，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。隐式锁是一种延迟加锁的机制，从而来减少加锁的数量。

隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时，隐式锁转化为显示锁。

InnoDB的insert操作，对插入的记录不加锁，但是此时如果另一个线程进行当前读，类似以下的用例，session 2 会锁等待session 1，那么这时如何实现的呢？

session 1

begin;
insert into student values (1,'张三','二班');

此时查看锁，如下所示会发现有一个表级别的IX意向排他锁，以及行记录级别的插入意向锁。

select * from performance_schema.data_locks dl ;

session 2

此时session1 事务未提交，执行session2 如下，会阻塞。如果session2阻塞时间久会出现​​ SQL 错误 [1205] [40001]: Lock wait timeout exceeded; try restarting transaction​​ 错误。

begin;

select * from student lock in share mode;

​​此时再次在session1查看锁：​​ 红框是session1插入时加的锁，蓝框是session2加的锁。可以看到对行记录2添加了X的排他锁，InnoDB自动为表添加了IS意向锁，获取到了行记录1的S锁，尝试获取行记录2的S锁时被阻塞。

总结隐式锁的逻辑过程如下：

• A、InnoDB的每条记录中都有一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中。
• B、在操作一条记录前，首先根据记录中的trx_id 检查该事务是否是活动的事务（未提交或回滚）。如果是活动的事务，首先将隐式锁转换为显示锁（就是为该事务添加一个锁）。
• C、检查是否有锁冲突，如果有冲突，创建锁，并设置waiting状态。如果没有冲突不加锁，跳到E。
• D、等待加锁成功，被唤醒，或者超时。
• E、写数据，并将自己的trx_id写入trx_id字段。

② 显示锁

通过特定的语句进行加锁，我们一般称之为显示加锁，例如：

# 显示加共享锁
select ... lock in share mode

# 显示加排他锁
select ... for update

【6】其他锁之：全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是：做全库逻辑备份。

全局锁的命令：

flush tables with read lock

【7】其他锁之：死锁

① 概述

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

死锁示例：

两个事务都持有对方需要的锁，并且在等待对方释放，且双方都不会释放自己的锁。

② 产生死锁的必要条件

1.两个或两个以上事务
2.每个事务都已经持有锁并且申请新的锁
3.锁资源同时只能被同一个事务持有或者不兼容
4.事务之间因为持有锁和申请锁导致彼此循环等待

死锁的关键在于：两个（或以上）的Session加锁的顺序不一致。

③ 如何处理死锁

方式一：等待，直到超时（innodb_lock_wait_timeout 默认50s）。

即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。这种方法简单有效，在InnoDB中，参数 innodb_lock_wait_timeout用来设置超时时间。

缺点：对于在线服务来说，这个等待时间往往是无法接受的。

那将此值修改短一些，比如1s/0.1s是否合适？不合适，因为容易误伤到普通的锁等待。

方式二：使用死锁检测进行死锁处理。

方式1检测死锁太过被动，InnoDB还提供了 ​​wait-for graph​​​ 算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，​​wait-for graph​​ 算法都会被触发。

这是一种较为​​主动的死锁检测机制​​​，要求数据库保存​​锁的信息链表​​​和​​事务等待链表​​两部分信息。

基于这两个信息，可以绘制 wait-for graph（等待图）

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

一旦检测到回路、有死锁，这时候InnoDB存储引擎会选择回滚undo量最小的事务，让其他事务继续执行（innodb_deadlock_detect=on表示开启这个逻辑，默认为on）。

缺点： 每个新的被阻塞的线程，都要判断是不是由于自己的加入导致了死锁，这个操作实践复杂度是O(n)。如果 100个并发线程同时更新同一行，意味着要检测 100*100=1万次，1万个线程就会有1千万次检测。

如何解决？

• 方式1：关闭死锁检测，但意味着可能会出现大量的超时，会导致业务有损。
• 方式2：控制并发访问的数量。比如在中间件中实现对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作。

进一步的思路

可以考虑通过将一行改成逻辑上的多行来减少锁冲突。比如，连锁超市账户总额的记录，可以考虑放到多条记录上，账户总额等于这多个记录的值的总和。

④ 如何避免死锁

• 合理设计索引，使业务SQL尽可能通过索引定位更少的行，减少锁竞争。
• 调整业务逻辑SQL执行顺序，避免update、delete长时间持有锁的SQL在事务前面。
• 避免大事务，尽量将大事务拆成多个小事务来处理，小事务缩短锁定资源的试卷，发生锁冲突的几率也更小。
• 在并发比较高的系统中，不要显示加锁，特别是在事务里显示加锁。如 ​​select ... for update​​​ 语句，如果是在事务里运行了 ​​start transaction​​​ 或 设置了 ​​autocommit =0​​ ,那么就会锁定所查找到的记录。
• 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免很多因为gap锁造成的死锁。

【8】锁的内存结构

我们前边说对一条记录加锁的本质就是在内存中创建一个锁结构与之关联，那么是不是一个事务对多条记录加锁，就要创建多个锁结构呢？比如：

# 事务 1
select * from user lock in share mode;

理论上创建多个锁结构没问题，但是如果一个事务要获取10000条记录的锁，生成10000个锁结构也太不可思议了。所以决定在对不同记录加锁时，如果符合下边这些条件的记录会放到一个锁结构中。

• 在同一个事务中进行加锁操作
• 被加锁的记录在同一个页面中
• 加锁的类型是一样的
• 等待状态是一样的

InnoDB存储引擎中的锁结构如下：

① 锁所在的事务信息

不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记录这个事务的信息。

此锁所在的事务信息在内存结构中只是一个指针，通过指针可以找到内存中关于该事务的更多信息，比方说事务id等。

② 索引信息

对于行锁来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。

③ 表锁/行锁信息

表锁结构和行锁结构在这个位置的内容是不同的。

• 表锁：记载着是对哪个表加的锁，还有其他的一些信息
• 行锁：记载了三个重要的信息

• Space ID：记录所在表空间
• Page Number：记录所在页号
• n_bits：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个​​n_bits​​属性代表使用了多少比特位。

n_bits的值一般都比页面中记录条数多一些，主要是为了之后在页面中插入了新记录后也不至于重新分配锁结构。

④ type_mode

这是一个32位的树，被分成了lock_mode、lock_type和rec_lock_type三个部分，如图所示：

锁的模式（lock_mode）占用低4位，可选的值如下：

• LOCK_IS（十进制的0）：表示共享意向锁，也就是IS锁
• LOCK_IX（十进制的1）：表示独占意向锁，也就是IX锁
• LOCK_S（十进制的2）：表示共享锁，也就是S锁
• LOCK_X（十进制的3）：表示独占锁，也就是X锁
• LOCK_AUTO_INC（十进制的4）：表示AUTO_INC锁。

在InnoDB存储引擎中，LOCK_IS, LOCK_IX, LOCK_AUTO_INC都算是表级锁的模式，LOCK_S 和 LOCK_X既可以算是表级锁的模式，也可以是行级锁的模式。

锁的类型（lock_type），占用5~8位，不过现阶段只有第5位和第6位被使用：

• LOCK_TABLE（十进制的16），也就是当第5个比特位置为1时，表示表级锁。
• LOCK_REC（十进制的32），也就是当第6个比特位置为1时，表示行级锁。

行锁的具体类型（rec_lock_type），使用其余的位来表示。只有在​​lock_type​​的值为​​LOCK_REC​​时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：

• LOCK_ORDINARY（十进制的0）：表示next-key锁
• LOCK_GAP（十进制的512）：也就是当第10个比特位置为1时，表示gap锁
• LOCK_REC_NOT_GAP（十进制的1024）：也就是当第11个比特位置为1时，表示正经记录锁。
• LOCK_INSERT_INTENTION（十进制的2048）：也就是当第12个比特位置为1时，表示插入意向锁。

is_waitging属性呢？基于内存空间的节省，所以把is_waiting属性放到了type_mode这个32位的数字中：

• LOCK_WAIT（十进制的256）：当第9个比特位置为1时，表示is_waiting未true，也就是当前事务尚未获取到锁，处在等待状态；当这个比特位为0时，表示is_waiting为false，也就是当前事务获取锁成功。

⑤ 其他信息

为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。

⑥ 一堆比特位

如果是行锁结构的话，在该结构末尾还放置 了一堆比特位，比特位的数量是由上边提到的​​n_bits​​​属性表示的。InnoDB数据页中的每条记录在记录头信息中都包含一个​​heap_no​​​属性，伪记录​​Infimum​​​的​​heap_no​​​值为0，​​Supremum​​​的​​heap_no​​​值为1，之后每插入一条记录，​​heap_no​​值就增1。

锁结构最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个​​heap_no​​，即一个比特位映射到页内的一条记录。

【9】锁监控

关于MySQL锁的监控，我们一般可以通过检查 innodb_row_lock 状态变量来分析系统上的行锁的争夺情况。

show status like '%innodb_row_lock%'

Innodb_row_lock_current_waits 0
Innodb_row_lock_time  278166
Innodb_row_lock_time_avg  30907
Innodb_row_lock_time_max  51003
Innodb_row_lock_waits 9

对各个状态量的说明如下：

• Innodb_row_lock_current_waits：当前正在等待锁定的数量
• ​​Innodb_row_lock_time​​ ：从系统启动到现在锁定总时间长度（等待总时长）
• ​​Innodb_row_lock_time_avg​​ ：每次等待所花平均时间（等待平均时长）
• Innodb_row_lock_time_max ：从系统启动到现在等待最常的一次所花的时间
• ​​Innodb_row_lock_waits​​ ：系统启动后到现在总共等待的次数（等待总次数）

MySQL把事务和锁的信息记录在了​​information_schema​​​库中，涉及到的三张表分别是​​INNODB_TRX​​​, ​​INNODB_LOCKS​​​和 ​​INNODB_LOCK_WAITS​​。

MySQL5.7及之前，可以通过​​information_schema.INNODB_LOCKS​​查看事务的锁情况，但只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况。

MySQL8.0删除了​​information_schema.INNODB_LOCKS​​​, 添加了​​performance_schema.data_locks​​​ ，可以通过​​performance_schema.data_locks​​​ 查看事务的锁情况。和MySQL5.7及之前不同，​​performance_schema.data_locks​​ 不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁。

同时，​​information_schema.INNODB_LOCK_WAITS​​​ 也被 ​​performance_schema.data_lock_waits​​所代替。

MySQL5.7下的锁关系可以参考博文：​​MySQL - 锁等待超时与information_schema的三个表​​

查看正在被阻塞的事务（SQL）：

SELECT * from information_schema.INNODB_TRX it

查看加锁的表：

# 如下给表user添加读锁，表town添加写锁
lock tables user read,town  write;

show open tables where In_use=1;

# 释放锁
unlock tables;

查看事务加锁

select * from performance_schema.data_locks dl ;

查看事务锁等待

select * from performance_schema.data_lock_waits dlw ;

查看元数据锁(MDL锁)

select * from performance_schema.metadata_locks ml

查看表锁定情况

show status like 'Table_locks_immediate' 
show status like 'Table_locks_waited'

• Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁其值+1。
• Table_locks_waited ： 出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值+1），此值高则说明存在着较严重的表级锁争用情况。