mysql锁
这一节，我们来聊一下mysql锁的内容。mysql锁是为了协调多个用户访问同一个资源，保障并发时的一致性和有效性。
按照锁的范围划分，我们可以分为
- 全局锁
- 表锁
- 行锁
全局锁
全局锁是在整个数据库加上读锁。让数据库处于只读状态，别的进程执行一下命令会阻塞住：数据更新语句（增删改查），数据定义语句（建表，表结构修改）和更新类事务的提交语句。
全局锁的语句为：
FLUSH TABLES WITH READ LOCK;
简称 FTWRL, 解锁语句为：
UNLOCK TABLES;
全局锁使用场景是做全库备份，让整个库只读，这听上去很危险
- 在主库备份，会导致数据无法写入，业务停摆
- 在从库备份，从库不能同步binlog中的日志，会导从库数据延迟。
那么为什么还要使用全局锁呢？这是为了防止数据不一致。举个栗子： 比如一个购物网站，其中有两张表 account(账户表)和order（订单表），我们下了总价100元的订单，操作步骤如下：
- 1）account表中减去100
- 2）order表中新增一个价值100的订单
现在我们不使用全局锁备份数据。在备份时，正好有人下了个100元的订单，那么备份出来的数据有如下几种情况：
- 1）account表和order表同时备份到（运气真好，正常）
- 2）account表和order表都没备份到 (下单失败，没赚也没损失，能接受)
- 3）account表没备份，order表备份（没花钱，白赚了100元的物品，商家哭晕在厕所）
- 4）account表备份，order表没备份（花了钱，没订单，投诉去，商家又哭晕在厕所）
也就是说如果不加锁，备份的数据会可能不会在同一个逻辑点，数据的逻辑是不一致的。那么有没有更好的备份方案呢，既可以备份且保持数据一致性，又可以不影响业务运行？还真有这样一个方案：
使用官方自带的mysqldump工具，使用时加上--single-transaction。
使用时会在启动一个事务，来保证一致性。由于MVCC的支持，这个过程是可以正常更新的，不用停业务。
当然此方法仅支持带事务功能的存储引擎，MyISAM引擎就不支持。
小结：
全局锁目的
- 保证数据一致性。
全局锁使用场景：
- 在备份库时使用
语句为：
- FLUSH TABLES WITH READ LOCK; //加锁
- UNLOCK TABLES; // 解锁
当存储引擎支持事务时，可以使用如下工具做替换方案
- mysqldump工具加--single-transaction
表级锁
表级锁，顾名思义就是锁住整张表，MYSQL中表锁分为两张情况：表锁和原数据锁（MDL）。
表锁
表锁又分为表读锁和表写锁。
表读锁语句：
LOCK TABLES [tablename] READ
表写锁的语句:
LOCK TABLES [tablename] WRITE
解锁语句：
UNLOCK TABLES
在还没出现更细的锁颗粒度时，我们常用来处理并发情况，但在InnerDB这种数据引擎下一般不用。
表级别的读锁和写锁的区别
我们先新建一个数据库：
CREATE TABLE `t16` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL COMMENT '唯一索引',
`b` int(11) NOT NULL,
`c` int(11) NOT NULL COMMENT '普通索引',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_a` (`a`) USING BTREE,
KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
再插入一条纪录：
INSERT INTO `t16`(`a`,`b`,`c`) VALUES(1,1,1);
我们先来看一下研究一下读锁：
| 事务A | 事务B |
|---|---|
| LOCK TABLES t16 READ; Query OK, 0 rows affected (0.00 sec) 给表加读锁 |
|
| SELECT * FROM t16; 1 row in set (0.00 sec) 正常返回结果 |
SELECT * FROM t16; 1 row in set (0.00 sec) 正常返回结果 |
| INSERT INTO t16(a,b,c) VALUES(2,2,2); ERROR 1099 (HY000): Table 't16' was locked with a READ lock and can't be updated 报错 |
INSERT INTO t16(a,b,c) VALUES(3,3,3);锁住 |
| UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) 解锁 |
Query OK, 1 row affected (1 min 18.19 sec)写入成功 |
我们再来研究一下写锁：
| 事务A | 事务B |
|---|---|
| LOCK TABLES t16 WRITE; Query OK, 0 rows affected (0.00 sec) 给表加写锁 |
|
| SELECT * FROM t16; 2 row in set (0.00 sec) 正常返回结果 |
SELECT * FROM t16;等待 |
UNLOCK TABLES;解锁 |
2 rows in set (58.85 sec)正常返回结果 |
| LOCK TABLES t16 WRITE; Query OK, 0 rows affected (0.00 sec) 给表加写锁 |
|
| INSERT INTO t16(a,b,c) VALUES(2,2,2); Query OK, 1 row affected (0.00 sec) 正常 |
INSERT INTO t16(a,b,c) VALUES(4,4,4);锁住 |
| UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) 解锁 |
Query OK, 1 row affected (1 min 18.19 sec)写入成功 |
根据上面的栗子，我们可以得出下面这个逻辑：
| 本事务读 | 本事务写 | 其他事务读 | 其他事务写 | |
|---|---|---|---|---|
| 加读锁后 | 正常 | 报错 | 正常 | 等待 |
| 加写锁后 | 正常 | 正常 | 等待 | 等待 |
原数据锁（MDL）
MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。 因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
- 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
- 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
我们在工作中，很多时候需要考虑MDL的存在，否则可能导致锁等待或者连接长时间打满的情况。我们来看下面的栗子：
| session1 | session2 | session3 |
|---|---|---|
| SELECT a,b,SLEEP(60) FROM t16; | ||
| ALTER TABLE t16 ADD COLUMN f int; | ||
| SELECT * FROM t16 WHERE id=1; | ||
4 rows in set (4 min 0.02 sec)4条纪录所以4分钟后返回结果 |
Query OK, 0 rows affected (3 min 57.35 sec)session1执行完后立即执行 |
1 row in set (3 min 35.10 sec)session2执行完后立即执行 |
session1中又一条慢sql需要100s返回，他导致了session2中的修改表结构的语句阻塞，而session2中的语句又导致了，其他session中的语句的阻塞。所以短时间内数据库连接很容易被打满了。那要怎么做呢？可以使用kill语句来强制结束session1或session2中的语句。 因此对于开发来说，在工作中应该尽量避免慢查询、尽量保证事务及时提交、避免大事务等，当然对于 DBA 来说，也应该尽量避免在业务高峰执行 DDL 操作。
小结：
标级锁分为表锁和原数据锁（MDL） 表级别读锁和写锁的区别为
- 加读锁，本事务可正常读，写报错，其他事务可正常读，写等待。
- 加写锁，本事务正常读写，其他事务读写等待。
元数据锁
- 在修改表结构时，会加上MDL写锁。
- 尽量不要在高峰期执行修改表结构的语句，容易引起连接打满。
行级锁
两阶段锁
** 在Innodb中，行锁是在需要时才加上，在事务提交时解锁，这就是两阶段锁的协议。**
我们知道这个设定对我们使用事务有什么帮助呢？ 如果你的事务要锁多行，需要把最可能造成锁冲突的锁往后放 举个栗子： A顾客需要在电影院B买票，我们简化一个流程：
- A顾客账户扣钱
- B电影院账户加钱
- 插入一条日志纪录
如果单从锁影响并发的方面考虑，应该如果规划他们之间的顺序呢？
根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。
共享锁和排他锁
Innodb的锁按照功能分，可以分为共享锁（读锁）和排他锁（写锁）。
- 共享锁：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁。
- 排他锁：允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享读锁和排他写锁。
对于普通 select 语句，InnoDB 不会加任何锁，事务可以通过以下语句显式给记录集加共享锁或排他锁：
- 共享锁：select * from table_name where … lock in share mode;
- 排他锁：select * from table_name where … for update;
- update，delete 语句也会加上排他锁
RC隔离级别下的锁
接下来我们分析一下RC隔离级别下的锁的情况。 我们分为三种情况
- 无索引
- 唯一索引
- 非唯一索引
测试表我们沿用上面的t16表，其中a唯一索引，b无索引，c非唯一索引。清空表数据，并在表中加入几条数据
truncate table t16;
insert into t16(a,b,c) values (1,1,1),(2,2,2),(3,3,3),(4,4,3);
无索引
| session1 | session2 |
|---|---|
| set session transaction_isolation='READ-COMMITTED';/* 设置会话隔离级别为 RC*/ | set session transaction_isolation='READ-COMMITTED';/* 设置会话隔离级别为 RC*/ |
| begin; | begin; |
SELECT * FROM t16 WHERE b=1 for update;正常 |
|
SELECT * FROM t16 WHERE b=3 for update;等待 |
|
| commit; | session1结束，结果正常返回 |
| commit; |
表面看来，session1只给b=1加了排他锁，实际在没有索引的清空下，他给整张表加了排他锁。下图是加锁的逻辑图：
没有索引的情况下，InnoDB 的当前读会对所有记录都加锁。所以在工作中应该特别注意 InnoDB 这一特性，否则可能会产生大量的锁冲突。
唯一索引
| session1 | session2 |
|---|---|
| set session transaction_isolation='READ-COMMITTED';/* 设置会话隔离级别为 RC*/ | set session transaction_isolation='READ-COMMITTED';/* 设置会话隔离级别为 RC*/ |
| begin; | begin; |
SELECT * FROM t16 WHERE a=1 for update;正常 |
|
SELECT * FROM t16 WHERE a=2 for update;正常 |
|
SELECT * FROM t16 WHERE c=1 for update;等待 |
|
| commit; | session1结束，结果正常返回 |
| commit; |
session1 给了 a=1 这一行加了排他锁，在 session2 中请求其他行的排他锁时，不会发生等待；但是在 session2 中请求 a=1 这一行的排他锁时，会发生等待。看下图：
如果查询的条件是唯一索引，那么 SQL 需要在满足条件的唯一索引上加锁，并且会在对应的聚簇索引上加锁。
非唯一索引
| session1 | session2 |
|---|---|
| set session transaction_isolation='READ-COMMITTED';/* 设置会话隔离级别为 RC*/ | set session transaction_isolation='READ-COMMITTED';/* 设置会话隔离级别为 RC*/ |
| begin; | begin; |
SELECT * FROM t16 WHERE c=3 for update;正常 |
|
SELECT * FROM t16 WHERE a=2 for update;正常 |
|
SELECT * FROM t16 WHERE a=4 for update;等待 |
|
| commit; | session1结束，结果正常返回 |
| commit; |
我们在满足条件 c=3 的数据上加了排他锁，如上面结果，就是第 3、4 行。因此第 1、2 行的数据没被锁，而 3、4 行的数据被锁了。如下图：
如果查询的条件是非唯一索引，那么 SQL 需要在满足条件的非唯一索引上都加上锁，并且会在它们对应的聚簇索引上加锁。
RR隔离级别下的锁
在研究RR隔离级别下，我们先来看一道小题目：
| session1 | session2 |
|---|---|
| set session transaction_isolation='READ-COMMITTED';/* 设置会话隔离级别为 RC*/ | set session transaction_isolation='READ-COMMITTED';/* 设置会话隔离级别为 RC*/ |
| begin; | begin; |
SELECT * FROM t16 WHERE c=3 for update;Result1 |
|
| INSERT INTO t16(a,b,c) VALUES(5,5,3); | |
| commit; | |
SELECT * FROM t16 WHERE c=3 for update;Result1 |
|
| commit; |
Result1和Result2分别是多少？
我们经过测试Result1为:
我们经过测试Result2为:
我们发现Result2比Result1多了一行，这也就是我们所讲的幻读。
那为什么会出现幻读，我们来看下面一张图：
从图中可以看出，RC 隔离级别下，只锁住了满足 c=3 的当前行，而不会对后面的位置（或者说间隙）加锁，因此导致 session1 的写入语句能正常执行并提交。
为了解决幻读问题，RR隔离级别引入了间隙锁。
我们重新建一张表，并插入数据：
CREATE TABLE `t17` (
`id` int(11) NOT NULL,
`a` int(11) NOT NULL COMMENT '唯一索引',
`b` int(11) NOT NULL,
`c` int(11) NOT NULL COMMENT '普通索引',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_a` (`a`) USING BTREE,
KEY `idx_c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
INSERT INTO t17(id, a, b, c) VALUES(1,1,1,1),(2,2,2,2),(4,4,4,4),(6,6,6,4);
我们一样分为三种情况：
- 无索引
- 非唯一索引
- 唯一索引
无索引
| session1 | session2 | session3 |
|---|---|---|
| set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ | set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ | set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ |
| begin; | begin; | begin; |
SELECT * FROM t17 WHERE b=1 for update;正常 |
||
SELECT * FROM t17 WHERE a=4 for update;等待 |
||
INSERT INTO t17(id, a, b, c) VALUES(5,5,5,5);等待 |
||
| commit; | session1结束，结果正常返回 |
session1结束，结果正常返回 |
| rollback; | rollback; |
其加锁的逻辑图如下：
如图，所有记录都有 X 锁，除此之外，每个 GAP 也被加上了 GAP 锁。因此这张表在执行完 select * from t17 where b=1 for update; 到 commit 之前，除了不加锁的快照读，其它任何加锁的 SQL，都会等待，如果这是线上业务表，那就是件非常恐怖的事情了。
RR 隔离级别下，非索引字段做条件的当前读不但会把每条记录都加上 X 锁，还会把每个 GAP 加上 GAP 锁。再次说明，条件字段加索引的重要性。
非唯一索引
| session1 | session2 | session3 |
|---|---|---|
| set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ | set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ | set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ |
| begin; | begin; | begin; |
SELECT * FROM t17 WHERE c=4 for update;正常 |
||
SELECT * FROM t17 WHERE a=4 for update;等待 |
||
INSERT INTO t17(id, a, b, c) VALUES(5,5,5,4);等待 |
||
| commit; | session1结束，结果正常返回 |
session1结束，结果正常返回 |
| rollback; | rollback; |
其加锁的逻辑图如下：
与 RC 隔离级别下的图相似，但是有个比较大的区别是：RR 隔离级别多了 GAP 锁。
如上图，首先需要考虑哪些位置可以插入新的满足条件 c=4 的项：
- 由于 B+ 树索引是有序的，因此 [2,2]（代表 c 和 id 的值，后面就不一一说明了）前面的记录，不可能插入 c=4 的记录了；
- [2,2] 与 [4,4] 之间可以插入 [4,3]；
- [4,4] 与 [4,6] 之间可以插入 [4,5]；
- [4,6] 之后，可以插入的值就很多了：[4,n](其中 n>6) ；
为了保证这几个区间不会插入新的满足条件 c=4 的记录，MySQL RR 隔离级别选择了 GAP 锁，将这几个区间锁起来。 而上面，我们插入了（id=5, c=4）的数据，所以被锁住了。
唯一索引
RR 隔离级别下，非索引字段做条件的当前读不但会把每条记录都加上 X 锁，还会把每个 GAP 加上 GAP 锁。再次说明，条件字段加索引的重要性。
因此以唯一索引为条件的当前读，不会有 GAP 锁。所以 RR 隔离级别下的唯一索引当前读加锁情况与 RC 隔离级别下的唯一索引当前读加锁情况一致。这里就不再实验了。
间隙锁在非唯一索引下引起的问题
| session1 | session2 |
|---|---|
| set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ | set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ |
| begin; | begin; |
SELECT * FROM t17 WHERE c=4 for update;正常 |
|
INSERT INTO t17(id, a, b, c) VALUES(5,5,5,5);等待 |
|
| commit; | session1结束，结果正常返回 |
| rollback; |
我们发现插入语句竟然等待了，为什么？ 因为间隙锁锁的是位置，根据上面这张图，session1的sql语句锁的范围是 “(2,无穷大]”，导致凡在这个区间都会被锁住。
所以间隙锁的引入会导致锁的范围更大，影响并发。 有很多公司使用RC隔离级别+日志ROW模式。
小结：
1）行级锁是两阶段的锁，一个事务中在需要时锁住，在commit时释放锁。
2）行级锁按照功能分为共享锁和排他锁。
3）在RC隔离级别下，会对数据加上纪录锁，但会有幻读的问题
4）在RR隔离级别下，会对数据加上纪录锁和间隙锁，解决了幻读的问题，但影响并发。
死锁
死锁产生的原因
死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。 我们使用t17表来举个栗子，看如何会产生死锁：
| session1 | session2 |
|---|---|
| set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ | set session transaction_isolation='REPEATABLE-READ';/* 设置会话隔离级别为 RR*/ |
| begin; | begin; |
SELECT * FROM t17 WHERE c=4 for update;sql1正常 |
SELECT * FROM t17 WHERE c=1 for update;sql2正常 |
INSERT INTO t17(id, a, b, c) VALUES(5,5,5,5);sql3等待 |
|
INSERT INTO t17(id, a, b, c) VALUES(7,7,7,1);sql4报死锁错误 |
我们来分析一下，刚开始执行sql1时 session1锁住的范围时 (2,正无穷]，执行sql2后session2锁住范围时(负无穷大,2) , sql3范围在session1锁的范围内，所以等待session1锁释放，而此时，sql4又在等待session2的释放。也就是session1和sesison2相互等待各自释放。所以就成了死锁了。
死锁的解决方案：
InnoDB 中解决死锁问题有两种方式：
- 检测到死锁的循环依赖，立即返回一个错误（这个报错内容请看下面的实验），将参数 innodb_deadlock_detect 设置为 on 表示开启这个逻辑；
- 等查询的时间达到锁等待超时的设定后放弃锁请求。这个超时时间由 innodb_lock_wait_timeout 来控制。默认是 50 秒。
一般我们采用第一种方案，因为第二种方案等待50秒时间过长，业务上无法接受，如果把时间调少，比如1秒，又有可能会误杀一些正常的锁。但第一种方案也会造成额外的cpu开销。
降低死锁的方案：
- 更新 SQL 的 where 条件尽量用索引；
- 基于 primary 或 unique key 更新数据；
- 减少范围更新，尤其非主键、非唯一索引上的范围更新；
- 加锁顺序一致，尽可能一次性锁定所有需要行；
- 将 RR 隔离级别调整为 RC 隔离级别。
小结
1） 死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。 2） 我们一般使用InnoDB自带的检索机制来检索是否死锁。
小练习：
有三张表，用户账户表，商品库存表，订单表。现在用户需要买商品，购买流程是
- 用户账户扣除相应金额，如果金额不够购买失败。
- 商品库存扣除相应库存，如果库存不够购买失败。
- 订单表新增一个订单纪录。
我们改如何操作这些表来完成上面的购买流程，并且支持更多的并发？
