MySQL加锁这点事儿

大家好，我是小黑呀！

数据库设计锁的目的是处理并发问题，在并发对资源进行访问时，数据库要合理控制对资源的访问规则。

而锁就是用来实现这些访问规则的一个数据结构。

在对数据并发操作时，没有锁可能会引起数据的不一致，导致更新丢失。

​锁的分类​

乐观锁和悲观锁

乐观锁： 对于出现更新丢失的可能性比较乐观，先认为不会出现更新丢失，在最后更新数据时进行比较。

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

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

select id,value,version from t where id=1;

update t set value=2,version=version+1 where id=1 and version=1;

**悲观锁：** 对于出现更新丢失的可能性比较悲观，在查询时就加锁，保证不被其他事务操作；可通过select...fot update实现。

select * from t where id = 1 for update;

共享锁（S锁）和排他锁（X锁）

共享锁（shared lock）是指多个事务之间可以共享锁资源，一般都是在读取数据时添加，也称为读锁（read lock）。

select * from t where id = 1 lock in share mode;

排它锁（ exclusive lock，X锁），也称为写锁（write lock）。

当事务A对数据添加上X锁后，其他事务则不能再对该数据添加任何锁，直到事务A释放数据上的X锁。

增、删、改都会对数据添加X锁，在查询语句中使用for update也会添加X锁。

按加锁粒度区分

全局锁

顾名思义，全局锁是对整个数据库加锁，加锁之后整个库对其他事务都不能进行写操作。MySQL中提供一种添加全局读锁的方式，命令是：​​flush tables with read lock​​(FTWRL)。

-- 加全局读锁
flush tables with read lock;
-- 解锁
unlock tables;

使用场景：全库逻辑备份。

但是使用全局锁进行备份有以下问题：

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

如果全库中所有的表都是innoDB引擎或其他支持事务的存储引擎，可以使用官方的备份工具mysqldump。

当​​mysqldump​​​使用参数​​–single-transaction​​的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。因为有MVCC的支持，这个过程中数据是可以正常更新的。

如果库中存在MyISAM这种不支持事务的存储引擎，则不能使用​​mysqldump​​。

使用set global readonly=true是否可行？

不可以使用​​set global readonly=true​​让全库只读后做逻辑备份。主要有两个原因：

1. ​​read-only​​​一般会用来区分主库和备库，修改​​global​​变量影响较大，不建议修改。
2. 异常处理机制不同，执行FTWRL命令后如果客户端异常断开连接服务器会自动释放全局锁。但是将read-ony设置为true之后则会永久生效，如果客户端异常断开，数据库会一直保持read-only状态。

表级锁（表锁和MDL锁）

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

表锁

lock tables ... read;
lock tables ... write;
-- 解锁
unlock tables;

使用表锁的开销相对较小，加锁快，不会产生死锁；但是加锁粒度大，发生锁冲突的概率更高，并发度更低。

在innoDB存储引擎中不推荐使用表锁，只有在没有事务支持的存储引擎中才会使用，如MyISAM。

元数据锁（MDL）

元数据锁是在MySQL5.5中引入的，MDL不需要显式添加，在对表数据做增删改查操作时添加MDL读锁，在对表进行DDL操作时添加MDL写锁。

元数据锁是为了保证在多个事务操作同一张表时表的元数据一致性。

如果没有元数据锁会存在什么问题呢？

• 事务隔离问题：比如在可重复读隔离级别下，会话A在两次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求。
• 数据同步问题：比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致备库在重做时，先重做​​alter table​​​语句，再重做​​update​​语句时就会出现复制错误的现象。

MDL读锁之间不互斥，因为一张表可以支持多个事务同时增删改查，读锁和写锁、写锁和写锁之间互斥，用来保证对表结构变更的安全性。

在对表执行DDL时，会导致所有的增删改查阻塞。所以在对表字段进行修改或增加字段时，一定要特别小心。

一般我们在对大数据量表做DDL时都会格外注意，以免对线上业务造成影响。但是对小表做DDL操作时同样要小心，比如以下场景：

1. 事务A先启动，这时会对表t加一个MDL读锁；

2. 然后事务B要对表t增加字段，这是需要获取一个MDL写锁，但是由于这时事务A还没有提交，所以MDL读锁没有释放，所以事务B会被阻塞；

3. 如果仅仅是事务B阻塞倒也没什么关系，顶多是DDL晚点执行；但是在这之后的所有对表t的增删改查都会被阻塞，导致表t不能执行任何读写操作。

意向锁

意向锁是加在表级别的一个锁，分为意向共享锁（IS锁）和意向排它锁（IX锁）。

意向锁，顾名思义，就是指明接下来要做的是一个什么类型的操作。

意向共享锁（IS）：在准备给表数据添加一个S锁时，需要先获得该表的IS锁。

意向排他锁（IX）：在准备给表数据添加一个X锁时，需要先获得该表的IX锁。

之所以有意向锁的存在，所以在上面的例子中：

意向锁的出现还有一个主要原因是为了在支持不同粒度锁时，能有更高的效率。

事务A对表T中的某一数据行添加了行锁，这时事务B要对表T添加表锁，但是在添加之前需要先检查是否有其他事务持有该表的X锁，如果持有则要阻塞；

事务B通过遍历表T中的所有行是否有锁，这样判断效率很低，非常耗时。

而意向锁因为是表级别的锁，在事务A在更新数据添加行锁之前，会在表级别由数据库自动添加一个IX锁，那么当事务B在需要获取X锁时，只需要检查表级别是否有IX锁，如果有IX锁代表当前有其他事务正在对表或者表中数据执行写操作，不能加锁成功。

行锁

MySQL中的行锁是在存储引擎层实现的，并不是所有的存储引擎都支持，比如MyISAM引擎中就没有行锁。

行锁顾名思义，是在数据行上添加锁，比如事务A要更新一行数据，先添加了行锁，然后事务B也要更新该行数据，则必须等事务A释放行锁之后才能更新。

行锁的加锁和解锁时机

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

begin;
update t set value = value + 1 where id = 1;
update t set value = value + 1 where id = 2;

begin ;
update t set value = value + 1 where id = 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);

假设我们要执行如下SQL，会如何进行加锁和解锁？

begin;
select * from t where d=5 for update;
commit;

比较好理解的是，这个语句会命中d=5的这一行，对应的主键id=5，因此在select 语句执行完成后，id=5这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行commit语句的时候释放。

由于字段d上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的5行记录上，会不会被加锁呢？

在事务A中执行了3次查询，都是通过​​for update​​获取写锁，并且是当前读。

假设只有id=5这一行加锁，那么三个查询的执行结果如下：

• Q1返回结果为(5,5,5);
• Q2返回结果为(0,0,5),(5,5,5)；
• Q3返回结果为(0,0,5)(1,1,5)(5,5,5)；

那么Q3的结果中查询到id=1的数据，这个现象被称为“幻读”。

这破坏了事务A中​​select * from t where d=5 fot update;​​要把所有d=5的数据锁住的语义。

其次，会存在数据一致性问题。

如果在事务B中将​​binlog​​拿到备库执行会得到不一样的结果。

实际验证一下，得到结果并不是只对id=5这一行加锁，并且对所有的间隙也加了锁。这样就保证不能再插入新的数据。

next-key lock(临键锁)

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。也就是说，我们的表t初始化以后，如果用​​select * from t where for update​​要把整个表所有记录锁起来，就形成了7个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁和临建锁的目的都是用来解决可重复读的问题，如果在读提交级别，间隙锁和临建锁都会失效。

加锁规则

MySQL中数据加锁的规则可以归纳为以下三种：

两个原则

• 加锁的基本单位是next-key lock
• 查找过程中访问到的对象才会加锁

两个优化

• 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
• 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁

一个BUG

• 唯一索引上的范围查询会访问到不满足条件的第一个值为止

死锁和死锁检测

什么是死锁？

在支持并发操作的系统中，不同的线程对资源出现循环依赖，线程之间互相持有对方需要的资源，导致线程都进入无限等待的状态，称之为死锁。

而在数据库中因为有锁机制的存在，同样会导致死锁。比如：

• 事务A先获取到id=1的行锁，然后事务B获取到id=2的行锁；
• 接着事务A要获取id=2的行锁，发现被事务B持有，阻塞；
• 事务B要获取id=1的行锁，发现被事务A持有，阻塞；
• 两个事务进入死锁状态。

当出现死锁后，有两种处理策略：

1. 直接进入等待，直到连接超时，超时时间可通过​​innodb_lock_wait_timeout​​设置。

2. 发起死锁检测，发现死锁后主动回滚死锁中的一个事务，让其他事务正常执行。将参数​​innodb_deadlock_detect​​​设置为​​on​​，表示开启死锁检测。