MySQL实战45讲阅读笔记-锁

系列
MySQL实战45讲阅读笔记-MySQL入门
MySQL实战45讲阅读笔记-日志
MySQL实战45讲阅读笔记-锁
MySQL实战45讲阅读笔记-索引
MySQL实战45讲阅读笔记-MVCC

全局锁

全局锁会对整个数据数据库实例加锁，使用的命令是flush tables with read lock，使用这个命令后其他线程的DML(增删改)语句、DDL(修改、定义表结构)语句和事务提交语句都会被阻塞；
通常在做全局备份的时候可能会用到，一旦使用该命令意味着该实例的业务停摆；如果不能接受业务暂停又想要做备份，可以使用mysqldump的--single-transaction参数，e.g.

mysqldump -uy2ss --single-transaction --databases db -p --master-data=1 > 20190721.sql

表示在开始备份的时候启动一个事务来拿到可重复读的一致性视图，在隔离级别下所有备份的数据会以这个这个事务启动时的数据为准，但是MVCC只是支持innodb引擎，所以在其他引擎下面想要做全局备份还是要依赖flush tables with read lock；解锁的命令是unlock tables，或者在与客户端连接断掉的情况下也会自动释放该锁；

表锁

表锁的语法是lock tables [table]{write|read} e.g.

lock tables t1 read, t2 write;

这个命令的意思是堵塞其他线程和本线程对t1表的写操作，阻塞其他线程对t2表的读写操作；释放该锁的命令也是unlock tables或者与客户端断开连接；

元数据锁(MDL meta-data lock)

MySQL5.5以后引入了MDL，MDL是为了保证在并发条件下DDL(数据定义语句)和DML(Data Manipulation Language数据操作语句-增删改语句)操作的一致性；在访问一个表的时候会自动加上MDL，当对一个表进行增删查改时候会对表加上MDL读锁，当对一个表的表结构进行修改的时候会对表加上MDL写锁；

• 读锁之间互不互斥，因此可以有多个线程对同一个表进行增删查改；
• 读写锁、写锁之间互斥；

当对一个表添加字段或者索引或者修改字段的时候是需要扫描全表数据的，所以在修改表结构的时候也有可能因为使用不当而导致数据库挂掉；

事务A	事务B	事务C
begin;
select * from t;
	alter table t add idx int;(blocked)
		select * from t;(blocked)

事务A在查询的时候会对表t添加一个MDL读锁，这时候事务B进行DDL时想要获取一个MDL写锁，但是被事务A持有读锁所以会被阻塞，接下来的事物C想要进行增删改查但被事务B阻塞，导致现在这个表现在是不能读写的状态；

• 为什么会被阻塞
  因为MDL锁是在执行语句开始的时候申请的，但是释放的时候是在事务提交的时候才会释放（两阶段协议），所以事务A一直持有MDL读锁而事务B的写锁因为读写互斥而被阻塞；

• 如何避免

  1. 尽量避免长事务，长事务会一直持有MDL锁；可以在information_schema的innodb_trx表中查找当前长事务kill掉；
  2. 修改当前session的lock_wait_timeout参数，设定一个合理的值防止长时间被锁住；

DDL

DDL(Data Definition Language 数据定义语句)常用的关键字show databases,use,create,drop,alter等，在MySQL5.5之前的DDL操作主要有Inplace和Copy两种方式；

• Copy
  通过创建临时表，将原表数据全部拷贝到临时表中，期间会添加MDL读锁锁住原表数据禁止DML，只可读，等到所有数据插入到新表中然后rename替换成新表，rename期间不可将DML读锁升级为写锁禁止读写直到完成操作；
• Inplace
  5.5版本中实现了Inplace方式，和Copy方式不同是的Inplace不会创建临时表，直接在原表中修改，同样会添加DML读锁不允许写只能读，效率比Copy高，因为所有的操作都是在innodb内部完成的，对于serve层来说并没有创建临时表所以叫inplace；

5.6之后引入了Online DDL，比起上面两种方式OnlineDDL运行在做DDL期间做表进行读写；大概的流程是这样的

1. 申请MDL写锁禁止读写，创建新的临时frm、idb文件，降级成MDL读锁；
2. 扫描原表中主键的所有数据页，用数据页中所有原表的数据生成B+树储存到idb文件中，期间所有对原表的操作记录到一个日志中；
3. 完成写文件操作后再执行日志的逻辑，升级成MDL写锁，rename替换frm和idb文件，结束；

常见的DDL操作

操作	方式	说明
add index	online	当表上有fulltext索引时会锁表，阻塞写
add fulltext index	inplace	锁表，阻塞写
optimize table	online	当有fulltext时是Copy方式，锁表阻塞写
alter table x engine=innodb	online	当有fulltext时是Copy方式，锁表阻塞写
add column	online	添加auto_increment列或修改当前列为自增列都要锁表，阻塞写
drop column	online	删除auto_increment列或修改当前自增列都要锁表，阻塞写
convert character set	copy	阻塞写

表锁和MDL都是属于表级锁；

自增锁(Auto-inc Locks)

自增锁是一种特殊的表级锁，具有AUTO_INCREMENT属性的列在事务插入前会先获得自增锁。在最简单的情况下，如果一个事务正在向表中插入值，那么任何其他事务都必须等待自己插入该表，以便第一个事务插入的行接收连续的主键值。

先来看看自增值的机制，比如有一个表

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

在MySQL5.7之前的版本AUTO_INCREMENT=2是保存在内存中的，每次重启后在第一次打开表的时候会去计算这个表的最大id；
而在MySQL8的版本将自增id保存在了redolog里面，就有了持久性；

再插入新行时如果id指定为0、null或者未指定值时，那么就把当前的AUTO_INCREMENT值当作新自增值，如果指定了具体值则使用具体值；

现在执行下面这个语句

insert into t values(null, 1, 1); 

这个语句的执行流程是

1. 执行器调用innodb引擎接口写入一行，传入的这一行的值是(0,1,1)；
2. innodb发现没有指定自增id值，获取表t当前的自增值为2；
3. 将插入行改成(2,1,1);
4. 将表的自增值改成3；
5. 继续执行入表操作；

如果在第5步因为重复值或者其他事物回滚什么的原因导致入表失败的话，此时并不会把自增值改回去，所以这时候如果有新的数据入表自增值将是4开始，这个导致自增值出现不连续的原因之一;

自增锁的优化

因为自增值为了保证在并发的情况下是唯一的，在申请的时候需要依赖自增锁，而自增锁也是个表级锁，为了能最大限度的提高并发读，MySQL有控制自增锁的使用策略；

先了解下insert大致可以分为下面几类

• Simple insert
  可以预先确定要插入的行数（当语句被初始处理时）的语句。 这包括没有嵌套子查询的单行和多行insert和replace语句，但不包括insert ... on duplicate key update，比如insert into t(a) values(1);
• Bulk insert
  事先不知道要插入的行数（和所需自动递增值的数量）的语句。 这包括insert ... select，replace ... select和load data语句，但不包括单纯insert。 Innodb在处理每行时一次为AUTO_INCREMENT列分配一个新值。
• Mixed insert
  这些是Simple inserts语句但是指定一些（但不是全部）新行的自动递增值。 比如：
  insert into t1 (c1,c2) values (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')或者insert ... on duplicate key update
• INSERT-like statements(类INSERT语句)
  所有可以向表中增加行的语句，包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA；包括simple-inserts, bulk-inserts, and mixed-mode inserts；

innodb_autoinc_lock_mode

innodb_autoinc_lock_mode是用来控制auto_increment的锁定模式，取值有3种

• 0 (traditional lock mode)
  所有INSERT-like语句获得一个特殊的表级锁，并且在语句结束后释放该锁(并不是事务结束)，保证自动递增由任何给定语句分配的值是连续的，但是性能是最低下的；
• 1 (consecutive lock mode)
  这个模式下bulk insert使用的是表级自增锁，在语句结束后释放且同一时刻只能一个语句持有自增锁；但是在simple insert时可以具体计算出需要几个自增id，然后一次性申请id后就释放该锁，并不需要等待语句结束后才能释放；在这个模式下可能会出现主键不连续的情况；默认值模式是这个模式；
• 2 (interleaved lock mode)
  这个模式都是申请主键后就立马释放锁，所以性能是最好的，但是当binlog_format为statement的情况下（statement模式下binlog保存的是执行的SQL语句）可能会造成主备不一致的情况；

为什么在interleaved下binlog的statement模式会造成主备不一致

比如下面这个例子

事务A	事务B
insert into t values(null, 1, 1);
insert into t values(null, 2, 2);
insert into t values(null, 3, 3);
	create tables t2 like t;
insert into t2 values(null,4,4)	insert into t2(c,d) select c,d from t;

假设sessionB申请了自增值以后马上释放自增锁，可能会出现这个情况

• sessionB添加了两个记录(1,1,1),(2,2,2)
• 然后sessionA来申请自增id=3，添加(3,4,4)
• sessionB申请到了自增id=4，添加(4,3,3)

因为sessionA和sessionB是同时执行insert，所以在binlog日志日志里面要么先保存sessionA，要么先保存sessionB；但是无论哪一种，把这个binlog拿到从库执行，sessionB语句执行出来的结束，id都是连续的，这就会导致主备不一致；

如何避免这种现象发生

1. 在执行批量插入语句时，自增锁一直持有到语句释放；这样能保证生成的id是连续的，但是性能方面就有问题；
2. binlog设置为row，记录的是被修改的数据，到备库执行的时候直接取binlog语句记录的id而不是依赖备库自增id的逻辑；

Innodb行锁

Innodb是支持行级锁的，行级锁的加锁粒度比表锁更小，所以使用行锁的并发程度更高，但是因为粒度更小所以加锁消耗的资源越大，死锁的概率更高；
使用Innodb行级锁的条件是使用索引，如果不使用索引导致全表扫描就意味着要使用表级锁;

Innodb有两种类型的行锁

• 共享锁（S锁、读锁）
  允许多个事务并行去读一行数据，阻止其他事务对这一行修改；假如事务A对某一行加上S锁，则事务A可以读取该行数据，但是不能修改，其他事务只能对该行添加S锁，不能加X锁直到事务A释放S锁；这样保证了其他事务可以读该行数据但是在S锁未释放之前不能修改该数据；
• 排他锁（X锁、写锁）
  允许获取到排他锁的事务更新数据，在某一行数据的排他锁释放之前，不允许其他事务再添加任何锁；
  Innodb在修改语句update、insert、delete都会自动给涉及到的数据加上排他锁，select语句默认不会添加任何锁，而select...for update和select...lock in share mode则会分别加上X锁和S锁，被加上排他锁的数据是允许其他事务select查询的；

意向锁

意向锁是一种表级锁，它是允许和行级锁共存；
假如事务A对某一行加上了共享锁只允许其他事物读不允许写，那么其他事务想要在事务A释放锁之前添加行级写锁是不被允许的(一旦其他事务添加上了行级写锁，就意味着该事务可以对表中的任意一行进行修改，这和事务A的行锁就冲突了)，其他事务需要判断该表是否可以添加锁，但是遍历所有行来判断某一行是否被加锁是非常消耗资源的一种方法是不现实的；

意向锁就是用来解决这种问题的，在意向锁存在的情况下，事务A想要申请行锁之前，需要申请意向共享锁；

意向锁种类

• 意向共享锁(IS锁)
  当一个事务需要对某一行添加共享锁的时候先需要对该表添加意向共享锁，成功之后才会添加S锁；
• 意向排他锁(IX锁)
  当一个事务需要对某一行添加排他锁的时候先需要对该表添加意向排他锁，成功之后才会添加X锁；

意向锁之间是相互兼容的，就意味着一个表中可以同时添加多个意向锁，但是意向锁和S/X锁之间会互斥，具体规则如下

这里的共享锁和排他锁都是表锁，意向锁和行锁之间不会互斥；
意向锁主要是解决表锁和行锁之间共存的问题;

当使用select * from t where id = 1 for update给id=1添加排他锁时，同时也会对表t添加IX锁，当其他事务想要获取t表的S锁时，会检测到表t存在IX锁，就可以得知表t中某一行一定存在排他锁，所以会被阻塞；
假如此时事务C想要获取id=2行的排他锁时，发现表t存在IX锁，然后开始申请IX锁，因为意向锁之间不会互斥，所以事务C对表t添加了一个IX锁，接着申请id=2行的排他锁，因为id=2行不存在任何锁所以事务C成功申请到了IX锁；

间隙锁(Gap Lock)

间隙锁是为了解决在RR(可重复读)隔离级别下出现的幻读问题，虽然RR可以保证当前事务的不受其他事务的update、delete的影响，但是会因为insert而出现幻读；只有RR隔离级别下才会有Gap锁；

• 为什么会出现幻读
  虽然update可能会被行锁锁住，但是insert操作是插入操作，毕竟行锁不能锁住不存在的行；

如何解决幻读

Innodb引入间隙锁，所谓间隙就是行与行之间的‘间隙’；比如表t中有(1,10),(3,30),(5,50),(7,70)几条数据，那么执行下面的语句会添加间隙锁

select * from t where secondrow = 30 for update;

除了给(3,30)这一行加上行锁外同时还会给(10,30),(30,50)这个区间的所有间隙都会添加锁，所以叫做间隙锁；其他事务想要往这个间隙中添加数据时会被锁住

insert into `t` values(2,41);//阻塞
insert into `t` values(5,21);//阻塞
insert into `t` values(2,61);//非阻塞

间隙锁之间是不会互斥的，间隙锁和行锁合称Next-key lock(临键锁)，比如上面表t一开始有原始数据(1,10),(3,30),(5,50),(7,70)，这时执行select * from t for update;把整个表的所有记录锁起来，这时候形成了5个next-key lock分别是(-oo, 10], (10, 30], (30, 50], (50, 70], (70, +00);

Gap Lock和Next-key lock只是在RR隔离级别下存在的，并遵守下面几个规则

1. 加锁的基本单位是Next-key lock；
2. 查找过程中访问到的对象才会加锁；
3. 索引上的等值查询，给唯一索引加锁的时候，Next-key lock退化成行锁；
4. 索引上的等值查询，向右遍历时最后一个值不满足等值条件的时候，Next-key lock退化成间隙锁；

实例1

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

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

事务A	事务B	事务C
begin;
update t set d = d + 1 where id = 7;
	insert into t values(8,8,8); (blocked)
		update t set d = d + 1 where id = 10;(Query ok)

1. 根据规则1，加锁单位是Next-key lock，sessionA加锁范围是(5,10]；
2. 根据规则4，这是一个等值查询(id=7)，且id=10不满足查询条件，next-key lock退化成间隙锁，加锁范围变成(5,10)

所以事务B会被阻塞，事务C可以执行成功；

实例2

事务A	事务B	事务C
begin;
select id from t where c= 5 lock in share mode;
	update t set d = d + 1 where id = 5; (Query ok)
		insert into t values(7,7,7);(blocked)

1. 加锁单位是next-key lock，所以给(0,5]添加next-key lock；next-key lock是由间隙锁和行锁构成，实际加锁过程是完成一个加锁操作后再添加另一个锁；
2. 规则4，c是普通索引，访问c=5后还是需要向右遍历，接下来c=10后不满足等值条件(c=5)后停下来，根据规则2访问到的对象都需要加锁，所以添加(5,10]的next-key lock，然后退化成间隙锁(5,10)；
3. select id from t where c= 5使用的是覆盖索引，并没有访问到主索引树，所以主索引上没有加任何锁；所以sessionB的update语句是可以执行的，但是sessionC的(7,7,7)会被sessionA的间隙锁给阻塞；

• lock in share mode只锁覆盖索引，如果是for update则会给主索引上面满足条件的行加上行锁；

实例3

事务A	事务B	事务C
begin;
select * from t where id >= 10 and id < 11 for update;
	insert into t values(8,8,8); (Query ok)
	insert into t values(13,13,13); (blocked)
		update t set d = d + 1 where id = 15;(blocked)

1. 添加next-key lock(5,10]，但是根据规则3，主键id上面的等值查找会退化成行锁，所以只给id=10加锁；
2. 索引树上继续向右遍历，下一项id=15后停止遍历，查找过程中访问到的对象需要加锁所以添加(10, 15]的间隙锁；

sessionA在主索引树上面添加了id=10的行锁和(10,15]的间隙锁，所以sessionB和sessionC会被阻塞；

假如事务A的查找语句变成

select * from t where c >= 10 and c < 11 for update;

因为c是普通索引，c>=10条件添加了(5,10]的间隙锁，接着继续向右遍历索引树找到id=15，添加(10,15]间隙锁；
所以插入(8,8,8)(13,13,13)和更新id=15行都是会被锁住的；

实例4

begin;
select * from t where id>9 and id<12 order by id desc for update;

执行这个SQL语句会遍历主索引树，从0到15拿到了所有满足条件的行，扫描到的行是(0,0,0),(5,5,5),(10,10,10),(15,15,15)，然后向左遍历添加间隙锁(0,5), (5,10)和(10,15)，同时5，10，15上面有行锁形成Next-key lock(0,5], (5,10]和(10, 15]，关键是id=15，根据规则4索引上面的等值查询，向右遍历时最后一个值不满足等值条件的时候，Next-key lock退化成间隙锁所以最后锁住的范围是(0,5], (5,10]和(10, 15)；

实例5

事务A	事务B
begin;
select c from t where c > 5 lock in share mode;
	update t set c=1 where c=5; (OK)
	update t set c=5 where c=1; (blocked)
commit;

1. 事务A需要找到比c大的第一个值，c=10；然后遍历索引树，添加Next-key lock(5, 10], (10, 15], (15, 20], (20, 25], (25, +oo)；
2. 事务B执行update语句后c索引树上面的Next-key lock变成了(1, 10]，所以事务B第二句update set c=5会被(1, 10]给阻塞住；

实例6

select * from t where c>=15 and c<=20 order by c desc lock in share mode;

1. order by c意味着从c索引上面最先要找到的c=20，所以第一个等值查询会扫描到c=25，所以添加Next-key lock(20, 25]会退回成Gap锁(20, 25)；
2. 然后向左扫描直到c=10停止，添加(15, 20], (10, 15]，(5, 10]间隙锁，同时因为是select *所以会在主键索引上寻找c=20，c=15，所以同样也会给主键索引上id=15和id=25的行添加行锁；

实例7

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB;
有10w行记录，id和c都是1-100000递增

事务A	事务B	事务C	事务D
begin;
select * from t where c=5 for update;
	delete from t where c=10; (blocked)
		insert into t values(100001, 8); (blocked)
			update t set c=100 where id=10;(blocked)
commit;

在RR隔离级别下，这里的加锁顺序是

1. 事务A执行select...for update，先是在server层添加了MDL读锁， 然后想进入到引擎层查找行c=5，因为c不是索引所以查找c=5这一行是要遍历整个表，所以对每一个遍历的行都添加上了排他锁(X锁)，添加排他锁之前要对表添加意向排他锁(IX锁)，并且添加了Gap锁，形成的Next-key lock范围是(-oo, 1], (1,2] .... (9999, 10000], (10000, +oo)，但是因为是给唯一索引加锁，Next-key lock会退化成行锁，最终添加的锁是全表的行锁和Gap锁；
2. 事务B对c=10操作会被事务A的锁锁住；
3. 事务C先是添加MDL读锁，然后想要插入数据需要(10001,8)添加X锁，添加X锁之前要先添加IX锁，但是事务C的IX锁会被事务A的X锁给阻塞，所以处于blocked状态；
4. 事务D同样被事务A锁住；

在RC隔离级别下情况又有些不同

事务A	事务B	事务C	事务D
begin;
select * from t where c=5 for update;
	delete from t where c=10; (blocked)
		insert into t values(100001, 8); (OK)
			update t set c=100 where id=10;(OK)
commit;

1. 事务A添加在server层MDL读锁 ，进入innodb对整个表所有行添加X锁和IX锁，但是在RC级别下不会添加Gap锁；
2. 事务B会被事务A锁住；
3. 因为不存在Gap锁，事务C想要对(100001,8)行插入的时候不会被阻塞住，因为行锁只能锁住存在的行，而id=100001之前是不存在的；
4. 事务D原本应该被事务A给锁住，但是在RC隔离级别情况下MySQL有个优化，会对不符合条件的行提前释放锁，这里事务D可以执行成功就是因为id=10的行锁被提前释放了（和semi-consistent read同理，但是这就违背了2PL(两阶段加锁协议)），在RR隔离级别为了保证binlog日志的记录顺序是不会发生提前释放锁的；

间隙锁和Next-key lock可以解决RR的幻读现象，但是毕竟是加锁，还是对于并发性能造成影响，同时死锁的概率性也会增加；如果业务可以接受幻读现象的话，可以使用RC隔离级别+binlog的row格式组合，避免使用间隙锁；

两阶段加锁协议(Two-Phase Locking)

一个事务中分为加锁和解锁两个阶段，并且要遵守下面三个规则

• 在对任何数据进行读、写操作之前，首先要申请并获得对该数据的锁；
• 事物提交或回滚时才是解锁阶段，其他时间是加锁阶段；
• 在释放一个锁之后，事务不再申请和获得其它任何锁；

总的来说就是所有lock操作都要在unlock之前完成；行锁是在需要的时候才添加，释放的时候是在事物结束之后再释放；

• 两阶段加锁对于性能的影响
  假如现在需要根据表A的价格更新表B的销售额并且在表C添加一条记录；

方案1:
begin;
// 获取A的价格
select * from A where s_id=1 for update;
// 更新B的money
update B set money=5 where id=1;
// 添加记录
insert into order;
commit;

方案2:
begin;
// 添加记录
insert into order;
// 获取A的价格
select * from A where s_id=1 for update;
// 更新B的money
update B set money=5 where id=1;
commit;

对于两个并行事物来说可能冲突语句是select for update和update，所以说语句之间的执行顺序也是非常关键的，方案2比方案1的加锁时间少，意味着能提高这个事物之间的锁等待，提升了并发度；

死锁

当并行线程出现了循环资源依赖，涉及的资源在相互等待其他线程释放资源导致进入无限等待的状态，成为死锁；

innodb有两种处理死锁的方式

• innodb_lock_wait_timeout
  这个参数可以控制死锁等待超时的时间，默认值为50s
• innodb_deadlock_detect
  发起死锁检测，发现死锁则根据权重选择一个事务进行回滚，让其他事物进行下去，默认为on；但是如果在并发量大的情况下死锁检测是比较消耗时间的，因为检测死锁事物的节点是指数级增长的；

可以通过控制并发数量来控制发生死锁的概率，即对于同一行的操作可以放到一个队列里面排队进行；

参考

MySql-两阶段加锁协议
插入InnoDB自增列，居然是表锁？
详解 MySql InnoDB 中意向锁的作用
详谈 MySQL Online DDL