InnoDB中的锁-意向锁

最新推荐文章于 2024-07-11 10:00:00 发布

原创最新推荐文章于 2024-07-11 10:00:00 发布 · 667 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java

本文详细解析InnoDB数据库的意向锁机制，探讨IS与IX、X与IX、IS与IX之间的冲突案例，并解释了如何支持多粒度锁共存。通过实验演示，揭示了意向锁在行级与表级锁判断中的作用。

锁列表

共享与列排他锁
意向锁
记录锁
间隙锁
Next-Key锁
插入意向锁
AUTO-INC锁

这次我们只来讨论和实验意向锁。

意向锁

InnoDB意向锁是为了支持多粒度锁共存而设计的，意向锁是一种特殊的行锁。在取得行锁之前需要先获取表的意向锁。
意向锁分为两类：意向共享锁和意向排他锁：

意向共享锁IS：表示事务想要对表中的行设置共享锁。
意向排他锁IX：表示事务想要对表中行设置排他锁。

意向锁主要是辅助表级和行级锁冲突的判断，因为InnoDB支持行级锁，如果没有意向锁，那么判断表级锁和行级锁冲突就需要遍历所有行的行锁，有了意向锁就可以直接判断意向锁是否存在就可以判断是否有行锁了。

意向锁兼容性

在这里插入图片描述

实验

X与IX冲突

事务A：先将表中的行设置一个IX锁,事务不提交

begin;
select * from sys_user where id=17 for update;

事务B：然后执行SQL

begin;
update sys_user set name_pinyin='wangwu1' where id=17;

事务B回滚：

MySQL [employees]> update sys_user set name_pinyin='wangwu1' where id=17;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
MySQL [employees]>

show engine innodb status输出：

---TRANSACTION 126451825, ACTIVE 40 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 93, OS thread handle 139734682273536, query id 94962 192.168.1.83 root updating
update sys_user set name_pinyin='wangwu1' where id=17
------- TRX HAS BEEN WAITING 40 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 484 page no 7 n bits 328 index PRIMARY of table `employees`.`sys_user` trx id 126451825 lock_mode X locks rec but not gap waiting
Record lock, heap no 257
------------------

X与IS冲突

事务A：先将表中的行设置一个IS锁,事务不提交

begin;
select * from sys_user where id=17 for share;

事务B：然后执行SQL

begin;
update sys_user set name_pinyin='wangwu1' where id=17;

事务B回滚：

MySQL [employees]> update sys_user set name_pinyin='wangwu1' where id=17;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

锁情况：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QkfcinwY-1645429069042)(en-resource://database/1691:1)]

IS与IX冲突

事务A：先对记录增加了一个IS锁

begin;
select * from sys_user where id=17 for share;

事务B：后对记录申请一个IX锁

begin;
select * from sys_user where id=17 for update;

事务B回滚：

MySQL [employees]> select * from sys_user where id=17 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

注意

以上都是基于行进行的实验，具体的冲突矩阵大家可以使用以下SQL自行测试：

lock tables sys_user write;
lock tables sys_user read;

参考

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
https://www.infoq.cn/article/zau0ewzsdtx9zofr6c8w