1.锁的分类

锁(Locking)是数据库在并发访问时保证数据一致性和完整性的主要机制。之前MyISAM锁章节已经讲过锁分类,而InnoDB锁按照粒度分为锁定整个表的表级锁(table-level locking)和锁定数据行的行级锁(row-level locking)
●表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
●行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。

1.1 InnoDB的行锁概述

InnoDB可以通过SHOW STATUS命令来检查InnoDB_row_lock状态变量用作分析系统上的行锁的争夺情况:
SHOW STATUS LIKE 'innodb_row_lock%';
(8)MySQL进阶篇SQL优化(InnoDB锁-共享锁、排他锁与意向锁)-LMLPHP
五个参数说明如下:
●Innodb_row_lock_current_waits:当前正在等待锁的数量。
●Innodb_row_lock_time:从系统启动到现在锁定总时间长度。
●Innodb_row_lock_time_avg:每次等待所花平均时间。
●Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度。
●Innodb_row_lock_waits:系统启动到现在总共等待的次数。
其中InnoDB_row_lock_waitsInnoDB_row_lock_time_avg的参数值比较高,那就说明锁争用情况比较严重,那就要注意下了!

2.共享锁与排他锁

InnoDB主要有以下两种类型的行锁:
●共享锁(S):允许获得该锁的事务读取数据行(读锁),同时允许其他事务获得该数据行上的共享锁,并且阻止其他事务获得数据行上的排他锁。
●排他锁(X):允许获得该锁的事务更新或删除数据行(写锁),同时阻止其他事务取得该数据行上的共享锁和排他锁。

共享锁和共享锁可以兼容,排他锁和其它锁都不兼容。例如,事务A获取了一行数据的共享锁,事务B可以立即获得该数据行的共享锁,也就是锁兼容;但是此时事务B如果想获得该数据行的排他锁,则必须等待事务A释放数据行上的共享锁,此种情况存在锁冲突。请看以下示例:

注意:我们在事务中使用select ... for share语句获取了数据行ID= 1上的共享锁;对于MySQL 8.0之前的版本,可以使用select ... lock in share mode命令。select ... for update语句是获取了数据行ID=1上的排他锁。

3.意向锁

InnoDB除了支持行级锁,还支持由MySQL服务层实现的表级锁(lock tables...write在指定的表加上表级排他锁)。当这两种锁同时存在时,可能导致冲突。例如,事务A获取了表中一行数据的读锁;然后事务B申请该表的写锁(例如修改表的结构)。如果事务B加锁成功,那么它就应该能修改表中的任意数据行,但是A持有的行锁不允许修改锁定的数据行。显然数据库需要避免这种问题,B的加锁申请需要等待A释放行锁。
那么如何判断事务B是否应该获取表级锁呢?首先需要看该表是否已经被其他事务加上了表级锁,然后依次查看该表中的每一行是否已经被其他事务加上了行级锁。这种方式需要遍历整个表中的记录,效率很低。所以为了解决这个问题,InnoDB引入意向锁(Intention Locks):
●意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
●意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
注意:这两种意向锁都是表锁
这个时候,事务A必须先申请该表的意向共享锁,成功后再申请数据行的行锁。事务B申请表锁时,数据库查看该表是否已经被其他事务加上了表级锁;如果发现该表上存在意向共享锁,说明表中某些数据行上存在共享锁,事务B申请的写锁会被阻塞。
因此,意向锁是为了允许行锁和表锁能够共存,从而实现多粒度锁机制。以下是表锁(表锁与行锁都有共享锁跟排他锁)跟意向表锁兼容性:

从表格可见表锁跟意向锁之间,只有共享锁兼容,而意向锁跟意向锁之间是互相兼容的。
注意:InnoDB表存在两种表级锁,一种是lock tables语句手动指定的锁,另一种是由InnoDB自动添加的意向锁。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁。请看以下两个示例。
示例1:

由此可验证,意向排他锁(IX)跟表级共享锁(S)正如表格中所示是冲突的,其他类型锁兼容这里就不一一示范了,大家可自行验证。
示例2:

由上述示例可以知道,事务T1和T2同时获得了商品品牌表goods_brand上的意向排他锁,以及不同数据行上的行级排他锁,而且这两种意向锁并没有发生冲突,由此可见意向锁跟意向锁之间是互相兼容的。InnoDB通过行级锁,实现了更细粒度的控制,能够支持更高的并发更新和查询。还有一点是InnoDB行锁是通过给索引上的索引项加锁来实现的,当有明确指定的主键或者索引时候,才是行级锁,否则就是表级锁!在下一个章节,我将会介绍这个知识点。

参考文献:
深入浅出MySQL大全
通过各种简单案例,让你彻底搞懂MySQL中的锁机制与MVCC

05-07 22:57