学习笔记：MySQL锁机制

2020-09-09 本文已影响0人大力papa

本文仅供学习交流使用，侵权必删。
不作商业用途，转载请注明出处

简单说明

文章里面使用到的表是MySQL官网提供的sakila schema

MySQL锁基本介绍

MySQL的每种存储引擎支持不同的锁支持。比如MyISAM和Memory引擎采用的是表锁（table lock），而像InnoDB和XtraDB采用的是行级锁（row lock）。InnoDB也支持表锁，但默认是采用行级锁的。

表锁：开销最小的锁策略，会锁住整张表。写锁会阻塞其他锁，读锁不会阻塞其他锁。

行级锁: 开销最大，同时可以最大程度支持并发处理。行级锁在存储引擎层实现，没有在MySQL服务器层实现。

InnoDB的锁实现

Shared and Exclusive Locks (共享锁和独占锁)

共享锁(S锁)和独占锁(X锁)是InnoDB实现行级锁的两种方式。

共享锁

S锁允许持有锁的事务读取行,S锁允许多个事务持有锁并读取行

## 申请共享锁
select * from actor where actor_id=1 lock in share mode;

S锁允许其他事务申请S锁，但会阻塞其他事务申请X锁的请求。

时间	会话A	会话B
1	set autocommit=0; select * from actor where actor_id = 1 lock in share mode;
2		set autocommit=0; select * from actor where actor_id = 1 lock in share mode;
3		## 获得共享锁，成功读取数据 commit;
4		select * from actor where actor_id = 1 for update;
5		## 获取独占锁失败

独占锁

X锁允许持有锁的事务更新行或者删除行。

-- 申请独占锁
select * from actor where actor_id=1 for update;

如果事务T1持有行的X锁，那么事务T2想要申请S锁或者X锁，都必须在T1释放了X锁之后才能成功获得。

时间	会话A	会话B
1	set autocommit=0; select * from actor where actor_id = 1 lock for update;
2		set autocommit=0; select * from actor where actor_id = 1 lock in share mode;
3		## 获取共享锁失败
4		select * from actor where actor_id = 1 for update;
5		## 获取独占锁失败

Intention Lock (意向锁)

意向锁是表级锁，意向锁是将锁定的对象分为多个层次，意向锁意味将事务在更细粒度上进行加锁。假设将上锁的对象看作一个树结构，对最下层的叶子对象加锁，那么先需要对父节点对象上锁。

层次结构

假设我们需要对记录R上X锁，那么需要分别对数据库A、表、页上IX锁，最后才能对记录R上X锁。但有一种情况是在对记录R加X锁之前，已经有另外一个事务对表1进行了S表锁，那么由于IX锁和S锁不兼容，所以对记录R加X锁的事务需要等待S锁释放才能进行加锁

意向锁主要分为两种锁：

意向共享锁（IS）：表示事务打算对表中的各个行设置共享锁。在事务可以获取表中某行的共享锁之前，它必须首先获取该表中的IS锁或更强的锁
意向排他锁（IX）：表示事务打算对表中的各个行设置排他锁。在事务可以获取表中某行的排它锁之前，它必须首先获取该表中的IX锁。

锁兼容性

Record Lock (记录锁)

记录锁就是针对索引上加锁，是行锁的其中一种实现。如果表没有建立索引，那么会使用隐式的主键上锁。例如：

select * from actor where actor_id=1 for update

另外如果以这种方式查询，InnoDB采用的是快照读(MVCC)的方式：

select * from actor where actor_id=1

记录锁的作用既是为了避免不可重复读问题，同时也能避免脏读的问题

Gap Lock (间隙锁)

Gap Lock是对索引之间间隙加锁,或者是对第一个或最后一个索引记录之前的间隙的锁定。
此外间隙锁需要在RR隔离级别下才会生效，RC隔离级别无法使用
这里我是使用了payment这张表，先删除payment_id=16的这条记录

delete from payment p where p.payment_id=16

然后我们做一个范围查询,不要提交事务

set autocommit=0;

select * from payment p where p.payment_id between 1 and 20 for update;

再接着开一个事务做插入操作,这个插入会被阻塞

set autocommit=0;

insert into payment(payment_id,customer_id,staff_id,rental_id,amount) value (16,1,1,5244,3)

Gap Lock主要目的是为了事务在间隙中插入数据导致不可重复读的问题

Next-Key Lock (临键锁)

临键锁是记录锁和间隙锁组合一起使用。临键锁锁定的是索引记录本身和索引记录之前的区间。Next-Key Lock在RR隔离级别生效，可以防止幻读问题，RC隔离级别无法使用。
如果一个会话在索引中的记录R上具有共享或排他锁，另一个会话不能按照索引顺序在R之前的间隙中插入新的索引记录。

假设一个索引包含10、11、13和20。该索引的潜在的Next-Key Lock可能为

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对于最后一个区间，Next-Key Lock锁的是索引最大值和"最高值"伪记录的区间，而这个伪记录的值高于索引中所有的值。

Insert Intention Lock (意向插入锁)

Insert Intention Lock是一种Gap Lock，它是插入之前由插入操作设置的一种类型。该锁表示插入的意图，即如果插入到同一个索引间隙中的多个事务没有插入到间隙中的相同位置，则它们不需要相互等待。

假设有值为4和7的索引记录。尝试分别插入值5和6的单独事务，在获得插入行的排他锁之前，每个事务都用 Insert Intention Lock锁定4和7之间的间隙，但是不会相互阻塞，因为没有冲突。

首先我们开启一个A事务查询actor表actor_id>100的数据并上排它锁

set autocommit=0;
select * from actor a where a.actor_id >100 for update

然后开启一个B事务插入一条actor_id=201的数据到actor，但这调插入会被A事务的排它锁阻塞

set autocommit=0;
insert into actor(actor_id,first_name,last_name) value (201,'dali','papa')

AUTO-INC Lock (自增锁)

AUTO-INC Lock是一种特殊的表级锁，当事务插入数据涉及到AUTO_INCREMENT列的时候会使用到AUTO-INC Lock。举个例子就是，如果一个事务正在向表中插入值，则任何其他事务都必须等待自己向该表中进行插入，以便第一个事务插入的行接收连续的主键值。

InnoDB提供了innodb_autoinc_lock_mode配置，它允许您选择如何在可预测的自动增量值序列和插入操作的最大并发性之间进行权衡

关于更多自增的详细细节可以看这篇文章：Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB”

参考

15.7.1 InnoDB Locking
《MySQL技术内幕 InnoDB存储引擎第2版》
《高性能MySQL 第4版》