mysql记录锁与互斥锁区别_和你聊聊MySQL锁

1.前言

在我们的日常生活中，随处可见锁的存在，比如自行车锁、电动车锁、汽车锁等。再比如商场的存货柜，持有锁的用户可以在指定的柜门中存放需要暂存的物品，其它没有锁的用户只能存放在其它柜门中或者等待你把物品取出并归还锁后才可以进行物品存放。

从上面的描述中，你很容易得出锁存在的意义就是对资源的保护，资源包括独立资源、共享资源。在程序中我们讨论最多的就是共享资源，由于其共享的特性，会带来并发问题，从而导致数据的不正确性与不完整性。

在了解为什么需要锁以及锁存在的意义之后，我们就可以一起来聊聊MySQL中的锁了。

2. MySQL锁

2.1 锁类型

共享锁

排它锁

共享锁顾名思义可以与其它锁之间共享，仅限于读锁与读锁之间，读与写之间是不共享的；排它锁顾名思义是独占，与读锁之间互斥，与写锁之间也是互斥。

共享锁与排它锁兼容性：

锁类型

共享锁

排它锁

共享锁

兼容

不兼容

排它锁

不兼容

不兼容

2.2 锁粒度

行级锁

表级锁

2.2.1 行级锁

record lock(记录锁)：锁定索引记录

gap lock(间隙锁)：锁定索引范围，不包括索引记录本身

next-key lock(记录锁 + 间隙锁)：record lock + gap lock

insert intention lock(插入意向锁)：在插入之前获取一个意向范围

2.2.2 表级锁

意向锁：在加行锁之前会先加上意向锁

自增长锁：在插入含有自增长列的记录时需要获取该锁

看到这里，想必你的内心一定是兴奋地、激动地，一下又了解了这么多有用的知识。别着急，下面还会继续带你聊聊这些锁的相关知识以及相关场景分析。

3. 表级锁

3.1 意向锁

3.1.1 意向锁分类

意向共享锁

意向排它锁

3.1.2 意向锁兼容性

锁类型

意向共享锁

意向排它锁

意向共享锁

兼容

兼容

意向排它锁

兼容

兼容

从兼容性表中可以得出一个重要的结论，那就是意向锁之间是互相兼容的。你可能又会想，既然它们之间是兼容的，那么意向锁存在意义是什么呢？

来看个问题，事务A对表中的某一行加了排它锁，之后事务B请求对整张表加锁，事务B会遍历表查看是否存在互斥锁，如果存在，事务B等待锁释放，如果不存在，事务B加锁成功。

如上可以看到事务B加表锁的流程是相当复杂，如果事务B在加表锁之前发现表上已经有了与之互斥意向锁，那么事务B就阻塞等待锁释放，否则加锁成功，整个加锁流程就会变得简单许多。

3.1.3 意向锁与表锁兼容性

锁类型

意向共享锁

意向排它锁

表级共享锁

兼容

不兼容

表级排它锁

不兼容

不兼容

3.1.4 意向锁与表锁示例

图一开启一个事务，执行完SQL后，user表上会有两把锁，一把是意向排它锁、另一把是行级排它锁。

图二需要加表级排它锁，由于表级排它锁与意向排它锁互斥，所以图二的请求会被阻塞。

图三需要加表级共享锁，由于表级共享锁与意向排它锁互斥，所以图三的请求会被阻塞。

图四需要加行级排它锁，先申请意向排它锁，意向锁之间可以相互兼容，在申请id=1的行级排它锁，最终加锁成功。

图五需要加行级共享锁，先申请意向共享锁，意向锁之间可以相互兼容，在申请id=2的行级共享锁，最终加锁成功。

3.1.5 特别注意事项

⚠️ 意向锁不会与行级的共享锁、排它锁互斥，只会和表级的共享锁、排它锁互斥。

⚠️ 意向锁不会与行级的共享锁、排它锁互斥，只会和表级的共享锁、排它锁互斥。

⚠️ 意向锁不会与行级的共享锁、排它锁互斥，只会和表级的共享锁、排它锁互斥。

3.2 自增长锁

往含有自增长列的表中插入记录时，需要获取自增长锁。

3.2.1 自增长锁模式

innodb_autoinc_lock_mode = 0(传统模式)：新增记录加表锁，锁需要等到语句执行完成之后才会被释放，不需要等到事务结束。

innodb_autoinc_lock_mode = 1(连续模式)：简单的新增语句不需要加表锁，只需要加轻量级锁，锁在自增长值分配完后就会被释放，不需要等待语句执行完成之后。

innodb_autoinc_lock_mode = 2(交叉模式)：新增记录不需要加锁，不加锁就会存在并发，存在不安全的情况。主从模式下，事务A、事务B并发在master上新增记录，事务A得到的自增长值为100，事务B得到的自增长值为101，从服务器回放的时候事务A得到的自增长是可能为101，事务B得到的自增长值可能为100。如果不存在主从复制，可以采用该模式来换取性能。

4.行级锁

前面提到过，行锁分为：record lock、gap lock、next-key lock，加锁先加next-key lock，再根据特定条件退化为gap lock或者record lock。

下面会针对唯一索引等值查询、唯一索引范围查询、非唯一索引等值查询、非唯一索引范围查询场景进行加锁分析，进一步巩固锁知识。在这之前我们需要做一些准备工作，创建表、插入记录。

4.1 创建表

create table t5(

id int(10) not null primary key,

c int(10) not null default 0,

d int(10) not null default 0,

key idx_c(c)

);

复制代码

4.2 插入记录

insert into t5 values(5, 5, 5), (10, 10, 10), (15, 15, 15), (20, 20, 20), (25, 25, 25);

复制代码

唯一索引id存在5、10、15、20、25这5个值，对应的加锁范围为(-∞,5]、(5,10]、(10,15]、(15,20]、(20,25]

4.3 唯一索引等值查询加锁

4.3.1 记录存在的等值查询

session1

session2

begin;

select * from t5 where id = 5 for update;

begin;

insert into t5 values(2, 2, 2);pass

insert into t5 values(8, 8, 8);pass

insert into t5 values(12, 12, 12);pass

insert into t5 values(22, 22, 22);pass

insert into t5 values(5, 5, 5);blocked

rollback;

rollback;

唯一索引的等值查询，加锁方式会由next-key lock退化为record lock，仅对索引id=5这一行进行加锁

4.3.2 记录不存在的等值查询

session1

session2

begin;

select * from t5 where id = 8 for update;

beign;

insert into t5 values(8, 8, 8);blocked

insert into t5 values(10, 10, 10);

ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'

rollback;

rollback;

唯一索引等值查询，查询的记录8不存在，8落在(5,10]之间，可得知加锁范围为(5,10]。索引的等值查询会向右遍历，最后一个值不相等时，会退化为间隙锁，最终的加锁范围为(5, 10)，session2中的第二条新增语句也可以说明这一点，如果加锁范围包括10，那么插入语句应该被阻塞，而不是报错。

4.4 唯一索引范围查询加锁

session1

session2

begin;

select * from t5 where id > 10 and id < 15 for update;

begin;

insert into t5 values(12, 12, 12);blocked

rollback;

rollback;

加锁范围为(10,15]

session1

session2

begin;

select * from t5 where id >= 10 and id < 15 for update;

begin;

insert into t5 values(8, 8, 8);pass

insert into t5 values(10, 10, 10);blocked

insert into t5 values(12, 12, 12);blocked

rollback;

rollback;

加锁范围为[10, 15]

4.5 非唯一索引等值查询加锁

4.5.1 记录存在的等值查询

Session1

Session2

begin;

select * from t5 where c = 5 for update;

begin;

insert into t5 values(2, 2, 2);blocked

insert into t5 values(8, 8, 8);blocked

insert into t5 values(12, 12, 12);pass

update t5 set d = d + 5 where id = 5;blocked

rollback;

rollback;

索引c的加锁范围为(-∞,5]、(5,10]

特别注意：非唯一索引的等值查询，下一个区间也会作为加锁范围

4.5.2 基于覆盖索引的等值查询

Session1

Session2

begin;

select id from t5 where c = 5 lock in share mode;

begin;

update t5 set d = d + 5 where id = 5;pass

update t5 set c = c + 5 where id = 5;blocked

update t5 set c = c + 5 where id = 10;pass

rollback;

rollback;

细心的你可能已经发现了4.5.1中执行update t5 set d = d + 5 where id = 5;被阻塞，4.5.2中却可以正常执行这样一个问题。先来看下select id from t5 where c = 5 lock in share mode;这条语句，你会发现查询字段使用的是id而不是*，这也造成了语句在索引c这颗索引树上就可以完成查询而不需要回表，这样就不会对索引id=5这条记录加锁，因此可以对id=5这行执行更新操作。

往下看你会惊讶的发现第二条语句被阻塞了，为什么呢？？？那是因为索引id=5这条记录对应的索引c被加了锁。

有兴趣的可是试着将session1中的语句修改成select id from t5 where c = 5 for update;，再分别执行seesion2中的语句看看会有什么不同

4.5.3 记录不存在的等值查询

session1

session2

begin;

select * from t5 where c = 8 for update;

begin;

insert into t5 values(8, 8, 8);blocked

rollback;

rollback;

加锁范围为(5,10)

4.6 非唯一索引范围查询加锁

session1

session2

begin;

select * from t5 where c >= 5 and c < 10 for update;

begin;

insert into t5 values(8, 8, 8);blocked

insert into t5 values(2, 2, 2);blocked

rollback;

rollback;

由于索引c是非唯一索引，等值查询不会退化为record lock，加锁范围为(-∞,10]

4.7 加锁总结

加锁都是加next-key lock

唯一索引的等值查询会退化为record lock

非唯一索引的等值查询，下一个区间也会作为加锁范围

索引的等值查询会向右遍历，最后一个值与查询的值不相等时会退化为gap lock

5.总结

本次主要和你聊了锁方面的相关知识，包括：为什么需要锁？、锁类型、锁粒度、加锁，其中若有不正确的地方，随时欢迎你来指正，也期待与你的下次相遇。