MySQL行锁定测试

##准备测试数据
create table locktest(id int primary key,col1 int,index(col1));
insert into locktest values(1,10);
insert into locktest values(2,20);
insert into locktest values(3,30);
insert into locktest values(4,40);
insert into locktest values(5,50);

非唯一索引

范围查询，临键锁（一）

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest where col1 > 30 and col1 < 50 for update;
+----+------+
| id | col1 |
+----+------+
|  4 |   40 |
+----+------+
1 row in set (0.00 sec)
#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  4 |   40 |
|  5 |   50 |
+----+------+
4 rows in set (0.01 sec)

##col1为20时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(16,20);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为30时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(16,30);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为49时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(16,49);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为50时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(16,50);
Query OK, 1 row affected (0.00 sec)

##col1为51时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(18,51);
Query OK, 1 row affected (0.00 sec)

##col1为19时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(17,19);
Query OK, 1 row affected (0.00 sec)

##col1为20时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=20 where col1=20;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为40时更新，发生阻塞，更新失败
mysql> update locktest set col1=40 where col1=40;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为50时更新，发生阻塞，更新失败
mysql> update locktest set col1=50 where col1=50;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

结论

列	值
数据（col1）	10、20、40、50
查询条件	> 30 and < 50
锁类型	临键锁
加锁区间	(20，40]、(40，50]
记录锁	40，50

范围查询，临键锁（二）

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from locktest where col1 > 30 and col1 < 50;
+----+------+
| id | col1 |
+----+------+
|  4 |   40 |
+----+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#事务B
mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
+----+------+
5 rows in set (0.00 sec)

##col1为29时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(6,29);
Query OK, 1 row affected (0.00 sec)

##col1为30时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(7,30);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为49时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(7,49);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为50时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(7,50);
Query OK, 1 row affected (0.00 sec)

##col1为51时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(8,51);
Query OK, 1 row affected (0.00 sec)

##col1为30时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=30 where col1 = 30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为40时更新，发生阻塞，更新失败
mysql> update locktest set col1=40 where col1 = 40;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为50时更新，发生阻塞，更新失败
mysql> update locktest set col1=50 where col1 = 50;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

结论

列	值
数据（col1）	10、20、30、40、50
查询条件	> 30 and < 50
锁类型	临键锁
加锁区间	(30，40]、(40，50]
记录锁	40，50

范围查询，临键锁（三）

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>  select * from locktest where col1 < 30 for update;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
+----+------+
2 rows in set (0.00 sec)

#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select  * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
+----+------+
5 rows in set (0.00 sec)

##col1为30时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(9,30);
Query OK, 1 row affected (0.00 sec)

##col1为31时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(6,31);
Query OK, 1 row affected (0.00 sec)

##col1为40时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(7,40);
Query OK, 1 row affected (0.00 sec)

##col1为29时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(8,29);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为5时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(10,5);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为1时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(10,1);
c^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为30时更新，发生阻塞，更新失败
mysql> update locktest set col1=30 where col1=30;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为20时更新，发生阻塞，更新失败
mysql> update locktest set col1=20 where col1=20;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为40时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=40 where col1=40;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

结论

列	值
数据（col1）	10、20、30、40、50
查询条件	< 30
锁类型	临键锁
加锁区间	(-∞，10]、(10，20]、(20，30]
记录锁	10，20，30

范围查询，临键锁（四）

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql>  select * from locktest where col1 > 40 for update;
+----+------+
| id | col1 |
+----+------+
|  5 |   50 |
+----+------+
1 row in set (0.00 sec)
#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
+----+------+
5 rows in set (0.00 sec)

##col1为35时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(6,35);
Query OK, 1 row affected (0.01 sec)

##col1为39时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(7,39);
Query OK, 1 row affected (0.00 sec)

##col1为30时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=30 where col1=30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为40时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(8,40);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为41时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(8,41);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为51时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(8,51);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为40时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=40 where col1=40;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为50时更新，发生阻塞，更新失败
mysql> update locktest set col1=50 where col1=50;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

结论

列	值
数据（col1）	10、20、30、40、50
查询条件	> 40
锁类型	临键锁
加锁区间	(40，50]，(50，∞]
记录锁	50

范围查询，间歇锁

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from locktest where col1 > 50 for update;
Empty set (0.00 sec)
#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
+----+------+
5 rows in set (0.01 sec)

##col1为40时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=40 where col1=40;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为50时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=50 where col1=50;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为45时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(6,45);
Query OK, 1 row affected (0.00 sec)

##col1为51时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(7,51);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为60时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(7,60);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

结论

列	值	扩展
数据（col1）	10、20、30、40、50	10、20、30、40、50
查询条件	> 50	>=50
锁类型	GAP锁	临键锁
加锁区间	(50，∞)	(50，∞]
记录锁	无	50
备注	唯一索引/主键索引在这个场景下与非唯一索引的结论一致

范围查询，临键锁（五）

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest where col1 < 10 for update;
Empty set (0.00 sec)
#事务B
mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
+----+------+
5 rows in set (0.00 sec)

##col1为11时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(6,11);
Query OK, 1 row affected (0.00 sec)

##col1为25时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(7,25);
Query OK, 1 row affected (0.00 sec)

##col1为9时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(8,9);
c^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为1时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(8,1);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为10时更新，发生阻塞，更新失败
mysql> update locktest set col1=10 where col1=10;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为20时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=20 where col1=20;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

结论

列	值
数据（col1）	10、20、30、40、50
查询条件	< 10
锁类型	临键锁
加锁区间	(-∞，10]
记录锁	10
备注	唯一索引/主键索引在这个场景下与非唯一索引的结论一致

等值查询，临键锁

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest where col1 = 30 for update;
+----+------+
| id | col1 |
+----+------+
|  3 |   30 |
+----+------+
1 row in set (0.00 sec)
#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
+----+------+
5 rows in set (0.00 sec)

##col1为30时更新，发生阻塞，更新失败
mysql> update locktest set col1=30 where col1=30;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为20时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=20 where col1=20;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为40时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=40 where col1=40;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为29时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(6,29);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为21时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(9,21);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为19时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(8,19);
Query OK, 1 row affected (0.00 sec)

##col1为31时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(10,31);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为39时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(10,39);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为41时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(10,41);
Query OK, 1 row affected (0.01 sec)

结论

列	值
数据（col1）	10、20、30、40、50
查询条件	= 30
锁类型	临键锁
加锁区间	(20，30]、 (30，40)注意此处是小括号，40不加锁，属于GAP锁
记录锁	30

等值查询，间歇锁

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest where col1 = 35 for update;
Empty set (0.00 sec)
#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
+----+------+
5 rows in set (0.00 sec)

##col1为31时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(6,31);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为29时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(6,29);
Query OK, 1 row affected (0.00 sec)

##col1为39时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(7,39);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为41时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(7,41);
Query OK, 1 row affected (0.00 sec)

##col1为30时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=30 where col1=30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为40时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=40 where col1=40;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

结论

列	值
数据（col1）	10、20、30、40、50
查询条件	= 35
锁类型	Gap锁
加锁区间	（30，40）
记录锁	无

范围查询，临键锁（六）

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from locktest where col1 > 31 and col1 < 36 for update;
Empty set (0.00 sec)

#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
+----+------+
5 rows in set (0.00 sec)

##col1为31时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(6,31);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为35时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(6,35);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为39时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(6,39);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为41时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(6,41);
Query OK, 1 row affected (0.00 sec)

##col1为30时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=30 where col1=30;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

##col1为40时更新，发生阻塞，更新失败
mysql> update locktest set col1=40 where col1=40;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##col1为50时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=50 where col1=50;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

结论

列	值
数据（col1）	10、20、30、40、50
查询条件	> 31 and < 36
锁类型	临键锁
加锁区间	（30，40]
记录锁	40

#多增加一些数据，方便唯一索引的测试
insert into locktest values(10,60);
insert into locktest values(15,70);
insert into locktest values(20,80);
insert into locktest values(25,90);
insert into locktest values(30,100);

唯一索引/主键索引

等值查询，记录锁

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest where id = 10 for update;
+----+------+
| id | col1 |
+----+------+
| 10 |   60 |
+----+------+
1 row in set (0.00 sec)

#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
| 10 |   60 |
| 15 |   70 |
| 20 |   80 |
| 25 |   90 |
| 30 |  100 |
+----+------+
10 rows in set (0.00 sec)

##id为10时更新，发生阻塞，更新失败
mysql> update locktest set col1=10 where id = 10;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##id为5时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=10 where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##id为15时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=10 where id = 15;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##id为9时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(9,10);
Query OK, 1 row affected (0.00 sec)

##id为13时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(13,10);
Query OK, 1 row affected (0.00 sec)

结论

列	值
数据（id）	1、2、3、4、5、10、15、20、25、30
查询条件	= 10
锁类型	记录锁
加锁区间	无
记录锁	10

等值查询，间歇锁

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest where id = 7 for update;
Empty set (0.00 sec)
#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
| 10 |   60 |
| 15 |   70 |
| 20 |   80 |
| 25 |   90 |
| 30 |  100 |
+----+------+
10 rows in set (0.00 sec)

##id为5时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=10 where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##id为10时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=10 where id = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##id为6时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(6,10);
C^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##id为11时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(11,10);
Query OK, 1 row affected (0.00 sec)

结论

列	值
数据（id）	1、2、3、4、5、10、15、20、25、30
查询条件	= 7
锁类型	Gap锁
加锁区间	（5，10）
记录锁	10
备注	条件是唯一索引/主键索引，做等值查询，数据不存在时，变为Gap锁

范围查询，临键锁

举例

#事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>  select * from locktest where id > 6 and id < 18 for update;
+----+------+
| id | col1 |
+----+------+
| 10 |   60 |
| 15 |   70 |
+----+------+
2 rows in set (0.00 sec)

#事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locktest;
+----+------+
| id | col1 |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  3 |   30 |
|  4 |   40 |
|  5 |   50 |
| 10 |   60 |
| 15 |   70 |
| 20 |   80 |
| 25 |   90 |
| 30 |  100 |
+----+------+
10 rows in set (0.00 sec)

##id为5时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=10 where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##id为10时更新，发生阻塞，更新失败
mysql> update locktest set col1=10 where id = 10;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##id为15时更新，发生阻塞，更新失败
mysql> update locktest set col1=10 where id = 15;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##id为20时更新，发生阻塞，更新失败
mysql> update locktest set col1=10 where id = 20;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##id为25时更新，没有发生阻塞，更新成功
mysql> update locktest set col1=10 where id = 25;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

##id为6时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(6,10);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##id为19时插入，发生阻塞，插入失败
mysql> insert into locktest(id,col1) values(19,10);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

##id为21时插入，没有发生阻塞，插入成功
mysql> insert into locktest(id,col1) values(21,10);
Query OK, 1 row affected (0.00 sec)

结论

列	值	扩展
数据（id）	1、2、3、4、5、10、15、20、25、30	1、2、3、4、5、10、15、20、25、30
查询条件	> 6 and < 18	> 16 and < 19
锁类型	临键锁	临键锁
加锁区间	(5，10]，(10，15]、(15，20]	(15，20]
记录锁	10、15、20	20
备注	条件是唯一索引/主键索引，做范围查询时，变为临键锁（查询结果有数据或者没有数据，都会变为临键锁）