MySQL 锁机制 | Hanpy's blog

简单记录一下 MySQL（InnoDB引擎）的锁类型。根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

一、全局锁

全局锁就是对整个数据库实例加锁。

1.1、加锁方法

# 加锁
mysql> Flush tables with read lock

# 解锁
mysql> unlock tables

执行成功之后，整个库处于只读状态，会关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

1.2、使用场景

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

可能造成的影响

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

二、表锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

2.1、表锁

表锁的语法

# 加锁
# mysql> lock tables tableNames read/write

# t1 表只读
mysql lock tables t1 read;

# t1 表只写
mysql lock tables t1 write;

与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外，也会限制当前线程。

2.2、MDL（metadata lock)

MDL 不需要显式的使用，MDL 的作用是，保证读写的正确性。当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

三、行锁

3.1、两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

3.2、死锁和死锁检测

死锁的定义

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

死锁的例子

CREATE TABLE `t1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `hanpy`.`t1`(`id`, `c`, `d`) VALUES (1, 1, 1);
INSERT INTO `hanpy`.`t1`(`id`, `c`, `d`) VALUES (2, 2, 2);
INSERT INTO `hanpy`.`t1`(`id`, `c`, `d`) VALUES (3, 3, 3);
INSERT INTO `hanpy`.`t1`(`id`, `c`, `d`) VALUES (5, 5, 5);

Session A	Session B
begin update t1 set c=c+1 where id=1;
	begin update t1 set c=c+1 where id=2;
update t1 set c=c+1 where id=2;
	update t1 set c=c+1 where id=1; # (1213, ‘Deadlock found when trying to get lock; try restarting transaction’)

Session A 会等待 id=2 的行锁，Session B会等待 id=2 的行锁，所以造成了死锁，最后触发了死锁检测。

出现死锁的两种策略

锁等待，直接进入等待，直到超时。innodb_lock_wait_timeout 可以设置超时的时间，默认设置的50s
主动检查，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

3.3、InnoDB 中两种标准行锁

InnoDB默认使用行锁，实现了两种标准的行锁——共享锁与排他锁；

共享锁

共享锁又称为读锁或者是S锁，当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

排它锁

排它锁又称为写锁或者是X锁，当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

读取操作加锁

默认情况下Innodb中，select 操作是使用一致性非锁定读（快照读）。
加读锁

# lock in share mode

# 举个例子
mysql> select * from t1 where id=1 lock in share mode;

加写锁

# for update

# 举个例子
mysql> select * from t1 where id=1 for update;

3.4、幻读

事务隔离级别是可重复读的前提下

幻读是指在同一个事务中，前后两次查询同一个范围的数据，但是第二次查询却看到了第一次查询没看到的行。

需要明确幻读出现的两个前提

事务的隔离级别为可重复读，且是当前读（当前读的情况才会出现幻读的情况，快照读使用MVCC来避免幻读）
幻读仅专指新插入的行（更新的不算）

幻读如何解决

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

间隙锁，锁的就是两个值之间的空隙。

间隙锁还有一个特殊的地方，间隙锁之间都不存在冲突关系，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。间隙锁和 next-key lock 的引入，解决了幻读的问题。