传送门:MySQL锁:01.总览
传送门:MySQL锁:02.InnoDB锁
传送门:MySQL锁:03.InnoDB行锁
通过索引实现行锁,在索引记录上加锁。
没有索引就无法实现行锁,升级成全表记录锁,等同于表锁。
理解InnoDB独特的行锁运行机制,认识特有的四种行锁粒度——lock_ordinary、lock_gap、lock_rec_not_gap、lock_insert_intention
InnoDB 行锁
- 默认都是加lock_ordinary锁
- 如果是唯一索引列上的等值查询,则退化成lock_rec_not_gap
- 所有版本,非唯一索引列上的范围查询,遇到第一个不符合条件的记录也会加上lock_ordinary。
- 8.0.18版本以前,主要指
锁排查可以用的视图和数据字典
mysql> show engine innodb status \G
mysql> select * from performance_schema.data_lock_waits;
mysql> select * from performance_schema.data_locks;
mysql> select * from performance_schema.metadata_locks;
InnoDB 行锁兼容性
请求的锁类型 请求的锁类型 请求的锁类型 请求的锁类型 lock_ordinary lock_rec_not_gap lock_gap lock_insert_intention 已获得的锁类型 lock_ordinary X X O X 已获得的锁类型 lock_rec_not_gap X X O O 已获得的锁类型 lock_gap O O O X 已获得的锁类型 lock_insert_intention O O O O
- gap只和insert intention锁冲突
- insert intention和任何锁都不冲突,除非也在相同位置做意向插入锁
- 先获得意向插入锁的,再尝试上gap lock是可以的
- 但是反过来 ,先获得gap lock的,再尝试加上意向插入锁便会阻塞,
- 原因是:先获得意向插入锁时,实际上插入已经成功,意向插入锁会被转变为对具体记录的ordinary 或 rec_not_gap ,此时二者都与lock gap兼容。
InnoDB行锁之共享锁
共享锁:
- 不允许其他事务修改被锁定的行,只能读
- select .. for share/ lock in share mode
- 自动提交模式下的普通select是一致性非锁定读,不加锁。
自动提交模式下, 不使用begin开启事务,直接select的话:
select * from xxx where .. 不加锁
select * from xxx where .. for share ,也查询不到加锁, 但是实际上是加锁的,只不过锁的时间及其的短暂。
验证:
此时,用排他锁来验证自动提交模式的for share究竟是否产生锁动作。
可以看出,自动提交模式下select(不加for share)是一致性非锁定读,但是加for share后,是会有锁定动作的,只不过没有阻塞的情况下,锁的持续时间是非常短暂的。
查看InnoDB锁
- 开启参数:innodb_status_output_locks=1; 以支持使用 show engine innodb status 查看锁详情。
mysql> begin ; select * from k1 where id=4 for update;
Query OK, 0 rows affected (0.00 sec)
mysql> show engine innodb status \G
..
MySQL thread id 31, OS thread handle 139620328457984, query id 1297 localhost root
**TABLE LOCK table kk
.k1
trx id 1901 lock mode IX**
RECORD LOCKS space id 3 page no 10 n bits 1056 index id of table kk
.k1
trx id 1901 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 000000000902; asc ;;
RECORD LOCKS space id 3 page no 9 n bits 320 index GEN_CLUST_INDEX of table kk
.k1
trx id 1901 lock_mode X locks rec but not gap
Record lock, heap no 248 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 6; hex 000000000902; asc ;;
1: len 6; hex 000000000663; asc c;;
2: len 7; hex 82000000940110; asc ;;
3: len 4; hex 80000004; asc ;;
4: SQL NULL;
5: SQL NULL;
RECORD LOCKS space id 3 page no 10 n bits 1056 index id of table kk
.k1
trx id 1901 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000016; asc ;;
1: len 6; hex 000000000200; asc ;;
..
- space id 3 表的表空间ID
- page no 10 锁所在datapage的ID
- heap no 4,slot no,记录在page物理上的第几个位置。
- 也可以查询P_S表
mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139620969519720:1072:139620864029240 | 2211 | 85 | 79 | kk | k1 | NULL | NULL | NULL | 139620864029240 | TABLE | IX | GRANTED | NULL |
| INNODB | 139620969519720:15:4:5:139620864026200 | 2211 | 85 | 79 | kk | k1 | NULL | NULL | PRIMARY | 139620864026200 | RECORD | X,REC_NOT_GAP | GRANTED | 4 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
- 再看下IS锁的情况
mysql> begin ; select * from k1 where id=4 for share;
Query OK, 0 rows affected (0.00 sec)
mysql> show engine innodb status \G
看不到IS锁信息。
1 row in set (0.00 sec)
mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139620969519720:1072:139620864029240 | 421095946230376 | 85 | 83 | kk | k1 | NULL | NULL | NULL | 139620864029240 | TABLE | IS | GRANTED | NULL |
| INNODB | 139620969519720:15:4:5:139620864026200 | 421095946230376 | 85 | 83 | kk | k1 | NULL | NULL | PRIMARY | 139620864026200 | RECORD | S,REC_NOT_GAP | GRANTED | 4 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
验证一下IS和IX的兼容
t1:
mysql> begin ; select * from k1 where id=4 for share;
Query OK, 0 rows affected (0.00 sec)
+------+------+------+
| id | dtl | name |
+------+------+------+
| 4 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)
t2:
mysql> begin ; select * from k1 where id=11 for update;
Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)
--注意:加锁加不在相同行,否则hang。
t3:
mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
| INNODB | 139620969519720:1072:139620864029240 | 2212 | 85 | 89 | kk | k1 | NULL | NULL | NULL | 139620864029240 | TABLE | IX | GRANTED | NULL |
| INNODB | 139620969519720:15:4:1:139620864026200 | 2212 | 85 | 89 | kk | k1 | NULL | NULL | PRIMARY | 139620864026200 | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 139620969521464:1072:139620864041176 | 421095946232120 | 84 | 110 | kk | k1 | NULL | NULL | NULL | 139620864041176 | TABLE | IS | GRANTED | NULL |
| INNODB | 139620969521464:15:4:5:139620864038296 | 421095946232120 | 84 | 110 | kk | k1 | NULL | NULL | PRIMARY | 139620864038296 | RECORD | S,REC_NOT_GAP | GRANTED | 4 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
4 rows in set (0.00 sec)
InnoDB行锁实现机制
- 基于索引实现,逐行检查,逐行加锁
- 没有索引的列上需要加锁时,会先对所有记录加锁,再根据实际情况决定是否释放锁。
- 辅助索引上加锁时,同时要回溯到主键索引上再加一次锁。
- 加锁的基本单位默认时lock_ordinary,当索引就具有唯一性的时候退化为lock_rec_not_gap
- 等值条件逐行加锁时,会向右遍历到第一个不满足条件的记录,然后lock_ordinary退化为lock_gap
- 如果发生唯一性检测(insert\update动作),那么会发生lock_ordinary , 再退化成lock_rec_not_gap
- 唯一索引的范围条件加锁时,也会对第一个不满足条件的记录加锁
对普通索引上锁
普通索引next-key lock + 主键 not gap + 普通索引的下一个记录的gap lock(见示意图)。
`
mysql> select * from k2;
+—-+——+——+
| id | dtl | un |
+—-+——+——+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 5 | 5 | (回溯到pk上锁)
Original: https://www.cnblogs.com/konggg/p/14134114.html
Author: 孔个个
Title: MySQL锁:03.InnoDB行锁
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/621096/
转载文章受原作者版权保护。转载请注明原作者出处!