MySQL的锁机制

锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。

并发控制技术

在数据库中，数据可以允许多个用户同时访问，因此在并发场景下需要确保数据的一致性，并发场景有三种：

• 读-读：多用户并发读不会有问题
• 读-写：可能出现脏读、幻读、不可重复读
• 写-写：并发更新同一行会导致丢失更新

目前有三种并发控制技术：

• 悲观并发控制(PCC)：假定多用户并发的事务在处理时都会引起并发冲突，每次操作数据的时候都会上锁。即先取锁再访问的策略，为数据的安全提供了保证，但是加锁会产生额外的开销，增加死锁的机会，只读型事务不会产生冲突也不需要加锁。
• 乐观并发(OCC)：假定多用户并发的事务在处理时不会彼此互相影响，只在提交时检查有没有其它事务修改了该数据，可以获得更大的吞吐量，但是发生冲突事务就会回滚重新执行。
• 多版本并发(MVCC)：每个写操作都会创建一个新版本的数据，读操作根据可见性规则返回其中一个数据快照。读-写冲突不加锁，非阻塞读的同时避免了脏读和不可重复读，但需要管理和挑选数据版本。

对并发控制有了一定的了解，但需要注意：

• MVCC与悲观和乐观并发控制并不是对立的，很直观的一点MVCC可以在不加锁的情况下解决读-写冲突，并不能解决写-写冲突，写操作还是需要上锁
• MVCC可以与悲观并发或乐观并发结合使用来提高并发的性能

MySQL中实现多版本两阶段锁协议，也就是MVCC+2PL（2PL是悲观并发实现的一种算法，锁只有在commit或rollback的时候释放）

为什么需要锁

• 事务在并发场景下会发生读-读、读-写、写-写三种冲突，而冲突会导致脏读、不可重复读、幻读以及更新丢失等一些问题
• 为了保证数据的完整性和一致性，需要使用锁来支持对共享资源的并发访问，结合多版本并发控制在很多情况下避免了加锁操作

锁分类

MySQL中锁大致可以分为表级别锁以及行级别锁，而不同的数据库引擎支持的锁类型也不同：

• MyISAM 只支持到表级锁
• InnoDB 可以支持到行级锁

表级别锁

表级别对操作的整张表加锁，锁定颗粒度大，资源消耗少，不会出现死锁，但并发度低。

表级锁有两种模式：

• 表共享锁：对同一表的操作不阻塞读，阻塞写
• 表独占锁：对同一表的操作读写阻塞

MyISAM引擎默认支持表级别锁。

表级别的锁有两种：表锁和元数据锁(MDL)。

表锁

LOCK TABLES为当前会话锁定表，UNLOCK TABLES释放被当前会话持有的任何锁。

• 显示加锁方式：lock tables {tb_name} read/write
• 释放锁：unlock tables (连接中断也会自动释放)

MyISAM引擎下隐式加锁：

• 执行SELECT查询自动加共享锁（读锁）
• 执行INSERT、UPDATA、DELETE操作自动加独占锁（写锁）

MyISAM读写锁优先级：默认情况下写锁比读锁具有更高的优先级，即使读请求先到等待队列，写锁也会插入到读锁之前，优先执行写操作，但MyISAM也支持依据生产环境通过修改参数的设置改变读写的优先级

元数据锁（MDL）

元数据锁是MySQL5.5开始引入的锁，是为了解决DDL操作和DML操作之间操作一致性。从锁的作用范围上来说，MDL算是一种表级锁。

MDL加锁过程是系统自动控制，无法直接干预，也不需要直接干预，当我们对一个表做增删改查操作的时候，会自动加MDL读锁；当我们要更新表结构的时候，加MDL写锁。加读锁则所有线程可正常读表的元数据，并且读锁不影响表的增删改查操作，只是不能修改表结构；而加写锁只有拥有锁的线程可以读写元数据，即只拥有锁的线程才能更新表结构，其他线程不能修改结构也不能执行相应的增删改查。

即MDL中，读读共享，读写互斥，写写互斥。

MySQL5.6之后引入了Online DDL 从名字上就可以看出来在线执行DDL，不和其他操作冲突，具体执行流程如下：

• DDL操作需要首先获取MDL写锁
• 将MDL写锁降级成MDL读锁。
• 做真正的DDL操作，这一步十分耗时，由于此时我们持有的是MDL读锁，因此并不会阻塞其他的增删改查操作。
• 做完DDL操作之后，接下来将MDL读锁升级成MDL写锁。
• 释放MDL锁。

注意：Online DDL并不是让你随时随地地可以更新表结构，也还是有限制的，只有在第三步哪里可以继续执行增删改查，即使是在第三步，更新表结构也是不允许的。

查看表级锁争用情况：SHOW STATUS LIKE 'table%'；

总之表级锁因为锁的粒度大，若一个事务执行时间过长，很可能会导致后面对这个表的请求全部阻塞。

行级别锁

InnoDB支持行级别锁，锁粒度小并发度高，但是加锁开销大也很可能会出现死锁，锁模式：

• 共享锁(读锁) S：对同一行的操作读不阻塞，阻塞写
• 排它锁(写锁) X：对同一行的操作读写都会阻塞
• 意向共享锁 IS：一个事务想要加S锁时必须先获得该表的IS
• 意向排它锁 IX：一个事务想要加X锁时必须先获得该表的IX

为什么需要意向锁：意向锁是表级别的锁，用来标识该表上有数据被锁住或即将被锁，对于表级别的请求(LOCK TABLE…)，就可以直接判断是否有锁冲突，不需要逐行检查锁的状态。

InnoDB的默认隔离级别RR(可重复读)，在RR下读数据有两种方式：

1）快照读：在MVCC下，事务开启执行第一个SELECT语句后会获取一个数据快照，直到事务结束读取到的数据都是一致的

• 普通的 select… 查询都是快照读

2）当前读：读取的数据的最新版本，并且在读的时候不允许其它事务修改当前记录

• select… lock in share mode(读锁)
• select… for update(写锁)

加锁方式

• 普通 select… 查询 (不加锁)
• 普通 insert、update、delete… (隐式加写锁)
• select…lock in share mode (加读锁)
• select…for update (加写锁)

解锁

• 提交/回滚事物（commit/rollback）
• kill 阻塞进程

注：以下行级锁分析都默认RR(可重复读)的事物隔离级别。

锁加在索引上

InnoDB的行锁是通过给索引上的索引项加锁来实现的。

即使在建表的时候没有指定主键，InnoDB会默认创建一个DB_ROW_ID的自增字段为表的主键，并且其主键索引（聚簇索引）为GEN_CLUST_INDEX。

主键索引也被称为聚簇索引。

可以看下面例子，涉及到回表对聚簇索引的索引项也会加锁：

行级锁算法

• Record Lock：对对应的索引记录项加锁
• Gap Lock：对索引项之间的间隙加锁，加锁之后间隙范围内不允许插入数据，防止发生幻读
• Next-key Lock：可以理解为Record Lock+Gap Lock（InnoDB行锁默认加的是 Next-key Lock）

举个例子更好理解：

现在你可能已经知道了：如果在加Record Lock的基础之上再加上Gap Lock问题就解决了。

通过上面这个例子，我们可以看到：

• record lock 可以锁一个存在的索引项
• gap lock 锁索引项之间的间隙，可以防止幻读（左开右开区间）
• next-key lock 上面两个锁相加，innodb默认加锁单位（左开右闭区间）

加锁规则

行级锁默认加 next-key lock，查询过程中访问到的索引项都会加锁，而根据不同的索引也有不同的加锁规则：

• 唯一索引等值查询：当索引项存在时，next-key lock 退化为 record lock；当索引项不存在时，默认 next-key lock，访问到不满足条件的第一个值后next-key lock退化成gap lock
• 唯一索引范围查询：默认 next-key lock，(特殊’<=’ 范围查询直到访问不满足条件的第一个值为止)
• 非唯一索引等值查询：默认next-key lock ，索引项存在/不存在都是访问到不满足条件的第一个值后next-key lock退化成gap lock
• 非唯一索引范围查询：默认 next-key lock，向右访问到不满足条件的第一个值为止

针对这几种情况分别举例说明一下，假设我有以下数据：

在上面的数据表我们可以得到5个next-key lock 区间：

唯一索引(id)：(-∞,1]，(1,4]，(4,6]，(6,9] ,（9,+supremum]

非唯一索引(age)：(-∞,18]，(18,21]，(21,23]，(23,26] ,（26,+supremum]

唯一索引等值查询：

唯一索引范围查询：

非唯一索引等值查询：

非唯一索引范围查询：

你会发现上面例子中：

• 唯一索引的查询用的是 select … for update
• 非唯一索引的查询用的是 select … lock in share model

for update 加的是写锁，写锁默认认为会对数据做更改，不管查询有没有涉及到回表都会对聚簇索引(主键索引)加锁

lock in share model 加的是读锁，如果没有涉及到回表（像覆盖索引），不会对聚簇索引(主键索引)加锁

如果上面例子中非唯一索引的查询用的是 select … for update，还需要分析聚簇索引(主键索引)的加锁情况。

死锁

死锁指的是两个或两个以上的事务在执行过程中争抢锁资源而造成相互等待的情况。

表锁不会出现死锁，主要还是针对InooDB的行锁，可以看下面的例子：

监控分析锁问题

# 查询InnoDB锁的整体情况
# 可以重点查看Innodb_row_lock_waits和Innodb_row_lock_time_avg这两个值
# 如果数值较大，说明锁之间的竞争大
show status like 'innodb_row_lock%';

#可以通过INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS这三个表
#分析可能存在的锁的问题
select * from information_schema.INNODB_TRX; # 查看所有事物
select * from information_schema.INNODB_LOCKS; # 查看锁
select * from information_schema.INNODB_LOCK_WAITS; # 查看锁等待

解决死锁

• 超时等待，事务超时自动回滚(innodb_lock_wait_timeout 默认50s)
• 主动死锁检测，事务请求锁的时候采用 wait-for graph 等待图的方式进行死锁检测（innodb_deadlock_detect 默认on）
• 发现死锁也可以人为 kill 进程