MySQL Lock--SELECT LOCK IN SHARE MODE加锁
测试环境
- MySQL版本： 5.7.30
- 事务级别： READ-COMMITTED
测试内容
在MySQL官方文档中有如下描述：
For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause).
SELECT ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
SELECT ... FOR UPDATE sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
For index records the search encounters, SELECT ... FOR UPDATE blocks other sessions from doing SELECT ... LOCK IN SHARE MODE or from reading in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
## 测试数据
```SQL
mysql> show create table tb1001 \G
*************************** 1. row ***************************
Table: tb1001
Create Table: CREATE TABLE `tb1001` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL,
`c2` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=524273 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from tb1001 limit 10;
+----+----+----+
| id | c1 | c2 |
+----+----+----+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 10 | 10 | 10 |
| 11 | 11 | 11 |
| 12 | 12 | 12 |
| 13 | 13 | 13 |
| 14 | 14 | 14 |
| 15 | 15 | 15 |
| 16 | 16 | 16 |
+----+----+----+
10 rows in set (0.00 sec)
测试准备
会话1执行：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select c1 from tb1001 where c1<10 lock in share mode;
+----+
| c1 |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
查看会话1锁信息
show engine innodb status \G
---TRANSACTION 6348940, ACTIVE 364 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 3184000, OS thread handle 140609456948992, query id 44877542 172.16.24.1 mysql_admin
查询根据过滤条件c1<10在索引idx_c1上进行查找，查询对c1=[1,2,3,10]的索引记录加锁，因此产生4个row lock。
由于查询未涉及到回表查询，因此不会访问主键索引，也不会对主键索引记录加锁。
测试01
保持会话1事务，会话2执行：
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
## 查询被阻塞
mysql> select * from tb1001 where c1=10 for update;
查看锁信息：
---TRANSACTION 6349451, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3184785, OS thread handle 140609456948992, query id 44900814 172.16.24.1 mysql_admin statistics
select * from tb1001 where c1=10 for update
------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 37 n bits 1152 index idx_c1 of table `demodb`.`tb1001` trx id 6349451 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;; ## c1=10的记录
1: len 4; hex 8000000a; asc ;;
------------------
---TRANSACTION 6349420, ACTIVE 94 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 3184770, OS thread handle 140609595356928, query id 44899425 172.16.24.1 mysql_admin
--------
证明会话1在索引idx_c1上持有c1=10的行锁
测试02
保持会话1事务，会话2执行：
mysql>
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb1001(c1,c2)select 9,9;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
证明会话1在索引idx_c1上未持有c1=10的行之前的间隙锁。
注意事项
在部分测试过程中，LOCK IN SHARE MODE产生的锁不会在SHOW ENGINE INNODB STATUS中显示出来，如：
------------
TRANSACTIONS
------------
Trx id counter 6349568
Purge done for trx's n:o < 6349567 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422085395426832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395421360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395423184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395422272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395420448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395425008, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395419536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395418624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395417712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395416800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395415888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395414976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395414064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395413152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395412240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422085395411328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6349567, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3184968, OS thread handle 140609456948992, query id 44906413 172.16.24.1 mysql_admin statistics
select * from tb1001 where c1=10 for update
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 168 page no 37 n bits 1152 index idx_c1 of table `demodb`.`tb1001` trx id 6349567 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 4; hex 8000000a; asc ;;
------------------
需要使用information_schema.INNODB_LOCKS来查看
mysql> select * from information_schema.INNODB_LOCKS;
+--------------------------+-----------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------------------+-----------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
| 6349893:168:37:5 | 6349893 | X | RECORD | `demodb`.`tb1001` | idx_c1 | 168 | 37 | 5 | 10 |
| 422085395424096:168:37:5 | 422085395424096 | S | RECORD | `demodb`.`tb1001` | idx_c1 | 168 | 37 | 5 | 10 |
+--------------------------+-----------------+-----------+-----------+-------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.01 sec)
