mysql中的锁-优快云博客

本文链接：https://blog.youkuaiyun.com/skylar2/article/details/149204618

对于大家同时要使用的临时资源（并发问题），我们需要合理地控制资源的访问规则；锁是控制访问规则的重要结构。

mysql中的锁：全局锁，表锁、元数据锁（MDL），行锁、间隙锁等

** 加锁 ** 这个动作是存储引擎才能做的

全局锁

适用场景：全局逻辑备份 // 一般在备库上执行

Flush table with read lock(FTWRL)命令，让整个库处于只读状态

等待：进行中的读写事务完成，脏页完成刷盘 // 保证数据一致性

全局锁问题：主库加锁，业务停摆；从库备份加锁，停止同步主库binlog，主从延迟；

不能使用readonly来代替全局锁，原因：

readonly一般有逻辑业务含义，比如表示备库，只读
全局锁在服务崩溃时释放；readonly崩溃后需要手动恢复，带来风险

可重复读快照实现备份

innodb有MVCC，可以使用一致性事务（快照）进行备份。mysqldump -single-transaction 启动可重复读级别的事务备份数据库，不阻塞库上读写。

* : innodb mvcc 快照是整库的，所以才能做数据备份

备库用-single-transaction做逻辑备份的时候，如果从主库的binlog传来DDL语句会发生什么？

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

时刻 1及之前传来，没影响，用的是DDL后的表；

Q5及之后传来没影响，用的是DDL前的表；

Q4和时刻2传来有影响，表结构和表数据会对不上；

表锁

lock tables … read/write，unlock table可以显示解锁。不支持行锁（innodb支持行锁）的存储引擎，使用表锁保护数据。

示例：lock tables t1 read, t2 write; 其他线程只能对t1进行读操作，不能对t2操作；同时当前线程也只能读t1，不能写t1，可以写t2;

元数据锁 MDL

MDL无需显示使用，访问表时自动添加；增删改数据时加MDL读锁，增删改表结构时加写锁。

在语句执行时申请，事务commit时释放 // 不是在语句执行完成时释放

阻塞链问题

sessionA	sessionB	sessionC	sessionD
begin; select * from t1; // 加读锁
	beigin; select * from t1; //加读锁	alter table t add c int; // 阻塞，期望加写锁
			select * from t1; // 被期望加写锁阻塞，不能读

*： MDL队列是先进先出的公平队列，MDL是原子操作

此时，整个表不能读写，等待sessionA、sessionB的完成；更严重的情况：客户端查询频繁且有重试机制，很快会把线程打满。

解决方案

执行alter table t add c int写操作前，判断前面是否有长事务（大查询），选择一：等待长事务执行完成；选择二：kill掉长事务

如果需要写热点表，虽然数据量不大，但请求频繁，需要设置任务超时时间，改不了也不要影响其他语句，等有空再改；

表加字段需要全表扫描原因

存储格式一致性：

所有行需要保证存储格式一致
新增字段后，所有现有字段都需要更新以包含新字段的存储空间

索引重建：

修改字段可能影响现有索引
添加索引需要读取现有全部数据来构建

Online DDL 实现步骤

加MDL写锁
降级为读锁
真正做DDL // 这时整个表可以正常的读写
升级写锁
释放锁

Online DDL减少了锁持有的时间，调高了并发度。

重建表时允许增删改也使用了类似的思想。

行锁

行锁在语句执行时添加，在事务提交时释放
- 为提高并发度，减少锁持有的时间，所以尽可能将容易造成锁冲突、要锁住多个行的操作往后放
行锁加锁不是原子操作：先获取意向锁（表级锁），再获取实际行锁
- 表级锁 IS锁：意向共享锁；IX锁：意向排他锁

死锁问题

sessionA	sessionB
begin; update t set k=k+1 where id=1; // id=1这行加写锁	begin;
	update t set k=k+1 where id=2; // id=2这行加写锁
update t set k=k+1 where id=2; // 阻塞，等待sessionB提交释放id=2这行的写锁
	update t set k=k+1 where id=1; // 阻塞，等待sessionA提交释放id=2这行的写锁

如上，互相等待，死锁形成；

解决方案

超时控制

配置：innodb_lock_wait_timeut

问题

超时时间不好设置，太短容易误伤业务，太长也不好，都等待很长时间，都没执行 // 业务有损

死锁检测

发现死锁时，主动回滚一个事务

配置：innodb_deadlock_detect=on

问题

CPU计算成本高。每个事务加锁的时候，都套看看他所依赖的线程有没有被别人锁住; 可能出现热点行没执行几个事务，但CPU利用率很高；

解决方案

控制并发度 =》死锁检测成本低

基本思路：对于相同行的更新在引擎前排队

实现位置：中间件、数据库服务端

间隙锁 gap lock

在可重复读隔离级别下还是有insert幻读的问题；幻读会导致主库数据和binlog不一致，进而导致主从数据不一致。

binlog记录是有顺序的。对于没有冲突的事务，先后顺序变化不会改变数据的最终结果，是可以并发的，哪个先完成先写入binlog都ok。但对于有冲突的事务，不同的执行顺序会造成数据的结果不一样，所以就需要锁来控制住先后的顺序，比如同时操作一行时，行锁会阻塞住后来的事务，等待当前事务提交再执行。

但行锁只能锁住已存在的数据，对插入数据不管用。所以需要间隙锁，锁住两行数据间的间隙，不允许插入，进而保证数据写入和binlog写入的一致性。

* 间隙锁：可重复读级别下才有间隙锁。读已提交级别下一般没有间隙锁，所以读已提交时需要设置binlog=row来保证数据一致性 // 读提交隔离级别下，在外键场景下有间隙锁

* 可重复读级别下，间隙锁等到事务提交才释放；读提交级别下优化：语句执行过程加行锁，在语句执行完成后，就把不满足条件的行上的行锁直接释放，不需要等待事务提交 // 并发能力更好；

为什么需要加锁的示例

当前表数据：(0,0,0) (5,5,5) (10,10,10) (15,15,15) (20,20,20) (25,25,25) // id c d

sessionA	sessionB	sessionC
begin; select * from t where d=5 for update; // (5,5,5); update t set d=100 where d=5;
	update t set d=5 where id=0; update t set c=5 where id=0;
select * from t where d=5 for update;
		insert into t values(1,1,5) ; update t set c=5 where id=1;
select * from t where d=5 for update;
commit;

执行结果：

select * from t where d=5 for update; // (5,5,5);
update t set d=100 where d=5; // (5,5,100)

update t set d=5 where id=0; // (0,0,5)
update t set c=5 where id=0; // (0,5,5)

insert into t values(1,1,5) ; // (1,1,5)
update t set c=5 where id=1; // (1,5,5)

// 结果：(0,5，5) (1,5,5) (5,5,100) ...

如果for update没有对其他行锁，binlog中的顺序是：sessionB、sessionC、sessionA

// binlog=statement

update t set d=5 where id=0; // (0,0,5)
update t set c=5 where id=0; // (0,5,5)

insert into t values(1,1,5) ; // (1,1,5)
update t set c=5 where id=1; // (1,5,5)

// 注意：binlog是顺序写入，顺序执行（没优化的时候），binlog在事务提交时写入
select * from t where d=5 for update; // (0,5,5)， (1,5,5)，(5,5,5); 
update t set d=100 where d=5; // (0,5,100)， (1,5,100)，(5,5,100)

// 结果：(0,5,100)， (1,5,100)，(5,5,100) ...

主库事务的执行结果和binlog回放的结果不一致。所以，得加锁

加锁规则

间隙锁+行锁=next-key lock

原则：加锁的单位是next-key lock，前开后闭
- 间隙是由查到的右边的数据决定的
原则：查找过程中访问到的对象都会加锁
优化：等值查询时，最后一个不包含退化为间隙锁，右闭=>右开
优化：如果是唯一索引，就退化成行锁
bug：唯一索引多锁一段，唯一索引的范围查询会访问到不满足条件的第一个值位置 // 因为是范围查询，多锁住了不满足条件的那行

死锁

间隙锁和插入数据冲突，间隙锁和间隙锁之间不冲突。所以不同事务可以对同一间隙加锁。

如果sessionA正序加锁，sessionB倒序加锁，就容易出现死锁。所以最好同一顺序处理。

死锁示例

当前表数据：(0,0,0) (5,5,5) (10,10,10) // id c d

sessionA	sessionB
begin; select * from t order by c for update;等sessionB释放id=10的锁	beigin; select * from t order by c desc for update; // 等sessionA释放id=0的锁