MySQL-锁详解

锁

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

• 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

小栗子

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，
那么如何解决是你买到还是另一个人买到的问题？

这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，
然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类

从对数据操作的类型（读\写）分

• **读锁(共享锁)：**针对同一份数据，多个读操作可以同时进行而不会互相影响。
• **写锁（排它锁）：**只能一个线程写，当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分

• 表锁
• 行锁

表锁(偏读)

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

​

案例分析

读锁

建表

create table mylock(
 id int not null primary key auto_increment,
 name varchar(20)
)engine myisam;       #使用myisam引擎
 
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

手动增加释放表锁

#【手动增加表锁】
lock table 表名字1 read(write)，表名字2 read(write);
#释放锁
unlock tables;

#【查看表上加过的锁】
show open tables;

结论：

当前会话对表A加了表锁(共享读锁)后：

• 只能读A表的数据，不能更新表
• 不能查询或更新其他的表

其他会话：

• 可以查询表A的数据
• 但是对表A进行更新操作以后会进入阻塞状态，等表A解锁后才会继续响应

写锁

#手动加写锁
lock table 表名 write;

结论

当前会话对表A加了写锁(互斥锁，排它锁)以后：

• 可以查询表A数据，也可以更新表A数据
• 不能查询或更新其他表

其他会话：

• 不能查询或更新表A的数据，会阻塞直到解锁

总结

• MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
• MySQL的表级锁有两种模式：
  表共享读锁（Table Read Lock）
  表独占写锁（Table Write Lock）
• 对MyISAM表的加读锁后，不会阻塞其他进程对同一表的读请求，当前线程的写请求会直接报错，其他进程的会阻塞直接解锁
• 对MyISAM表的加写锁后，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

简而言之，就是读锁会让多个线程可以共享读，但是一个线程也不能写。而写锁只允许一个线程读写，其他线程的操作全部

---

行锁

1. 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高
2. InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁

案例分析

建表

create table hanglock(a int(11),b varchar(16))engine=innodb;
 

insert into hanglock values(4,'4000');
insert into hanglock values(5,'5000');

#创建索引
create index hanglock_a on hanglock (a);
create index hanglock_b on hanglock (b);

行锁基本演示

InnoDB默认就是行锁,会话1更新某一行,此时这行是被加了写锁的;会话2仍然可以读取这一行是因为数据并不是从表中读取的,而是从undo log文件中读取的

开启两个session，都关闭自动提交

set autocommit=0

sessionA对修改第一行数据，sessionB也修改第一行数据

此时sessionA修改成功但是并没提交，sessionB想对同一数据进行修改被阻塞

可以说明行锁生效

两会话修改不同行数据

都修改成功，说明不是表锁，不同行数据之间可以独立修改

索引失效，行锁变表锁

sessionA在更新数据时，where条件下的varchar类型的索引字段没加单引号，导致索引失效，行锁升级为表锁，导致两个session更新不同行的数据却阻塞了

原理：就是一旦对表进行全表扫描，innodb的行锁就会自动变为表锁，保护表的记录，等到update结束释放表锁

手动锁定一行

select * from hanglock where a=8 for update;

然后别的线程想要修改相同行的数据时就会被阻塞

分析行锁定

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

对各个状态量的说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是

  Innodb_row_lock_time_avg（等待平均时长）
  Innodb_row_lock_waits（等待总次数）
  Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

最后可以通过来查询正在被锁阻塞的sql语句

SELECT * FROM information_schema.INNODB_TRX\G;

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
2. 尽可能减少范围检索条件，避免间隙锁
3. 尽量控制事务大小，减少锁定资源量和时间长度
4. 锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
5. 涉及相同表的事务，对于调用表的顺序尽量保持一致。
6. 在业务环境允许的情况下,尽可能低级别事务隔离

间隙锁

什么是间隙锁

间隙锁（Gap Lock）是Innodb在可重复读的隔离级别下，解决幻读问题时引入的锁机制

防止幻读

为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

当线程A更新一段条件范围内的值，线程B此时想在这个范围内插入数据会被阻塞，select 不会被阻塞

原因:
因为检索执行过程中通过范围查找，并且默认加了行锁的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

缺点

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

疑问？

为啥select时，可以插入，说明没间隙锁，需要手动加

SELECT * FROM `gap` WHERE `id` = 5 FOR UPDATE;

产生条件：

**①用范围条件而不是相等条件检索数据，②并请求共享或排他锁**时

InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。

innodb自动使用间隙锁的条件：

1. 必须在RR级别下
2. 请求共享或排他锁
3. 检索条件**必须有索引**（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

唯一索引的间隙锁

测试环境：

环境：MySQL，InnoDB，默认的隔离级别（RR）

关闭自动提交

set autocommit=0

数据表：

CREATE TABLE `gap` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据：

INSERT INTO `gap` VALUES ('1', '小罗');
INSERT INTO `gap` VALUES ('5', '小黄');
INSERT INTO `gap` VALUES ('7', '小明');
INSERT INTO `gap` VALUES ('11', '小红');

在进行测试之前，我们先来看看test表中存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 5]
3. (5, 7]
4. (7, 11]
5. (11, +infinity]

定值查询存在数据

只使用行锁，不会产生间隙锁

为什么不产生间隙锁？

因为唯一索引是不重复的，查出来的只有一条记录，不会被别的线程影响产生幻读问题(记录条数变化)

我们现在进行以下几个事务的测试：

/* 查询 id = 5 的数据并加记录锁 */
SELECT * FROM `gap` WHERE `id` = 5 FOR UPDATE;

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务1更新 id=5 的数据 */   
update gap set name='大黄' where id=5;  #被阻塞

/* 事务2插入一条 name = '小张' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (4, '小张'); # 正常执行

/* 事务3插入一条 name = '小张' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (8, '小东'); # 正常执行

由于主键是唯一索引，而且是只使用一个索引查询，并且只锁定一条记录，所以以上的例子，只会对 id = 5 的数据加上记录锁，而不会产生间隙锁。

定值查询不存在数据

产生间隙锁

为什么？幻读

1. 因为事务A第一次查询不存在的记录时，显示不存在，
2. 此时事务B插入了该条记录，
3. 事务A再查询时发现该条数据突然出现了，此时就产生了幻读问题

测试查询不存在的数据时，会怎样：

/* 查询 id = 3 这一条不存在的数据并加记录锁 */
SELECT * FROM `gap` WHERE `id` = 3 FOR UPDATE;

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务6插入一条 id = 1，name = '小张1' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES 12, '小张1'); #报错主键不能重复，不是阻塞

/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (2, '小张1'); # 阻塞

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (4, '小白'); # 阻塞

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (6, '小东'); # 正常执行

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (8, '大罗'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

• **间隙锁范围为:[1,3],(3,5] ** 边界是主键，重复无法插入，所以是闭区间
• 我们可以看出，指定查询某一条记录时，如果这条记录不存在，会产生间隙锁

范围条件查询

产生间隙锁

我们继续在 id 唯一索引列上做以下的测试：

/* 查询 id 在 7 - 11 范围的数据并加记录锁 */
SELECT * FROM `gap` WHERE `id` BETWEEN 7 AND 11 FOR UPDATE;

# 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (3, '小张1'); # 正常执行

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (4, '小白'); # 正常执行

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (6, '小东'); # 阻塞

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (8, '大罗'); # 阻塞

/* 事务6插入一条 id = 9， name = '大东' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (9, '大东'); # 阻塞

/* 事务7插入一条 id = 11， name = '李西' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (11, '李西'); # 阻塞


/* 事务8插入一条 id = 12， name = '张三' 的数据 */
INSERT INTO `gap` (`id`, `name`) VALUES (12, '张三'); #阻塞

从上面我们可以看到

• [5, 7]、(7, 11]、(11,+oo) ，都不可插入数据
• 其它区间可以正常插入数据

当我们给 (7, 11] 这个区间加锁的时候，会锁住 (5, 7]、(7, 11]、(11,+oo) 这三个区间。

结论

1. 对于指定查询某一条记录的加锁语句WHERE id = 5 FOR UPDATE;
   • 如果该记录不存在，会产生记录锁和间隙锁
   • 如果记录存在，则只会产生记录锁
2. 对于查找某一范围内的查询语句，会产生间隙锁，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;

---

普通索引的间隙锁

数据准备

创建 gap1表：

# 注意：number 不是唯一值

CREATE TABLE `gap1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT '数字',
  PRIMARY KEY (`id`),
  KEY `number` (`number`) #USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

在这张表上，我们有 id number 这两个字段，id 是我们的主键，我们在 number 上，建立了一个普通索引，为了方便我们后面的测试。

现在我们要先加一些数据：

INSERT INTO `gap1` VALUES (1, 1);
INSERT INTO `gap1` VALUES (5, 3);
INSERT INTO `gap1` VALUES (7, 8);
INSERT INTO `gap1` VALUES (11, 12);

在进行测试之前，我们先来看看test1表中 number 索引存在的隐藏间隙：

1. (-infinity, 1]
2. (1, 3]
3. (3, 8]
4. (8, 12]
5. (12, +infinity]

定值查询存在的数据

为什么这个会产生间隙锁，唯一索引却没有？

就是因为不唯一，值可以重复，会产生幻读问题

1. 如果事务A查出名字为黄凯宇的有3个
2. 事务B插入了几个名字叫黄凯宇的人
3. 事务A再查就多出了几条数据，产生幻读问题

我们执行以下的事务（事务1最后提交），分别执行下面的语句：

/* 查询 number = 3 的数据并加记录锁 */
SELECT * FROM `gap1` WHERE `number` = 3 FOR UPDATE;


/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `gap1` (`number`) VALUES (0); # 正常执行

/* 事务3插入一条 number = 1 的数据 */
INSERT INTO `gap1` (`number`) VALUES (1); # 被阻塞

/* 事务4插入一条 number = 2 的数据 */
INSERT INTO `gap1` (`number`) VALUES (2); # 被阻塞

/* 事务5插入一条 number = 4 的数据 */
INSERT INTO `gap1` (`number`) VALUES (4); # 被阻塞

/* 事务6插入一条 number = 8 的数据 */
INSERT INTO `gap1` (`number`) VALUES (8); # 正常执行

/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `gap1` (`number`) VALUES (9); # 正常执行

• 插入number时，范围[1,3],(3,8]上锁
• 这里可以看到，number (1 -,8) 的间隙中，插入语句都被阻塞了，而不在这个范围内的语句，正常执行，这就是因为有间隙锁的原因。

定值查询不存在的数据

我们再进行以下的测试，方便我们更好的理解间隙锁的区域（我们要将数据还原成原来的那样）：

/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `gap1` WHERE `number` = 5 FOR UPDATE;



/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `gap1` (`number`) VALUES (0); # 正常执行



/* 事务4插入一条 number = 3 的数据 */
INSERT INTO `gap1` (`number`) VALUES (3); # 被阻塞

INSERT INTO `gap1` (`id`, `number`) VALUES (4, 3); #不会被阻塞，不在区间


/* 事务6插入一条 number = 7 的数据 */
INSERT INTO `gap1` (`number`) VALUES (7); # 被阻塞

/* 事务3插入一条 id = 6， number = 8 的数据 */
INSERT INTO `gap1` (`id`, `number`) VALUES (6, 8); # 阻塞    id<7,number=8  在区间里

/* 事务4插入一条 id = 8， number = 8 的数据 */
INSERT INTO `gap1` (`id`, `number`) VALUES (8, 8); # 正常执行 因为id>7，排在后面，不在区间内，


/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `gap1` (`number`) VALUES (9); # 正常执行


/* 事务7修改 id = 11， number = 12 的数据 */
UPDATE `gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # 阻塞  修改成number=5，相当于插入number=5的数据

奇怪的现象：

事务3添加 id = 6，number = 8 的数据，给阻塞了；

事务4添加 id = 8，number = 8 的数据，正常执行了。

INSERT INTO gap1 (number) VALUES (3); # 被阻塞

INSERT INTO gap1 (id, number) VALUES (4, 3); #不会被阻塞，不在区间

事务7将 id = 11，number = 12 的数据修改为 id = 11， number = 5的操作，给阻塞了；

**这是为什么呢？**我们来看看下边的图，大家就明白了。

当 number 相同时，会根据主键 id 来排序

隐藏的间隙锁图

从图中可以看出，

1. 事务3添加的 id = 6，number = 8，这条数据是在 （3, 8） 的区间里边，所以会被阻塞；
2. 事务4添加的 id = 8，number = 8，这条数据则是在（8, 12）区间里边，所以不会被阻塞；
3. 事务7的修改语句相当于在 （3, 8） 的区间里边插入一条数据，所以也被阻塞了。

范围条件查询

和唯一索引类似

结论

1. 在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
2. 普通索引和唯一索引中，数据间隙的分析，数据行是**优先根据普通索引排序，再根据唯一索引排序**。

临键锁(Next-key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

本文要点

1. 记录锁、间隙锁、临键锁，都属于排它锁；
2. 记录锁就是锁住一行记录；
3. 间隙锁只有在事务隔离级别 RR可重复读 中才会产生；
4. 唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
5. 普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
6. 间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现 幻读 现象；
7. 普通索引的间隙，优先以普通索引排序，然后再根据主键索引排序（多普通索引情况还未研究）；
8. 事务级别是RC（读已提交）级别的话，间隙锁将会失效。