MySQL INSERT Statement Lock analysis

Recently, the lock operation of insert is more puzzling, so do it yourself, a look at exactly. The main thing is to take a look at three SQL to see what locks are being used in the execution of SQL.

SELECT * FROM INFORMATION_SCHEMA. Innodb_trx\g; Records the transactions that are currently executing, as well as some state of the transaction

SELECT * FROM INFORMATION_SCHEMA. Innodb_locks\g; Contains the specifics of the INNODB transaction lock, including the lock that the transaction is requesting and the lock the transaction is added to.

SELECT * FROM INFORMATION_SCHEMA. Innodb_lock_waits; The state of the lock waits for the transaction that contains the blocked

MySQL version: 5.6.25-73.1-log uses INNODB, isolation level is Repeatable-read

First look at the table structure: There are unique keys in the table

Perform the same insert operation in each of the two transactions:

Insert into Room_poi_cut_result (Poi_id,poi_orgunit_id,old_bd,new_bd,cut_reason,is_execute) VALUES ( 12345,22,12,12,0,0);

First look at the data for the Innodb_trx table: you can see that transaction 10722584462 is in the execution state, and transaction 10722584486 is in a blocked state

Then look at the state of the lock: As you can see, transaction 10722584462 and transaction 10722584486 are getting row locks, one is S lock, one is x lock

Finally, let's look at the data in this table innodb_lock_waits: You can see that transaction 10722584486 is being blocked at 10722584486:29178:10:638, and the lock holder is a transaction 10722584462

So, here we can conclude that first the unique key plus the row lock, followed by the INSERT statement will have a lock escalation process, from S lock to x lock.

Next, do a second experiment and add the on UPDATE statement to the Inser.

Perform the same insert operation in each of the two transactions:

Insert into Room_poi_cut_result (Poi_id,poi_orgunit_id,old_bd,new_bd,cut_reason,is_execute) VALUES ( 12345,22,12,12,0,0) on duplicate key update poi_id=12345;

Then take a look at the data for the three tables innodb_trx,innodb_locks,innodb_lock_waits:

conclusion, first the unique key plus row lock, followed by the first experiment is different, directly on the unique key plus x lock.

Next, do a third experiment, remove the unique key from the table, and then perform the insert operation:

Table structure:

Then execute the following insert statement in two transactions:

Insert into ROOM_POI_CUT_RESULT2 (Poi_id,poi_orgunit_id,old_bd,new_bd,cut_reason,is_execute) VALUES ( 12345,22,12,12,0,0);

As a result, there is no blocking, look at the data in the table Innodb_trx: You can see that both transactions are in running state, and trx_rows_locked are 0.

MySQL INSERT Statement Lock analysis