mysql select lock in share mode_【MySQL】Innodb Select……lock in share mode会阻塞Insert么？...

Select语句不特别说明的话，一般加的锁都是Share类型的，本文仅对Share类型的锁进行验证，它是否会阻塞Insert语句呢？

验证环境：MySQL5.7.27，tx_isolation:REPEATABLE-READ，语句执行需要关闭autocommit。

测试表结构：CREATE TABLE `next_key` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` char(4) DEFAULT NULL,

`age` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `index_age` (`age`)

) ENGINE=InnoDB

测试数据：+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | Lucy | 10 |

| 2 | Lily | 15 |

| 3 | Jess | 20 |

+----+------+-----+

事务1执行语句但不提交,线程ID为8：select * from next_key where age > 15 lock in share mode ;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 3 | Jess | 20 |

+----+------+-----+

1 row in set (0.00 sec)

事务2执行语句，线程ID为5：insert into next_key(name,age) values('Lili','40');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

看到语句2是不是很奇怪呢？是不是和我们想的不太一样。select获取到的是Share(S)，Insert获取到的是Exclusive(X)，为什么Insert语句会超时呢？我们先看下相关表里获取到的信息：select * from innodb_trx\G

*************************** 1. row ***************************

trx_id: 17183

trx_state: LOCK WAIT

trx_started: 2020-03-14 16:24:44

trx_requested_lock_id: 17183:258:4:1

trx_wait_started: 2020-03-14 16:34:43

trx_weight: 3

trx_mysql_thread_id: 5(线程ID)

trx_query: insert into next_key(name,age) values('Lili','40')

trx_operation_state: inserting

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 1136

trx_rows_locked: 4

trx_rows_modified: 1

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 0

trx_is_read_only: 0

trx_autocommit_non_locking: 0

*************************** 2. row ***************************

trx_id: 281479595502264

trx_state: RUNNING

trx_started: 2020-03-14 16:34:35

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 3

trx_mysql_thread_id: 8(线程ID)

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 1

trx_lock_structs: 3

trx_lock_memory_bytes: 1136

trx_rows_locked: 3

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 0

trx_is_read_only: 0

trx_autocommit_non_locking: 0

2 rows in set (0.00 sec)

从上面的表中，可以很清楚的但是线程ID为8，的状态为“RUNNING”；而线程ID为5的语句的状态为“LOCK WAIT”select * from innodb_locks\G

*************************** 1. row ***************************

lock_id: 17183:258:4:1

lock_trx_id: 17183

lock_mode: X

lock_type: RECORD

lock_table: `test`.`next_key`

lock_index: index_age

lock_space: 258

lock_page: 4

lock_rec: 1

lock_data: supremum pseudo-record

*************************** 2. row ***************************

lock_id: 281479595502264:258:4:1

lock_trx_id: 281479595502264

lock_mode: S

lock_type: RECORD

lock_table: `test`.`next_key`

lock_index: index_age

lock_space: 258

lock_page: 4

lock_rec: 1

lock_data: supremum pseudo-record

2 rows in set (0.00 sec)

从上图的信息可以清楚的看到，trx_id为281479595502264的锁类型是S；相反在trx_id为17183的锁类型为X。我们继续查看系统记录的信息：------------

TRANSACTIONS

------------

Trx id counter 17184

Purge done for trx's n:o < 17183 undo n:o < 0 state: running but idle

History list length 6

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 281479595503168, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 17183, ACTIVE 619 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1

MySQL thread id 5, OS thread handle 123145405575168, query id 156 localhost root update

insert into next_key(name,age) values('Lili','40')

Trx read view will not see trx with id >= 17183, sees < 17178

------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 258 page no 4 n bits 72 index index_age of table `test`.`next_key` trx id 17183 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;;

很清楚的看到，trx_id: 17183,已经获取到了lock_mode X insert intention。但是需要等待，没有直接执行成功。如果把前面的Select语句commit，那么Insert将会很快执行成功。那么到底是为什么呢？

官方的文档中有一段如下：

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

大致的意思是insert intention lock 属于gap lock的一种。那么什么是gap lock呢？“A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record”官方的解释。通俗的理解。select * from table where age >10 and age < 30 for update，这个时候insert age为20的值时候，就需要等待；相反如果insert不在这个范围的值，则会直接插入成功。所以insert intention lock 插入前也需要判断，如果插入的属于锁定的范围，那么它会等待；相反会直接插入。

小结：

从测试结果来看，表象是Select语句获取到的是Share锁，由于我对码源码的理解有限。所以通过官方文档和相关的验证来看。个人揣测，实际上他获取的是Next-Key Locks。通过innodb_trx表中的记录着重看下面：……

rx_mysql_thread_id: 8

……

trx_rows_locked: 3

……

很清楚的看到它锁定的行数是3，而我们的测试数据呢？+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | Lucy | 10 |

| 2 | Lily | 15 |

| 3 | Jess | 20 |

+----+------+-----+

符合我们的条件>15的只有两条，但是它锁定的是3行，所以它锁定了15，20，20以后的所有，区间等同于：(15, positive infinity)。正好是符合Next-Key Locks的定义。所以个人推断是select实际上获取到的还是X并不是S，而后面的Insert语句属于insert intention lock，它插入的区间正好是我们前面锁定的区间，因此等待。

以后仅仅是我的个人理解，由于个人水平有限，所以若有不对之处，还请指出，谢谢～