MySQL锁机制

?MySQL的锁机制用于管理事务对共享资源的并发访问，实现事务的隔离级别。

MySQL的锁比较多，下面我们按照四个维度来介绍相关的锁。

图 MySQL 锁的分类

1 加锁机制

悲观锁	操作数据时，认为其他线程也会对该数据进行更改。于是在获取数据时会先加锁，其他线程会被阻塞直到拿到锁。（先加锁后访问）
乐观锁	操作数据时，认为其他线程不会对数据进行操作。不对数据进行上锁，但是在提交更新时会判断其他线程是否在同时更新或已更新，如果是，则继续等待更新或抛出异常。

表 悲观锁与乐观锁

1.1 悲观锁

悲观锁的实现原理为：

1）在对记录进行修改前，先尝试为该记录加上排他锁；

2）如果加锁失败，则等待或抛出异常（用户决定）；

3）如果加锁成功，则可对记录进行修改，事务完成才会解锁。

4）期间如果有其他事务对该记录做加锁操作，则需要等待当前事务解锁。

优点：

1）更新失败的概率比较低。

缺点：

1. 依赖数据库；
2. 效率比较低；

1.1.1 悲观锁的使用

需求描述：模拟商场系统下单过程，先判断购买数量是否大于库存，是则可以购买，用户完成订单信息填写及付款后，下单成功，库存数量更新，等于现库存减去购买数量。

建立个存储过程来实现上述操作，下面是没有使用悲观锁的代码：

DROP PROCEDURE IF EXISTS buyIPhone;
CREATE PROCEDURE buyIPhone(IN p_count INT)
BEGIN
DECLARE d_stock INT DEFAULT 0;
START TRANSACTION;
SELECT stock INTO d_stock FROM goods WHERE id = 1; -- 初始库存 stock = 10
SELECT SLEEP(10); -- 提交订单等耗时操作
IF d_stock >= p_count THEN
? UPDATE goods SET stock = stock - p_count WHERE id = 1; -- 下单成功，商品减去库存
ELSE?
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT="库存不足";
END IF;
COMMIT;
END;

然后在两个线程中同时调用：CALL buyIPhone(6);

最后结果是，两个都调用成功，但是库存变成了-2。

然后，我们使用悲观锁来改写上面的代码。

图 使用悲观锁后的代码

然后在两个线程中同时调用：CALL buyIPhone(6);

最后的结果是，有一个调用成功，另一个报错：库存不足。商品的库存数量为4。

在上面的代码中，我们使用了“SELECT...FOR UPDATE”语句，这个语句的作用是申请排他锁。注意：在申请排他锁时，如果其他线程有对该结果集中的任何数据使用排他锁或共享锁，那么这个线程会被阻塞。

另外，InnoDB默认是行级锁，但是如果没有指定主键（或索引），那么会把整张表都锁住。

1.2 乐观锁

乐观锁实际上是一种无锁机制。CAS是一种乐观锁机制，全称compare and swap。乐观锁通过比较操作值与预期值（操作原值）是否相等来确定是否执行交换操作。如果相等，则执行，否则不执行。CAS避免了使用传统锁带来的性能开销和死锁问题，提高了程序的并发性能。

优点：

1. 并未真正加锁，效率高。

缺点：

1）如果粒度掌握不好，更新失败的概率会比较高。

图 CAS示意图

下面我们将实现一个需求来复现CAS的ABA问题、自旋及问题优化方案。

需求描述：银行有张表记录了用户名、用于余额信息，用户可以存取钱，现在要求，任何情况下，账号余额不能小于0。

1.2.1 ABA问题

在CAS中，线程1修改某个值时，该值旧值=A，然后继续其他的业务操作。此时线程2将该值修改为B并提交了事务，然后线程3将该值修改为A，这时线程1在提交事务修改前，先判断该值的旧值是否等于现值，发现相等，于是提交修改。

ABA问题就好比：1）某公司会计挪用了公司50w，过了几个月后，再把50w补回去，虽然最后结果没什么变化，但是该会计这样操作已涉嫌犯罪。2）你老婆去老王家出轨了，然后再回到家中。那么你会不会原谅你老婆呢？

ABA 问题的解决一般使用版本号机制来解决。

1.2.2版本号机制

在数据表中加一个表示版本的int类型字段（或时间戳字段），每次提交修改时，版本的值会加1。

CREATE DEFINER=`root`@`localhost` PROCEDURE `depositByVersion`(IN p_amount DECIMAL)
BEGIN
DECLARE d_version_before INT DEFAULT 0;
DECLARE d_version_after INT DEFAULT 0;
DECLARE d_amount DECIMAL DEFAULT 0;
SELECT version,amount INTO d_version_before,d_amount FROM account WHERE id = 1;
SELECT SLEEP(5); -- 其他耗时操作
IF d_amount > -p_amount THEN
SELECT version INTO d_version_after FROM account WHERE id = 1;
IF d_version_before = d_version_after THEN
UPDATE account SET version = version + 1,amount = amount + p_amount WHERE id = 1;
ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='余额已被修改，请重新操作';
END IF;
ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='余额不足';
END IF;
END

在实际开发中，我们常使用“自旋”的方式让事务遇到版本号不一致的问题时能得以继续执行。

DROP PROCEDURE IF EXISTS depositBySpin;
CREATE DEFINER=`root`@`localhost` PROCEDURE `depositBySpin`(IN p_amount DECIMAL)
BEGIN
DECLARE d_version_before INT DEFAULT 0;
DECLARE d_version_after INT DEFAULT 0;
DECLARE d_amount DECIMAL DEFAULT 0;
label: LOOP
SELECT version,amount INTO d_version_before,d_amount FROM account WHERE id = 1;
SELECT SLEEP(5); -- 其他耗时操作
IF d_amount > -p_amount THEN
SELECT version INTO d_version_after FROM account WHERE id = 1;
IF d_version_before = d_version_after THEN
UPDATE account SET version = version + 1,amount = amount + p_amount WHERE id = 1;
LEAVE label;
END IF;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='余额不足';
END IF;
END LOOP label;
END

1.2.3 高并发下乐观锁的问题

在高并发场景下，比如在双十一，1秒钟就可能会有几十万个订单，而这几十个订单都需要同时来修改商品库存，此时将会有大量的修改库存的事务陷入长时间的自旋中，让用户长时间的等待。

此时，我们应该修改事务的粒度，让修改库存这个事务操作的粒度变细些。

2 兼容性

共享锁	多个事务可以同时持有共享锁，用于读取数据行。其他事务也可以获取共享锁，但不能获取排他锁。
排他锁	只能由一个事务持有排他锁，用于修改或删除数据行。其他事务无法同时持有任何类型的锁。

表 共享锁与排他锁

共享锁语法是：LOCK IN SHARE MODE。

排他锁的语法是：FOR UPDATE。

3 颗粒度

全局锁	对整个数据库实例加锁，加锁后数据库处于只读状态。其他的DML语句将会被阻塞。
表锁	对目标表进行加锁。锁定颗粒度大，发生锁冲突的概率最高，并发度最低。
页锁	数据库底层是以页为单位的，一页大小为16KB，一张表可能有很多页。页锁是介于表锁和行锁之间的锁。
行锁	当一个事务要修改或读取某行数据时，会申请该行的记录锁，阻止其他事务对同一行的修改操作。

表锁 按颗粒度划分SQL锁

全局锁一般用于全库的备份。

加全局锁：FLUSH TABLES WITH READ LOCK;

释放锁：UNLOCK TABLES;

3.1 表锁

元数据锁	meta data lock 简称MDL。加锁过程是系统自动控制的。元数据是指表结构，当对一张表进行增删改查时，加MDL读锁，不能修改这张表的结构，当对表结构进行修改时，加MDL写锁。
表共享读锁	不会阻塞其他线程读，但会阻塞线写。LOCK TABLES 表名 READ;
表独占写锁	既会阻塞其他线程读，也会阻塞其他线程写。LOCK TABLES 表名 WRITE。

表 表锁的种类

４模式

记录锁	行锁，对表中的记录加锁。记录锁是锁住索引记录而不是真正的数据记录。属于排他锁。
间隙锁	Gap Lock是行锁的一种，是InnoDB在RR隔离级别下为解决幻读问题引入的锁机制。
临界锁	Next-key 是记录锁和间隙锁的组合。加在某条记录以及这条记录前面间隙上的锁。
意向锁	不与行级锁冲突的表级锁。避免为了判断表是否存在行锁而去全表扫描。
插入意向锁	Insert Intention Lock，是间隙锁的一种，专门针对insert操作。多个事务在同一个索引范围区间插入记录时，如果插入位置不冲突，不会彼此阻塞。
自增锁	实现自增约束，当一个事务要插入数据并获取下一个自增值时，它首先会获取个自增锁，一旦事务获得自增锁，它就可以安全地执行插入操作，并确保每次插入都会得到唯一且连续的自增值，其他事务在等待自增锁被释放前，无法获取下一个自增值。

表 按模式划分SQL锁

4.1 记录锁、间隙锁与临界锁

这些锁都是基于索引实现的。

记录锁：精准加在某一行上。

间隙锁：加载不存在的空闲空间，可以是两个索引记录之间，也可以是第一个索引记录之前，或者最后一个索引之后的无限空间。

临界锁：记录锁与间隙锁的组合。

图 演示的数据记录表

针对主键索引（id字段），可加锁的范围分别是：

记录锁：1，2，5，18

间隙锁：（负无穷,1)、（2,5）、(5,18)、(18,正无穷)

临界锁：（负无穷,1]、[2,5)、[5,18)、[18,正无穷)

4.2 意向锁

没加意向锁时：线程1对某表的某条记录加行锁；线程2对该表加表锁前，需检查当前表是否有对应行锁，如果没有则添加表锁，否则阻塞。会从第一行检索到最后一行，效率极低。

有意向锁：线程1对某表的某条记录加行锁，同时对该表加上意向锁。其他线程在对这张表加锁时，会根据该表所加的意向锁来判断是否可以添加表锁。而不用逐行判断行锁了。

共享意向锁	将在一个范围内共享锁定，阻止其他事务获取排他所。多个事务可以同时持有共享意向锁。
排他意向锁	将在一个范围内请求排他锁，阻止其他事务获取共享锁和排他锁。只能由一个事务持有排他意向锁。

表 意向锁的种类

事务1：申请id=2或5的共享意向锁

START TRANSACTION;
SELECT * FROM teacher WHERE id = 2 OR id = 5 LOCK IN SHARE MODE; -- 申请共享锁
SELECT SLEEP(5);
COMMIT;

事务2：申请id=2或5的排他意向锁

START TRANSACTION;
SELECT * FROM teacher WHERE id = 2 OR id = 5 FOR UPDATE; -- 申请排他锁
SELECT SLEEP(5);
COMMIT;

事务3： 申请id=1 的排他意向锁

START TRANSACTION;
SELECT * FROM teacher WHERE id = 1; -- 申请排他锁
SELECT SLEEP(5);
COMMIT;

1. 执行事务1，事务2。结果是事务2要等事务1提交后，才能获取排他锁。
2. 执行事务1，事务2，事务3。结果是事务3获取排他锁不会受事务1及事务2的影响。

5 MVCC

MVCC（Mutil-Version Concurrency Control）全称多版本并发访问。是一种并发环境下进行数据安全控制的方法，其本质上是一种乐观锁。

MVCC的核心是Undo Log及Read View。

5.1 Undo Log

对记录做了变更操作（INSERT、UPDATE、DELETE）时就会产生一条Undo记录。作用是保护事务在发生异常或手动回滚时可以回滚到历史版本数据，能让你读取某个时间点保存的数据。

InnoDB引擎中，一个聚簇索引（主键索引）的记录之中，一定会有两个隐藏字段trx_id 和 roll_pointer。

trx_id：记录修改这条记录的事务id。

roll_pointer：记录该条记录上一个版本的地址。

图 Undo Log示意图

5.2 Read View

一致性视图，是在读操作前创建的。RC级别下每次读操作前都生成一个Read View。而RR下是在第一次读操作前生成一个Read View。主要有四个字段：1）creator_trx_id,创建当前Read View所对应的事务ID；2）m_ids: 所有当前未提交的事务（活跃事务）id；3）min_trx_id：m_ids 里最小的事务id值；4）max_trx_id：InnoDB需要分配给下一个事务的事务ID值（事务ID是累计递增的）。

在访问某条记录时，按照下面规则来判断该记录在版本链中的某个版本（取trx_id字段）是否可见：

1. trx_id < min_trx_id, 表明生成该版本的事务在生成ReadView前已提交，所以可读。
2. trx_id > max_trx_id, 表明该版本事务在生成ReadView后才生成，所以不可读。
3. min_trx_id <= trx_id <= max_trx_id，分两种情况：

1）trx_id 在m_ids 中

trx_id = creator_trx_id，表明该版本是当前事务产生的，所以可读；

trx_id != creator_trx_id，表明该版本还是活跃事务但不是当前事务，所以不可读。

2）trx_id 不在m_ids 中，所以该版本事务已提交，所以可读。