关于MySQL的行锁(记录锁)/临键锁/间隙锁
1.1 表锁
表锁偏向Myisam存储引擎，开销小，加锁快，无死锁的情况；锁的粒度大，发生锁冲突的概率高，并发度最低。
1.2 行锁
行锁偏向InnoDB引擎，开销大，加锁慢，会出现死锁；锁的粒度小，发生锁冲突的概率小，并发度最高。
1.3 InnoDB和Myisam的不同
- 一是InnoDB支持事务(TRANSACTION,TX)，而Myisam则不支持事务；
- 二是InnoDB采用了行级锁，而Myisam则采用的是表级锁；
- 三是InnoDB采用的是聚簇索引+非聚簇索引，它的索引和数据都存在.idb文件里，而Myisam只采用非聚簇索引，索引存在.MYI文件，数据则存在.MYD文件中。
1.4 复习事务的ACID属性
- A(Atomicity)：原子性(一个事务中的全部语句要么全部执行，要么全部不执行，不能只执行一半)
- C(Consistency)：一致性(在事务开始和完成时，数据都必须保持一致状态)
- I(Isolation)：隔离性(数据库提供一定的隔离机制，保证并发环境下的操作也是独立运行的)
- D(Durable)：持久性(事务完成之后，数据已经写入磁盘，它对数据的修改是永久性的)
1.5 事务的隔离级别
//TRANSACTION_NONE 不使用事务。
//TRANSACTION_READ_UNCOMMITTED 允许脏读。
//TRANSACTION_READ_COMMITTED 防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别(RC)
//TRANSACTION_REPEATABLE_READ 可以防止脏读和不可重复读(RR)
//TRANSACTION_SERIALIZABLE 可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率
一般不会使用事务串行化，因为它是使用的表锁，会降低数据库的效率，一般隔离级别只到TRANSACTION_REPEATABLE_READ(RR)，MySQL的默认隔离级别也是RR，幻读则是通过临键锁和间隙锁来解决的 。
1.6 索引失效行锁升级为表锁
CREATE TABLE `my_lock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `my_lock` VALUES (1, '1000');
INSERT INTO `my_lock` VALUES (2, '2000');
INSERT INTO `my_lock` VALUES (3, '3000');
比如以这个表为例，当我们执行如下的sql语句
update my_lock set id=10 where name=1000;
这个sql语句，name本应该是varchar，但是我们使用的是int类型的数据，mysql会自动进行类型转换，varchar类型使用int类型去赋值是很严重的情况，它会导致索引失效，因此锁的是整个表。
1.7 间隙锁、临键锁和记录锁
需要说明的是：下面所有执行的SQL都正确测试过，测试环境为MySQL5.6.50版本。至于结论，不一定要以我的为准，都是我自己分析的，不一定正确。
select ... for update -- 在读的时候给数据加上写锁
1.7.1 案例数据如下
| id(主键) | a(普通索引) | (无索引) |
|---|---|---|
| 5 | 5 | 5 |
| 10 | 10 | 10 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
对应的SQL语句如下：
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `t_index` (`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO `t` VALUES (5, 5, 5);
INSERT INTO `t` VALUES (10, 10, 10);
INSERT INTO `t` VALUES (15, 15, 15);
INSERT INTO `t` VALUES (20, 20, 20);
INSERT INTO `t` VALUES (25, 25, 25);
COMMIT;
1.7.2 临键锁和间隙锁简介
在可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的临键锁（next-key lock）来实现的。
加锁规则有以下特性，我们会在后面的案例中逐一解释：
//1.加锁的基本单位是临键锁（next-key lock）,它是前开后闭原则，例如(5,10]
//2.插叙过程中访问的对象会增加锁
//3.唯一索引上的等值查询，临键锁升级为行锁，如(5,10]的临键锁升级为10的行锁
//4.普通索引上的等值查询，会从当前的key开始向右遍历找到第一个值不满足查询需求的记录，并且临键锁会退化为间隙锁
//5.唯一索引上的范围查询会访问到不满足条件的第一个值为止
1.7.3 针对键是唯一键(比如主键索引)时
(1) 等值查询
1' 当表中已经有该数据
BEGIN;
select * from t where id=5 for update;
加的临键范围锁应该是(5,10]，因为使用的是等值匹配，临键锁升级为记录锁(行锁)，因此只会有5这一条记录锁。
INSERT INTO t VALUES(4,4,4); -- 成功
UPDATE t SET d=9 WHERE id=5; -- 阻塞
INSERT INTO t VALUES(6,6,6); -- 成功
UPDATE t SET d=9 WHERE id=10; -- 成功
2' 当表中没有该数据
BEGIN;
select * from t where id=6 for update;
加的临键锁的范围应该是(5,10]，因为使用的是等值匹配，如果范围内的最大值仍旧不满足条件，则退化为间隙锁(5,10)。
INSERT INTO t VALUES(4,4,4); -- 成功
UPDATE t SET d=9 WHERE id=5; -- 成功
INSERT INTO t VALUES(6,6,6); -- 阻塞
INSERT INTO t VALUES(9,9,9); -- 阻塞
UPDATE t SET d=9 WHERE id=10; -- 成功
(2)范围查找
BEGIN;
select * from t where id>5 and id<19 for update;
查询出来的结果最小和最大分别是10和15，它的临近的键是5和20，原则是左闭右开，因此加的临键锁的范围是(5,20]。
UPDATE t SET c = 5 WHERE id = 5; -- 执行成功
INSERT INTO t VALUES(19,19,19); -- 执行失败，阻塞
UPDATE t SET c = 20 WHERE id = 20; -- 执行失败，阻塞
INSERT INTO t VALUES(21,21,21); -- 执行成功
(3)间隙查询
1' 示例1
BEGIN;
select * from t where id>6 and id<9 for update;
结果查询出来的结果是null，因此会给区间两侧的键5和10加上临键锁，也就是(5,10]。
UPDATE t SET c=9 WHERE id=10; -- 执行失败，阻塞
UPDATE t SET c=9 WHERE id=5; -- 执行成功
INSERT INTO t VALUES(11,11,11); -- 执行成功
INSERT INTO t VALUES(4,4,4); -- 执行成功
2' 示例2
BEGIN;
select * from t where id>=10 and id <11 for update;
查询出来的结果是10，其两侧的键是5和15，因此加上临键锁(5,10]和(10,15]，而由于左侧使用等值查询匹配了10，因此(5,10]会升级为行锁(记录锁)。最后锁的范围就是[10,15]。
INSERT INTO t VALUES(9,9,9); -- 执行成功
UPDATE t SET d=10 WHERE id =10; -- 执行失败，被阻塞
INSERT INTO t VALUES(13,13,13); -- 执行失败，被阻塞
UPDATE t SET d=10 WHERE id =15; -- 执行失败，被阻塞
1.7.4 针对普通索引时
(1) 等值查找
1' 当数据库中有这条记录
BEGIN;
select * from t where c=10 for update;
加的是临键锁(5,10]，但是它会从10开始向右找到第一个不满足条件的记录，也就是15，并且退化为间隙锁。最后锁的范围是(5,15)。
UPDATE t SET d=12 WHERE c=5; -- 执行成功
INSERT INTO t VALUES(6,6,6); -- 执行失败，被阻塞
UPDATE t SET d=12 WHERE c=10; -- 执行失败，被阻塞
INSERT INTO t VALUES(11,11,11); -- 执行失败，被阻塞
INSERT INTO t VALUES(14,14,14); -- 执行失败，被阻塞
UPDATE t SET d=15 WHERE c=15; -- 执行成功
INSERT INTO t VALUES(16,16,16); -- 执行成功
2' 当数据库中没这条记录
BEGIN;
select * from t where c=9 for update;
加的临键锁为(5,10]，但是它会从9开始向右匹配到第一个不匹配的值，也就是10，并退化为间隙锁，因此锁的范围为(5,10)。
INSERT INTO t VALUES(4,4,4); -- 成功
UPDATE t SET d=12 WHERE c=5; -- 成功
INSERT INTO t VALUES(6,6,6); -- 阻塞
INSERT INTO t VALUES(9,9,9); -- 阻塞
UPDATE t SET d=12 WHERE c=10; -- 成功
(2) 间隙查询
BEGIN;
select * from t where c>11 and c<13 for update;
这种情况下，匹配的就是普通的临键锁，也就是(10,15]。
INSERT INTO t VALUES(9,9,9); -- 成功
UPDATE t SET d=10 WHERE c=10; -- 成功
INSERT INTO t VALUES(11,11,11); -- 阻塞
INSERT INTO t VALUES(12,12,12); -- 阻塞
INSERT INTO t VALUES(13,13,13); -- 阻塞
INSERT INTO t VALUES(14,14,14); -- 阻塞
UPDATE t SET d=10 WHERE c=15; -- 阻塞
INSERT INTO t VALUES(16,16,16); -- 成功
(3) 范围查找
BEGIN;
select * from t where c>=10 and c<11 for update;
加的临键锁有(5,10]和(10,15]，而由于c不是唯一索引，因此(5,10]不会退化为10的行锁，由于不是等值查询，因此不会向右搜索到一个不匹配的值。因此锁的范围还是(5,15]。
INSERT INTO t VALUES(4,4,4); -- 执行成功
INSERT INTO t VALUES(6,6,6); -- 执行失败，被阻塞
UPDATE t SET d=10 WHERE c=5; -- 执行成功
UPDATE t SET d=10 WHERE c=15; -- 执行失败，被阻塞
INSERT INTO t VALUES(16,16,16); -- 执行成功
