请选择 进入手机版 | 继续访问电脑版
查看: 58|回复: 0

[资料分享] 基于innodb锁三种算法解析

[复制链接]

签到天数: 193 天

[LV.7]化身百千

发表于 2019-3-13 20:27:31 | 显示全部楼层 |阅读模式
innodb支持行级锁,但是它还支持范围锁。即对范围内的行记录加行锁。

有三种锁算法:

1.record lock:即行锁
2.gap lock:范围锁,但是不锁定行记录本身
3.next-key lock:范围锁加行锁,即范围锁并锁定记录本身,gap lock + record lock。
record lock是行锁,但是它的行锁锁定的是key,即基于唯一性索引键列来锁定(SQL Server还有基于堆表的rid类型行锁)。如果没有唯一性索引键列,则会自动在隐式列上创建索引并完成锁定。

next-key lock是行锁和范围锁的结合,innodb对行的锁申请默认都是这种算法。如果有索引,则只锁定指定范围内的索引键值,如果没有索引,则自动创建索引并对整个表进行范围锁定。之所以锁定了表还称为范围锁定,是因为它实际上锁的不是表,而是把所有可能的区间都锁定了,从主键值的负无穷到正无穷的所有区间都锁定,等价于锁定了表。

以下示例过程将演示范围锁的情况。

1.有索引的情况

首先创建一个有索引的表t。然后插入几个被分隔的记录。

create table t(id int);
create unique index idx_t on t(id);
insert into t values(1),(2),(3),(4),(7),(8),(12),(15);
在会话1执行:无需知道lock in share mode是什么意思,只需知道它的作用是在读取的时候加上共享锁并且不释放,具体内容在事务章节中会说明。

begin;
select * from t where id<5 lock in share mode;
在会话2执行:

insert into t values(9);
insert into t values(6);
这时发现第一条插入语句是正常插入的,而第二条语句被阻塞。 show engine innodb status 看结果。


  1. mysql> show engine innodb status;
  2. ------------
  3. TRANSACTIONS
  4. ------------
  5. Trx id counter 14992
  6. Purge done for trx's n:o < 14987 undo n:o < 0 state: running but idle
  7. History list length 914
  8. LIST OF TRANSACTIONS FOR EACH SESSION:
  9. ---TRANSACTION 0, not started
  10. MySQL thread id 50, OS thread handle 0x7f5d57e0a700, query id 1495 localhost root init
  11. show engine innodb status
  12. ---TRANSACTION 14991, ACTIVE 17 sec inserting
  13. mysql tables in use 1, locked 1
  14. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  15. MySQL thread id 49, OS thread handle 0x7f5d57d88700, query id 1491 localhost root update
  16. insert into t values(6)
  17. ------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
  18. RECORD LOCKS space id 187 page no 4 n bits 80 index `idx_t` of table `test`.`t` trx id 14991 lock_mode X locks gap before rec insert intention waiting
  19. Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  20. 0: len 4; hex 80000007; asc     ;;
  21. 1: len 6; hex 00000000060c; asc       ;;

  22. ------------------
  23. ---TRANSACTION 14989, ACTIVE 32 sec
  24. 2 lock struct(s), heap size 360, 5 row lock(s)
  25. MySQL thread id 43, OS thread handle 0x7f5d57f0e700, query id 1489 localhost root
  26. 其中"locks gap"就表示阻塞insert语句的锁是gap锁,即范围锁。锁定的范围包括(-∞,4],(4,7](锁到操作行的下一个key,此处插入id=6,由于存在id=7的key,所以锁到7为止,这就是next-key的意思)。当测试插入或修改-1,0,5,6等小于7的值都会被阻塞,而插入或修改大于7的值就不会被阻塞。

  27. 如何判断锁定的范围大小?可以通过下面的查询语句:

  28. mysql> select * from information_schema.INNODB_LOCKS\G
  29. *************************** 1. row ***************************
  30.     lock_id: 2856:109:4:6
  31. lock_trx_id: 2856
  32.   lock_mode: X,GAP
  33.   lock_type: RECORD
  34. lock_table: `test`.`t`
  35. lock_index: idx_t
  36. lock_space: 109
  37.   lock_page: 4
  38.    lock_rec: 6
  39.   lock_data: 7
  40. *************************** 2. row ***************************
  41.     lock_id: 421383739058184:109:4:6
  42. lock_trx_id: 421383739058184
  43.   lock_mode: S
  44.   lock_type: RECORD
  45. lock_table: `test`.`t`
  46. lock_index: idx_t
  47. lock_space: 109
  48.   lock_page: 4
  49.    lock_rec: 6
  50.   lock_data: 7
  51. 2 rows in set (0.000 sec)
复制代码

lock_mode为"X+GAP",表示next-key lock算法。其中lock_data值为7,表示锁定了值为7的记录,这是最大锁定范围边界。lock_rec的值为6,表示锁定了6行记录,其中1,2,3,4,7共5行记录是通过gap锁锁定的范围,加上待插入的id=6(该行为key锁锁定),共锁定6行记录。

而如果使用的是大于号,由于操作任何一条记录,它的下一个key都会被锁定,这等价于锁定了整个无穷区间,即实现了表锁的功能。如下:

在会话1上执行:

# 首先回滚
rollback;
begin;
select * from t where id>10 lock in share mode;
在会话2执行:

insert into t values(0);
insert into t values(5);
insert into t values(100);
会发现任何插入都是阻塞的。即锁定的范围为(-∞,+∞),等价于锁定了整张表。

但是如果使用的等于号,那么在查找索引的时候发现只需锁定一条记录和下一条记录中间的范围即可。

在会话1执行:

# 首先回滚
rollback;
begin;
select * from t where id=5 lock in share mode;
在会话2执行:

insert into t values(0);
insert into t values(10);
会发现上述插入都是允许的。

但如果插入id=6的记录,则阻塞,因为锁定的范围为[5,7]区间。

也就是说,在有索引的情况下,如果是非具体的行锁,那么就会将能扫描到的索引键值内的所有范围加锁。

下面测试没有索引的情况。

2.无索引的情况

首先创建没有索引的表,然后插入一些分隔的记录。

create table ttt(id  int);
insert into ttt values(1),(2),(3),(4),(7),(8),(12),(15);
在会话1上执行:

begin;
select * from ttt where id=4  lock in share mode;
在会话2上执行:

insert into ttt values(5);
insert into ttt values(100);
insert into ttt values(0);
会发现不管是插入哪些记录,都会被阻塞。因为没有索引键值的时候,自动隐式创建索引会锁定整个区间。查看下innodb的事务状态。

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 15102
Purge done for trx's n < 15096 undo n:o < 0 state: running but idle
History list length 944
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 15066, not started
MySQL thread id 53, OS thread handle 0x7f5d57d47700, query id 1615 localhost root
---TRANSACTION 15065, not started
MySQL thread id 52, OS thread handle 0x7f5d57dc9700, query id 1590 localhost root
---TRANSACTION 15097, not started
MySQL thread id 51, OS thread handle 0x7f5d57ecd700, query id 1637 localhost root
---TRANSACTION 0, not started
MySQL thread id 50, OS thread handle 0x7f5d57e0a700, query id 1642 localhost root init
show engine innodb status
---TRANSACTION 15101, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 49, OS thread handle 0x7f5d57d88700, query id 1641 localhost root update
insert into ttt values(0)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 190 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`ttt` trx id 15101 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 15087, ACTIVE 215 sec
2 lock struct(s), heap size 360, 9 row lock(s)
MySQL thread id 43, OS thread handle 0x7f5d57f0e700, query id 1631 localhost root
可以发现,这时的锁不是范围锁,因为没有了locks gap,但却仍然是行锁而不是表锁,只不过此时等价于表锁。如下

mysql> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 15102:190:3:1
lock_trx_id: 15102
  lock_mode: X
  lock_type: RECORD
lock_table: `test`.`ttt`
lock_index: GEN_CLUST_INDEX
lock_space: 190
  lock_page: 3
   lock_rec: 1
  lock_data: supremum pseudo-record
*************************** 2. row ***************************
    lock_id: 15087:190:3:1
lock_trx_id: 15087
  lock_mode: S
  lock_type: RECORD
lock_table: `test`.`ttt`
lock_index: GEN_CLUST_INDEX
lock_space: 190
  lock_page: 3
   lock_rec: 1
  lock_data: supremum pseudo-record
发现确实是行锁而非表锁。并且索引键值那里为"supermum pseudo-record",这表示锁定的是"最大上界伪记录",即锁定的是无穷值。

没索引的时候,哪怕查询具体的行记录都会锁定整个区间,更不用说锁定范围(例如:where id>5)。其实它们的结果都是一样的:锁定整个区间。

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /2 下一条

返回顶部