How to use mysql insert intention lock

1. The insertion intention lock is a gap lock, not an intention lock. It is generated by the insert operation. When multiple transactions write different data to the same index gap at the same time, there is no need to wait for other transactions to complete, and no lock wait will occur.

Assume that there is a record index containing key values ​​4 and 7, and different transactions insert 5 and 6 respectively. Each transaction will generate an insertion intention lock, and add it to between 4-7 to get the inserted row. Exclusive locks, but not mutual locks, because the data rows do not conflict.

2. Inserting intention locks will not prevent any locks, and inserting records will hold record locks.

Example

insert into test_user(user_id,name,age) values(2,'b',10)
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6628 page no 4 n bits 72 index `index_user` of table `test`.`test_user` trx id 117851203
插入意向锁
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;;

The above is the detailed content of How to use mysql insert intention lock. For more information, please follow other related articles on the PHP Chinese website!