（8）MySQL进阶篇SQL优化（InnoDB锁共享锁、排他锁与意向锁）
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%";
五个参数说明如下：
●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_waits和InnoDB_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
以上是 （8）MySQL进阶篇SQL优化（InnoDB锁共享锁、排他锁与意向锁） 的全部内容， 来源链接： utcz.com/z/535591.html
