innodb锁问题

最新推荐文章于 2022-05-27 21:32:24 发布

易方达蓝筹

最新推荐文章于 2022-05-27 21:32:24 发布

阅读量89

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_35475714/article/details/109139876

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1、锁类型

innodb存储引擎实现了如下两种标准的行级锁：

共享锁（S LocK）：允许事务读一行数据
排他锁（X Lock） : 允许事务删除或更新一行数据
如果一个事务T1获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为锁读取并没有改变行r的数据。
但如果T3想获取r的排他锁，则必须等待事务T1、T2释放行r上的共享锁

2、加锁语句

select … for update，对读取的行记录加一个X锁
select … lock in share mode，对读取的行记录加一个S锁
这两个语句必须在一个事务中，当事务提交了，锁也就释放了

3、行锁的3种算法

Record lock: 单个行记录上锁
Gap lock：间隙锁，锁定一个范围，但不包括记录本身
Next-key Lock: Gap lock +Record lock,锁定一个范围,并且锁定记录本身

Next-key Lock，例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：
(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

在MySQL中，行级锁并不是直接锁记录，而是锁索引
在如2所述创建锁时，默认采用Next-key Lock锁技术。

3.1 Next-key Lock降级为Record Lock

当查询的索引含有唯一属性时，innodb存储引擎会对Next-key Lock进行优化，将其降为Record Lock。

#建立测试表
create table t(a int ,b int,primary key(a),key(b));
insert into t select 1,1;
insert into t select 3,1;
insert into t select 5,3;
insert into t select 7,6;
insert into t select 10,8;

在事务A中，先执行select * from t where a =5 for update;
在事务B中，执行insert into t select 4;直接成功，不需要等待

3.2 Next-key Lock锁住索引范围

在事务A中，先执行select * from t where b =3 for update;
在事务B中，执行insert into t select 4,2或insert into t select 6,5会被阻塞住。
对于辅助索引b，其加上的是Next-key Lock锁，锁定范围是[1,3],但innodb存储引擎还会对辅助索引下一个键值加上gap lock，所以锁定的范围为[1,6],故在事务B中，执行下面的语句都会阻塞：
insert into t select 11,1;
insert into t select 12,2;
insert into t select 13,5;
Next-key Lock 引入主要是为了解决幻读问题

4、隔离级别的问题

脏读：脏读指的是不同事务下，当前事务可以读取到另外事务未提交的数据。
不可重复读：不可重复读指的是同一事务内多次读取同一数据集合，读取到的数据是不一样的情况。（一般由其他事务对数据进行了update、delete操作）
幻读: 是指在同一事务下，连续执行两次同样的 sql 语句可能返回不同的结果，第二次的 sql 语句可能会返回之前不存在的行。幻读是一种特殊的不可重复读问题。（一般由其他事务对数据进行了insert操作）

在 InnoDB 存储引擎中，默认的隔离级别为可重复读，SELECT 操作的不可重复读问题通过 MVCC得到了解决，而 UPDATE、DELETE 的不可重复读问题是通过 Record Lock 解决的，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。

5、锁常见应用场景

5.1 外键检查

外键插入或更新时，需要查询附表记录，此时用的就是select ... lock in share mode方式。当父表对该行执行更新、删除操作（加X锁）时，子表的操作会被阻塞，否则会导致父表、子表数据不一致。

5.2 唯一性检查

Next-key Lock机制在应用层面实现唯一性检查，如果通过索引select ... lock in share mode查询一个值，即使查询不存在，其锁定的也是一个范围，因此若没有返回任何行，那么新值的插入一定是唯一的。

5.3 丢失更新

丢失更新：是两个事务在并发下同时进行更新，后一个事务的更新覆盖了前一个事务更新的情况，丢失更新是数据没有保证一致性导致的。比如，事务A 修改了一条记录，事务B 在事务A 提交的同时也进行了一次修改并且提交。当事务A查询的时候，会发现刚才修改的内容没有被修改，好像丢失了更新。
通过select ... for update，对读取的行记录加一个X锁。