MySQL 行级锁演示

准备数据

create table tb_user(id int, name varchar(25), data int, primary key(id));

insert into tb_user values(1, 'tom', 10), (3, 'jerry', 30), (8, 'kitty', 80), (11, 'rose', 110), (19, 'luci', 190), (25, 'gaga', 250);

查看数据

mysql> select * from tb_user;
+----+-------+------+
| id | name  | data |
+----+-------+------+
|  1 | tom   |   10 |
|  3 | jerry |   30 |
|  8 | kitty |   80 |
| 11 | rose  |  110 |
| 19 | luci  |  190 |
| 25 | gaga  |  250 |
+----+-------+------+

查看索引

mysql> show index from tb_user;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY  |            1 | id          | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

行锁

S,REC_NOT_GAP：表示行锁。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_user where id = 1 lock in share mode;
+----+------+------+
| id | name | data |
+----+------+------+
|  1 | tom  |   10 |
+----+------+------+
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 |
+---------------+-------------+------------+-----------+---------------+-----------+
| testdb        | tb_user     | NULL       | TABLE     | IS            | NULL      |
| testdb        | tb_user     | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

间隙锁/临键锁

GAP：表示间隙锁

S：表示临键锁（lock_mode只有一个S时）

唯一索引-等值查询

更新一个不存在的id。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_user set name = 'nihao' where id = 5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

加锁情况：可以看到是GAP间隙锁，锁的范围是3-8之间的记录。

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 |
+---------------+-------------+------------+-----------+-----------+-----------+
| testdb        | tb_user     | NULL       | TABLE     | IX        | NULL      |
| testdb        | tb_user     | PRIMARY    | RECORD    | X,GAP     | 8         |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

尝试插入id=7的数据，阻塞等待，手动取消了。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_user values(7, 'Ruby', 70);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

普通索引-等值查询

创建普通索引：

mysql>  create index idx_user_data on tb_user(data);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

共享锁：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_user where data=30 lock in share mode;
+----+-------+------+
| id | name  | data |
+----+-------+------+
|  3 | jerry |   30 |
+----+-------+------+
1 row in set (0.00 sec)

加锁情况：

1-3之间加了Next-Key Lock（包含了3的记录，即3的行锁和1-3的间隙锁），不允许更新3，不允许插入2。

3-8之间加了间隙锁，不允许插入7。

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 |
+---------------+-------------+---------------+-----------+---------------+-----------+
| testdb        | tb_user     | NULL          | TABLE     | IS            | NULL      |
| testdb        | tb_user     | idx_user_data | RECORD    | S             | 30, 3     |
| testdb        | tb_user     | PRIMARY       | RECORD    | S,REC_NOT_GAP | 3         |
| testdb        | tb_user     | idx_user_data | RECORD    | S,GAP         | 80, 8     |
+---------------+-------------+---------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)

演示

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_user set name='nihaoa' where id = 3;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> insert into tb_user values(2, 'Alice', 20);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> insert into tb_user values(7, 'Ruby', 70);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

唯一索引-范围查询

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb_user where id>=19 lock in share mode;
+----+------+------+
| id | name | data |
+----+------+------+
| 19 | luci |  190 |
| 25 | gaga |  250 |
+----+------+------+
2 rows in set (0.00 sec)

加锁情况：19加行锁，19-25加临键锁，25到正无穷加临键锁。

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              |
+---------------+-------------+------------+-----------+---------------+------------------------+
| testdb        | tb_user     | NULL       | TABLE     | IS            | NULL                   |
| testdb        | tb_user     | PRIMARY    | RECORD    | S,REC_NOT_GAP | 19                     |
| testdb        | tb_user     | PRIMARY    | RECORD    | S             | supremum pseudo-record |
| testdb        | tb_user     | PRIMARY    | RECORD    | S             | 25                     |
+---------------+-------------+------------+-----------+---------------+------------------------+
4 rows in set (0.00 sec)

演示：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_user set name='nihaoa' where id = 19;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> insert into tb_user values(23, 'Jone', 230);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> update tb_user set name='nihaoa' where id = 25;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> insert into tb_user values(30, 'Haha', 300);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted