Java OpenResty Spring Spring Boot MySQL Redis MongoDB PostgreSQL Linux Android Nginx 面试 小程序 Arthas JVM AQS juc Kubernetes Docker DevOps


MySQL 行级锁演示

MySQL 大约 7111 字

准备数据

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
阅读 301 · 发布于 2022-05-09

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批