聊聊MySQL里的 record锁和 gap 锁-20201020
聊聊MySQL里的 record锁和 gap 锁
注意：隔离级别为 可重复读！！！
mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
#创建测试表
mysql> create table t2(id int,name varchar(10),primary key(id),key(name));
Query OK, 0 rows affected (0.46 sec)
#插入测试数据
mysql> insert into t2 values(1,'A'),(3,'A'),(5,'C'),(7,'G'),(10,'I');
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
#确认目前的数据
mysql> select id,name from t2;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 3 | A |
| 5 | C |
| 7 | G |
| 10 | I |
+----+------+
5 rows in set (0.00 sec)
以下为record 锁 的操作步骤
case1
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.00 sec)
session2
mysql> use sakila;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where id=5 lock in share mode;
被hang住，哈哈
#目前数据库中的锁的情况
case2
session1
mysql> select * from t2 where id=5 and name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.00 sec)
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where id=5 and name='B' for update;
哈哈 被锁
#目前数据库中的锁的情况
结论：MySQL 二级索引上加的锁 会自动的在主键上也附加一个锁
以下为gap锁的测试步骤
case1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.00 sec)
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(6,'C');
哈哈 被锁
#确认数据库中锁的情况，请注意最后 被锁的记录数和被锁的主键
session3
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(4,'B');
哈哈 照旧被锁
#确认下数据库中的锁
case2
3
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.00 sec)
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(6,'E');
哈哈 照旧被锁
#确认数据库中的锁的情况
case3
session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2 where name='C' for update;
+----+------+
| id | name |
+----+------+
| 5 | C |
+----+------+
1 row in set (0.00 sec)
session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t2 values(2,'B');
哈哈 照旧被锁
mysql> insert into t2 values(8,'F');
哈哈 照旧被锁
mysql> insert into t2 values(6,'G');
哈哈 照旧被锁
文末附上 那条牛逼的sql语句
SELECT
t1.lock_trx_id '事务id',
t2.trx_started '事务的开始时间',
CASE t2.trx_state
WHEN 'LOCK WAIT' THEN '请求锁'
WHEN 'RUNNING' THEN '持有锁'
ELSE 'null'
END '事务状态' ,
CASE lock_mode
WHEN 'X' THEN '排它锁'
WHEN 'X,GAP' THEN '排它锁,空隙锁'
WHEN 'S' THEN '共享锁'
ELSE 'null'
END '锁模式',
t1.lock_type '锁类型',
t1.lock_table '被锁的表',
t1.lock_index '锁的index',
t1.lock_space '表空间的id',
t1.lock_page '被锁的页号',
t1.lock_rec '被锁的记录数',
t1.lock_data '被锁行的主键'
FROM information_schema.innodb_locks t1
LEFT JOIN
information_schema.innodb_trx t2
ON t1.lock_trx_id=t2.trx_id;
创建时间：2021-12-22 10:41
넶浏览量：0
