一、 概述
1. 为什么要有数据库锁
数据库是一个多用户使用的共享资源，因此经常会发生多个数据库事务并发执行的情况，这些事务在并发执行时可能会引发一系列的数据一致性问题，比如常见的脏读、幻读、不可重复读，还有最为严重的脏写现象。
如果一个事务修改了另一个事务尚未提交的数据，就意味着发生了脏写现象。脏写对数据一致性有非常严重的影响，因此MySQL在任何隔离级别下均不允许有脏写现象的存在。
为了避免这些情况的发生MySQL提出两种解决方案，第一种是MVCC，这种机制适用于数据库的读操作，而对于数据库的写操作它却无能为力，因此MySQL提出了第二种解决方案，即使用MySQL锁来确保数据的一致性。 MySQL锁就是让原本并发执行的事务排队执行，这个锁本质上就是一个内存中的结构。
2. MySQL的锁有哪些
MySQL的锁可以根据不同的维度进行分类，如下表：
| 分类 | 锁 |
|---|---|
| 范围 | 行锁、表锁 |
| 算法 | 记录锁、间隙锁、Next-Key Lock |
| 属性 | 共享锁(S锁)、独占锁(X锁) |
| 状态 | 意向共享锁(IS锁)、意向独占锁(IX锁) |
以上只是大致对MySQL锁的一个分类，并不完全严谨，只供参考。
二、 MySQL锁分析
1. 测试用表
lock_demo表
create table lock_demo
(
id int auto_increment primary key,
name varchar(30) null,
age int null
);
create index idx_age on lock_demo (age);
INSERT INTO lock_demo (id, name, age) VALUES (1, '小明', 19);
INSERT INTO lock_demo (id, name, age) VALUES (3, '李华', 25);
INSERT INTO lock_demo (id, name, age) VALUES (6, '小红', 22);
INSERT INTO lock_demo (id, name, age) VALUES (10, '小丽', 13);
INSERT INTO lock_demo (id, name, age) VALUES (11, '小华', 8);
lock_demo2表
create table lock_demo2
(
id int not null primary key,
no int null,
grade int null,
score int null,
constraint idx_unique_no unique (no)
);
create index idx_grade_score on lock_demo2 (grade, score);
INSERT INTO lock_demo2 (id, no, grade, score) VALUES (1, 1, 1, 70);
INSERT INTO lock_demo2 (id, no, grade, score) VALUES (4, 3, 1, 80);
INSERT INTO lock_demo2 (id, no, grade, score) VALUES (7, null, 4, 65);
INSERT INTO lock_demo2 (id, no, grade, score) VALUES (10, 6, 4, 89);
INSERT INTO lock_demo2 (id, no, grade, score) VALUES (11, null, 7, 66);
2. 独占锁(S锁)、共享锁(X锁)
共享锁(S锁)：是读取操作创建的锁。其他用户可以并发读取加锁的数据，但任何事务都不能对加锁的数据进行修改（获取数据上的独占锁），直到该数据上的锁被释放。
独占锁(X锁)：如果事务对数据加上独占锁后，则其他事务不能再对加锁的数据加任何类型的锁。获取独占锁的事务既能读数据，又能修改数据。
下表展示了共享锁与独占锁的兼容性：
| 兼容性 | X锁 | S锁 |
|---|---|---|
| X锁 | 不兼容 | 不兼容 |
| S锁 | 不兼容 | 兼容 |
InnoDB默认在读已提交和可重复读隔离级别下，所有普通的select语句都会使用一致性读(也叫一致性非锁定读，即利用MVCC机制读取数据)，该读取方式不会对数据加锁，其他事务可自由对表中的记录进行改动。 对于数据库的写操作来说，MySQL会自动加X锁，但是如果在我们的业务中存在这么一些场景，需要在读取数据时禁止修改数据或者禁止其它事务读取数据，则我们可以采取手动加锁的方式来进行。
对读取的数据加S锁： SELECT … LOCK IN SHARE MODE;
对读取的记录加X锁： SELECT … FOR UPDATE;
参考以下示例：
3. 表锁、行锁
行锁，顾名思义，就是一锁锁一行或者多行记录的一种锁。InnoDB的行锁是基于索引加的锁，不是针对记录加锁，因此为了使用行锁，必须命中索引。
表锁，对整张表进行上锁。因此如果加的是独占表锁，则其他事务无法访问该表的数据，将会被阻塞。
下表是行锁和表锁的对比：
对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁， 而且这些存储引擎并不支持事务，所以当我们为使用这些存储引擎的表加锁时，一般都是针对当前会话来说的。
InnoDB不仅支持表锁，也支持行锁，并且对事务也有很好的支持，在高并发下相比其他存储引擎有更好的性能。因此我们现在最常用的存储引擎就是InnoDB，之后对锁的讲解及实战都是基于InnoDB储存引擎。
上图右边的注释有个笔误，第一步应该是释放lock_demo2的表锁，而不是释放lock_demo的表锁，注意。
通过以上示例我们可以得出以下的结论 ：
- 手动锁表首先会释放之前拥有的锁，然后才会获取新的表锁
- 未使用索引导致的锁表可能会发生死锁，手动锁表则不会发生死锁
- 表锁和行锁不能混合使用，每个事务只能使用一种锁。
使用行锁的条件
- SQL语句命中索引
- MySQL查询优化器决定使用索引
使用表锁的条件
- 没有命中索引或者索引失效
- 手动开启表锁
- MySQL查询优化器决定不使用索引等其他的情况
4. 意向共享锁(IS)、意向独占锁(IX)
IS锁、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。
意向共享锁IS：当事务准备在某条记录上加S锁时，需要先在表级别添加IS锁。
意向独占锁IX：当事务准备在某条记录上加X锁时，需要先在表级别添加IX锁。
IS锁和IX锁是相互兼容的。
5. 插入意向锁、隐式锁
插入一条数据之前，首先向需要插入的位置加插入意向锁，如果成功则插入数据，失败则阻塞等待，插入意向锁仅用于判断当前是否可以插入数据。如果数据有唯一性限制，则判断表中是否已经存在该数据，如果存在则对该数据加S型锁然后报错。
隐式锁，当执行insert时并不会直接生成锁，而是当有其他的事务读取这条记录并且插入数据的事务未提交时才会生成锁，推迟了锁的生成。这种机制下，当没有其他的事务来读取这条记录时不会生成锁，提高了数据库的插入效率。
6. AUTO- INC锁
创建表的时候我们可能会为某列指定AUTO_INCREMENT属性，之后插入记录时数据库会自动为该列赋予自增的值，这时就会用到AUTO-INC锁。
MySQL在执行插入语句时就加一个表级别的AUTO-INC锁，然后为每条待插入记录的AUTO_lNCREMENT修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC锁释放掉。这样一来一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，从而保证分配的值是递增。
如果我们的插入语句在执行前就可以确定具体要插入多少条记录，那一般采用轻量级锁的方式对AUTO_INCREMEN修饰的列进行赋值。这种方式可以提升数据的插入性能。还可以设置innodb_autoinc_lock_mode参数去设置使用哪种方式。
这两种锁只针对单一的插入语句，一旦插入语句执行完之后锁就会被释放掉，而不会等待事务执行完毕。因此事务完成之后发生回滚，可能会出现id不连续的情况。
7. 记录锁、间隙锁、Next- Key Lock
根据实现算法及使用场景，行锁又分为三种：记录锁、间隙锁、Next-Key Lock。
- 记录锁锁定的是表中的某一条索引记录。
- 间隙锁(Gap Lock)又称之为区间锁，每次锁定的都是一个区间但是不包含记录本身。间隙锁主要解决的是幻读的问题，因此只有在可重读的隔离级别下才可能会使用它。
- Next-Key Lock，有时候我们既想锁住某条记录又想阻止其他的事务在该记录前面的间隙中插入记录，为此InnoDB专门提供了Next-Key Lock来满足这种需求，它的本质就是记录锁和间隙锁的结合。同理因为使用到了间隙锁，所以也只有在可重读隔离级别下才会使用该锁。
以下加锁原则适用于InnoDB存储引擎可重读隔离级别下： - 加锁的基本单位是Next-Key Lock且是前开后闭区间(]。
- 等值查找在没有找到符合条件的数据之后，会把锁转换为间隙锁。
- 使用主键索引和唯一索引等值查询查找到符合条件的记录之后，会把锁转换成记录锁。
- 使用普通索引等值查询查找到符合条件的记录之后，依然会使用Next-Key Lock。
- 只有使用到的对象才会加锁。
- 间隙锁与记录锁冲突，间隙锁与间隙锁相互兼容读，但是却不兼容写。
- 主键索引范围查找，边界值符合条件会将该记录的Next-Key Lock转为记录锁。但是其它类型的索引使用范围查找Next-Key Lock不会转换为记录锁。
通过以下的示例可以加深对这几条原则的理解。
-
主键索引等值查找
-
唯一索引等值查找
-
普通索引等值查找
-
联合索引等值查找
-
主键索引范围查找
此处左边的区间应该是记录锁(6)，和间隙锁(6,10),右边执行会阻塞是因为主键落在了区间(6,10)
此处一个错误，左边的区间应该是(1,3],(3,6),右边的区间是(3,6],(6,10)，（3,6]的区间包含(3,6)，因此可以执行查询。 -
唯一索引范围查找
-
普通索引范围查找
-
联合索引范围查找
-
死锁
InnoDB在检测到死锁之后会选择一个较小的事务(数据改动相对较少的事务)进行回滚以释放该事务持有的锁。
通过以上示例我们可以知道产生死锁的原因：
- 两个事务互相获取对方拥有的锁时会发生死锁。
- 两个事务获取到相同的共享锁锁之后，若又把锁升级成独占锁，会引发死锁。
如何避免死锁：
- 尽量使用读已提交隔离级别，间隙锁和Next-Key Lock会锁住区间大大增加产生死锁的概率。
- 尽量保证不同事务获取锁的顺序相同，即以同样的顺序去更新数据。
- 使用乐观锁来替代使用共享锁。
- 使用索引查询或者修改数据。
- SQL在执行完毕后及时提交或者回滚事务。
9. 半一致性读
半一致性读是一种夹杂在一致性读和锁定读之间的数据读取方式。所谓半一致性是当update语句读取到已经被其他事务加了锁的记录时InnoDB会将该记录的最新版本读出来，然后判断该版本是否与update语句中的搜索条件相匹配。如果不匹配，则不对该记录加锁，从而跳到下一条记录；如果匹配，则再次读取该记录并对其进行加锁。
当隔离级别不大于读已提交(读未提交、读已提交)且执行update语句时将使用半一致性读，这么处理只是为了让update语句尽量少被别的语句阻塞 。
三、锁实战
前面讲了很多锁的概念还有一些小的案列，但是还是理论知识偏多，在真正的业务中还需要结合具体的场景进行SQL语句的加锁分析，在这里分享一个真实场景下如何判断MySQL加锁的案列。
修改用户默认钱包
udg模块是用来存储用户的数字商品，例如优惠券等，优惠券又储存在用户的电子钱包里，每个用户有多个钱包，但是只有一个默认钱包
用户可能会有修改自己默认钱包的需求，因此我们开放了一个修改默认钱包的接口。在修改用户的默认钱包时会涉及到两条记录的修改，分别是用户原先默认的钱包记录和需要被设为默认钱包的记录。下面就这次修改所涉及的SQL进行一次分析，帮助我们尽可能地提高SQL的执行效率。
业务对应的表结构如下：
CREATE TABLE `digital_goods_wallet` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户ID',
`wallet_id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '钱包ID',
`name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '钱包名',
`status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT '钱包状态 ENABLED/DISABLED',
`default_wallet` varchar(10) COLLATE utf8mb4_general_ci NOT NULL COMMENT '是否为默认钱包 1. truefalse用户多个钱包只有一个能是true',
`extend_info` varchar(1000) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '钱包扩展信息，存储json对象',
`create_time` bigint NOT NULL COMMENT '创建时间，时间戳格式',
`update_time` bigint NOT NULL COMMENT '更新时间，时间戳格式',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_wallet` (`user_id`,`wallet_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='虚拟资产钱包表';
我们先写出业务第一步和第二步数据库操作对应的SQL(这里省略了不重要的字段)。
1. update digital_goods_wallet set default_wallet = 'false' where default_wallet = 'true' and user_id = 'U5DCE1CCC5264A40001FA1331';
2. update digital_goods_wallet set default_wallet = 'true' where wallet_id = 'ANddSqZja0XFLKJA6nXnKWil9P2Z321D';
其中为user_id和wallet_id建立了普通联合索引idx_user_wallet(user_id, wallet_id)。
接下来我们分别对这两条SQL进行分析。
先查看第一条SQL语句的执行计划。
通过查询计划我们知道这条SQL使用了联合索引，并且因为它修改了不包含在该联合索引中的字段，因此在读已提交的隔离级别下InnoDB会使用记录锁锁住符合条件的联合索引记录和主键索引记录。在可重读隔离级别下，InnoDB会使用Next-Key Lock和间隙锁锁住联合索引，区间为(small_user_id, U5DCE1CCC5264A40001FA1331]和(U5DCE1CCC5264A40001FA1331, big_user_id)(small_user_id为小于U5DCE1CCC5264A40001FA1331的第一条user_id，big_user_id为大于U5DCE1CCC5264A40001FA1331的第一条user_id),使用记录锁锁住符合条件的主键索引记录。
digital_goods_wallet通过以上分析我们可以发现这条SQL使用了联合索引，再结合我们的业务一个用户不太可能会有太多的钱包，因此即使需要回表也不会有太多的性能消耗。因此这个SQL满足我们的需求，无需优化。
再来分析第二条SQL语句。
如果我们先不使用执行计划直接分析SQL的话，因为索引的最左匹配原则，所以这条SQL不会走联合索引，执行时会发生锁表。为了验证我们的猜想，我们查看该SQL的执行计划。
通过查询SQL的执行计划我们可以发现这条SQL使用了主键索引但是执行计划的type值却是index，也就是说执行这个SQL需要扫描主键索引所在的这棵B+树，也就相当于全表扫描，因此这条SQL在执行时会进行锁表。这显然会大大降低数据库的性能，因此我们结合业务对这条SQL进行优化。
这个SQL发生的业务场景是修改用户的默认钱包，因此我们能获取到要修改钱包的id(wallet_id)和用户id(user_id)，为了避免全表扫描，我们可以在where条件中同时使用user_id和wallet_id进行数据过滤。
修改之后的SQL语句为：
update digital_goods_wallet set default_wallet = 'true' where wallet_id = 'ANddSqZja0XFLKJA6nXnKWil9P2Z321D' and user_id = 'U5DCE1CCC5264A40001FA1331';
我们再次查看优化之后SQL的执行计划。
可以发现修改之后的SQL会使用联合索引。我们对SQL的加锁情况进行分析，在读已提交隔离级别下会使用记录锁锁住符合条件的联合索引记录和主键索引记录。在可重读隔离级别下，会使用Next-Key Lock和间隙锁锁定联合索引，区间为(small_user_id, small_wallet_id)[U5DCE1CCC5264A40001FA1331, ANddSqZja0XFLKJA6nXnKWil9P2Z321D]和(U5DCE1CCC5264A40001FA1331, ANddSqZja0XFLKJA6nXnKWil9P2Z321D)(big_user_id, big_wallet_id)(small_user_id为小于U5DCE1CCC5264A40001FA1331的第一条user_id，small_waller_id为小于ANddSqZja0XFLKJA6nXnKWil9P2Z321D的第一条wallet_id，big_user_id为大于U5DCE1CCC5264A40001FA1331的第一条user_id,big_wallet_id为大于ANddSqZja0XFLKJA6nXnKWil9P2Z321D的第一条wallet_id),会使用记录锁锁定符合条件的主键索引记录。
可以发现经过对第二条SQL的优化可以有效的避免表锁提高数据库的性能。
四、总结
MVCC和锁是解决MySQL并发事务数据一致性问题的两种方案。
事务利用MVCC读取数据的方式称为一致性读，读记录之前进行加锁称为锁定读。select语句InnoDB默认使用一致性读，update语句则在隔离级别不大于读已提交时使用半一致性读。
执行SQL语句时如果使用索引则一般使用行锁否则使用表锁，因此为了提高数据库的并发性能，尽可能地使用索引。
行锁分为记录锁、间隙锁、Next-Key Lock。
InnoDB在读已提交隔离级别下只会使用记录锁，在可重读隔离级别下会使用记录锁、间隙锁和Next-Key Lock且默认使用Next-Key Lock。使用锁会降低数据库并发性能并且可能会引发死锁，用锁需谨慎，发生死锁之后，数据库会默认选择一个事务回滚。
只有在事务提交之后才会释放事务持有的行锁。
414
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
