大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说（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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/6212.html
