# 行级锁
# 1. 行锁的概念
1) 行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
2) 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
3) 临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。 在RR隔离级别下支持。
跟表锁一样,行锁也分为以下两种类型
共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁,但是允许其他事务获得相同数据集的共享锁
排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。
1
2
3
4
5
6
7
2
3
4
5
6
7
常见的SQL语句,在执行时,所加的行锁如下:
| SQL | 行锁类型 | 说明 |
|---|---|---|
| INSERT … | 排他锁 | 自动加锁 |
| UPDATE … | 排他锁 | 自动加锁 |
| DELETE … | 排他锁 | 自动加锁 |
| SELECT(正常) | 不加任何锁 | |
| SELECT … LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT之后加LOCK IN SHARE MODE |
| SELECT … FOR UPDATE | 排他锁 | 需要手动在SELECT之后加FOR UPDATE |
SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE ,这两种sql其实就和普通的select语句功能是一样的,
引进这两种形式的原因是因为普通的select语句是不加锁的,但是我们在有些业务场景需要针对select语句加锁,
因此就可以通过SELECT … LOCK IN SHARE MODE为普通的select语句加上共享锁,或者通过SELECT … FOR UPDATE 为普通的sql语句加上排他锁。
1
2
3
2
3
# 2. 行级锁的实现方式
1) 在不通过索引条件查询的时候,InnoDB使用的是表锁,而不是行锁。
2) 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以即使是访问不同行的记录,如果使用了相同的索引键,也是会出现锁冲突的。
3) 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
4) 即便在条件中使用了索引字段,但具体是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。
隐式加锁:
A. InnoDB自动加意向锁。
B. 对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
C. 对于普通SELECT语句,InnoDB不会加任何锁;
显示加锁:
A. 共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
B. 排他锁(X) :SELECT * FROM table_name WHERE ... FOR UPDATE
用SELECT … IN SHARE MODE获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE或者DELETE操作。
但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用SELECT… FOR UPDATE方式获得排他锁。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 3. 行级锁使用case
- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 添加共享锁
mysql> select * from course where id = 1 lock in share mode;
+----+------+
| id | name |
+----+------+
| 1 | Java |
+----+------+
1 row in set (0.00 sec)
-- 添加排他锁
mysql> update course set name='C++' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
- session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from course where id = 1 lock in share mode;
+----+------+
| id | name |
+----+------+
| 1 | Java |
+----+------+
1 row in set (0.00 sec)
-- 可以看到多个共享锁并不互斥
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| my_test | course | NULL | TABLE | IS | NULL |
| my_test | course | PRIMARY | RECORD | S,REC_NOT_GAP | 1 |
| my_test | course | NULL | TABLE | IS | NULL |
| my_test | course | PRIMARY | RECORD | S,REC_NOT_GAP | 1 |
+---------------+-------------+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)
-- 获取排他锁失败,session1 释放排他锁之后才可以正常update
mysql> update course set name = "C#" where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
-- 查看当前锁
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| my_test | course | NULL | TABLE | IX | NULL |
| my_test | course | PRIMARY | RECORD | X,REC_NOT_GAP | 1 | /* 行排他锁*/
| my_test | course | NULL | TABLE | IX | NULL |
| my_test | course | PRIMARY | RECORD | X,REC_NOT_GAP | 1 |
+---------------+-------------+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)
-- 如果不根据索引去查找行,行锁会升级为表锁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 3.1 间隙锁 & 临间锁(记录和间隙/行级锁和间隙锁的组合)
1) 在默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用的是临键锁进行搜索和索引扫描而并非使用行锁,这样做的目的是防止幻读,但是在特定情况下,临键锁会变化成行锁或间隙锁:
2) 当我们针对唯一索引对已存在的记录进行等值匹配的时候,临键锁会自动优化成行锁
3) 索引上的等值查询(唯一索引),给不存在的记录加锁时, 优化为间隙锁
4) 索引上的范围查询(唯一索引),会访问到不满足条件的第一个值为止。
1
2
3
4
2
3
4
- session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from course;
+----+--------+
| id | name |
+----+--------+
| 1 | C++ |
| 2 | PHP |
| 3 | MySQL |
| 4 | Golang |
| 15 | Python |
+----+--------+
5 rows in set (0.00 sec)
-- 不存在的索引加上排他锁就会升级为间隙锁
mysql> update course set name = 'Pgsql' where id = 10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
- session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| my_test | course | NULL | TABLE | IX | NULL |
| my_test | course | PRIMARY | RECORD | X,GAP | 15 | /* 间隙锁,锁住了4-14这个间隙*/
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13