Examples of mysql Gaplock and monitoring lock

In the InnoDB engine, there are roughly three row-level locks in MySQL: recordlock, Gap, and next-keylocks. Recordlock is used to lock a row of records, while gap locks the records in a range. Next-keylocks is the effect of the first two.

The following is an example:

Create a table named user_info, which indicates the user information. user_num is a unique index with data records initially created.

Run session1 first

Session1:
Mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 Sec)

Mysql> select count (1) From user_info where user_num <'u1119yux 'lock in share mode;
+ ---------- +

 

Session2:
Mysql> set autocommit = 0;

Insert into user_info values (100000000, 'u10000', 'ddddd ', 'dddddd', 'name', 'realname', '123', 11, 'dd ', 'dd', 2012, '2017-10-10 ');

At this time, the insert Statement of session2 does not respond until it times out.

 

You can view locks in the following ways,

1. Show engine InnoDB status

2. Show processlist

3. View INFORMATION_SCHEMA.innodb_trx, innodb_locks, and innodb_lock_waits.

Mysql> show engine innodb status \ G

We can see that trx B5F5001 is waiting for X record locks, but no one can tell who holds the lock.

------------
TRANSACTIONS
------------
Trx id counter B5F5002
Purge done for trx's n: o <B5F4EEF undo n: o <0
History list length 685
List of transactions for each session:
--- TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7f566c797700, query id 30 localhost root
Show engine innodb status
--- TRANSACTION B5F4EE7, ACTIVE 25 sec inserting
Mysql tables in use 1, locked 1
Lock wait 2 lock struct (s), heap size 376, 1 row lock (s), undo log entries 1
MySQL thread id 2, OS thread handle 0x7f566c7d8700, query id 27 localhost root update
Insert into lsmp_lottery_user values (100000000, 'u1019yux ', 'dddddd', 'ddddd', 'name', 'realname', '123', 11, 'dd ', 'dd', 2012, '2017-10-10 ')
------- TRX has been waiting 25 sec for this lock to be granted:
Record locks Space ID 4 Page No 14 N bits 648 index'index _ luser_num 'of table 'dlsp'. 'lsmp _ lottery_user 'trx id b5f5001 lock_mode x locks gap before rec insert intenwaiting
Record lock, heap no 552 physical record: n_fields 2; compact format; Info Bits 0
0: Len 18; hex 7531304151716b326d69673331783374677a; ASC u101aqk2mig31x3tgz ;;
1: Len 4; hex 8000fdf8; ASC ;;

------------------
--- Transaction b5f4ee6, active 55 Sec
5 lock struct (s), heap size 1248,196 3 row lock (s)
MySQL thread Id 1, OS thread handle 0x7f4b30560700, query ID 13 localhost Root

 

View information about the INFORMATION_SCHEMA lock,

We can see that the transaction B5F4EE7 is waiting for the S lock of the transaction B5F4EE6,

Transaction B5F4EE6 holds the range lock in the row lock

Mysql> select * from INFORMATION_SCHEMA.innodb_locks \ G;
* *************************** 1. row ***************************
Lock_id: B5F4EE7: 4: 14: 552.
Lock_trx_id: B5F4EE7
Lock_mode: X, GAP
Lock_type: RECORD
Lock_table: 'dlsp '. 'lsmp _ LOTTERY_USER'
Lock_index: 'index _ LUSER_NUM'
Lock_space: 4
Lock_page: 14
Lock_rec: 552
Lock_data: 'u101aqk2mig31x3tgz'
* *************************** 2. row ***************************
Lock_id: B5F4EE6: 4: 14: 552.
Lock_trx_id: B5F4EE6
Lock_mode: S
Lock_type: RECORD
Lock_table: 'dlsp '. 'lsmp _ LOTTERY_USER'
Lock_index: 'index _ LUSER_NUM'
Lock_space: 4
Lock_page: 14
Lock_rec: 552
Lock_data: 'u101aqk2mig31x3tgz'
2 rows in set (0.00 sec)

 

Mysql> select * from INFORMATION_SCHEMA.innodb_lock_waits \ G;
* *************************** 1. row ***************************
Requesting_trx_id: B5F4EE7
Requested_lock_id: B5F4EE7: 4: 14: 552
Blocking_trx_id: B5F4EE6
Blocking_lock_id: B5F4EE6: 4: 14: 552.
1 row in set (0.00 sec)

ERROR:
No query specified