(8)MySQL进阶篇SQL优化(InnoDB锁-共享锁、排他锁与意向锁)

时间:2022-02-27 22:35:20

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锁-共享锁、排他锁与意向锁)
五个参数说明如下:
●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):允许获得该锁的事务更新或删除数据行(写锁),同时阻止其他事务取得该数据行上的共享锁和排他锁。

锁类型

共享锁(S)

排他锁(X)

共享锁(S)

兼容

冲突

排他锁(X)

冲突

冲突

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

session_1

session_2

(1)先设置事务T1提交类型为事务非自动提交。

(1)先设置事务T2提交类型为事务非自动提交。

-- 事务提交类型:0.事务非自动提交,1.事务自动提交

SET AUTOCOMMIT=0;

-- 事务提交类型:0.事务非自动提交,1.事务自动提交

SET AUTOCOMMIT=0;

(2)获取了商品品牌表数据行ID=1上的共享锁。

(2)获取了商品品牌表数据行ID=1上的共享锁。

-- 获取ID=1品牌行共享锁

MySQL [(none)]> BEGIN;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR SHARE;

+----+--------+

| ID | Name   |

+----+--------+

|  1 | 荣耀   |

+----+--------+

1 row in set (0.00 sec)

-- 获取ID=1品牌行共享锁

MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR SHARE;

+----+--------+

| ID | Name   |

+----+--------+

|  1 | 荣耀   |

+----+--------+

1 row in set (0.00 sec)

(2)获取了商品品牌表数据行ID=1上的排他锁。此时该命令会一直处于等待状态并且最终超时。也就是说,共享锁和排他锁不兼容。

-- 获取ID=1品牌行排他锁

MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR UPDATE;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

(2)在当前会话提交或者回滚事务。

-- 提交事务

MySQL [(none)]> commit;

Query OK, 0 rows affected (0.00 sec)

(3)再获取了商品品牌表数据行ID=1上的排他锁。

-- 获取ID=1品牌行排他锁

MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR UPDATE;

+----+--------+

| ID | Name   |

+----+--------+

|  1 | 荣耀   |

+----+--------+

1 row in set (0.00 sec)

注意:我们在事务中使用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申请的写锁会被阻塞。
因此,意向锁是为了允许行锁和表锁能够共存,从而实现多粒度锁机制。以下是表锁(表锁与行锁都有共享锁跟排他锁)跟意向表锁兼容性:

锁类型

共享锁(S)

排他锁(X)

意向共享锁(IS)

意向排他锁(IX)

共享锁(S)

兼容

冲突

兼容

冲突

排他锁(X)

冲突

冲突

冲突

冲突

意向共享锁(IS)

兼容

冲突

兼容

兼容

意向排他锁(IX)

冲突

冲突

兼容

兼容

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

session_1

session_2

(1)先设置事务T1提交类型为事务非自动提交。

(1)先设置事务T2提交类型为事务非自动提交。

-- 事务提交类型:0.事务非自动提交,1.事务自动提交

SET AUTOCOMMIT=0;

-- 事务提交类型:0.事务非自动提交,1.事务自动提交

SET AUTOCOMMIT=0;

(2)在当前事务T1中为商品品牌表goods_brand中的数据行ID=1加上排他锁,同时会为表goods_brand加上意向排他锁。

-- 获取ID=1的品牌行排他锁

MySQL [(none)]> BEGIN;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR UPDATE;

+----+--------+

| ID | Name   |

+----+--------+

|  1 | 荣耀   |

+----+--------+

1 row in set (14.63 sec)

(2)在当前事务T2中显式加表级共享锁或者排他锁,因为意向排他锁和表级共享锁冲突,所以session_2事务T2一直等待session_1事务T1释放锁。

-- 显式加表级共享锁或者排他锁

LOCK TABLES goods.goods_brand READ;

-- 或者

-- LOCK TABLES goods.goods_brand WRITE;

阻塞...

(3)T1提交或者回滚事务。

(3)当session_1事务T1提交或者回滚事务释放锁,T2自动获取goods_brand表的共享锁。

-- 回滚

MySQL [(none)]> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> LOCK TABLES goods.goods_brand READ;

Query OK, 0 rows affected (8.09 sec)

(4)释放goods_brand表共享锁。

-- 释放共享锁

MySQL [(none)]> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

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

session_1

session_2

(1)先设置事务T1提交类型为事务非自动提交。

(1)先设置事务T2提交类型为事务非自动提交。

-- 事务提交类型:0.事务非自动提交,1.事务自动提交

SET AUTOCOMMIT=0;

-- 事务提交类型:0.事务非自动提交,1.事务自动提交

SET AUTOCOMMIT=0;

(2)在当前事务T1中为商品品牌表goods_brand加上了意向排他锁和数据行ID=1上的排他锁。

(2)在当前事务T2中为商品品牌表goods_brand加上了意向排他锁和数据行ID=2上的排他锁。

-- 获取ID=1的品牌行排他锁

MySQL [(none)]> BEGIN;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=1 FOR UPDATE;

+----+--------+

| ID | Name   |

+----+--------+

|  1 | 荣耀   |

+----+--------+

1 row in set (0.00 sec)

-- 获取ID=2的品牌行排他锁

MySQL [(none)]> SELECT * FROM goods.goods_brand WHERE ID=2 FOR UPDATE;

+----+--------+

| ID | Name   |

+----+--------+

|  2 | 苹果   |

+----+--------+

1 row in set (0.00 sec)

(3)提交或者回滚事务。

-- 提交事务

MySQL [(none)]> COMMIT;

Query OK, 0 rows affected (0.00 sec)

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

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