MySQL 行级锁演示
MySQL 锁 About 7,111 words准备数据
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
Views: 1,702 · Posted: 2022-05-09
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...