How to use mysql key lock

1. By default, innodb uses temporary key locks to lock records.

select ... for update

2. When the query index contains unique attributes, the temporary key lock will be optimized and downgraded to a record lock, that is, only the index itself is locked, not the range.

3. The temporary key lock will degrade in different scenarios.

Example

事务1
 
start transaction;
select SLEEP(4);
select * from test where age=22 for update;
select * from test where age>23 for update;
select sleep(20);
commit;
 
**************************
事务2
 
start transaction;
select sleep(8);
select * from test where age=20 for update;
select * from test where age=21 for update;
select * from test where age=22 for update;
select * from test where age=23 for update;
commit;
 
 
************************************
事务执行状态
 
事务1
 
start transaction
> OK
> 时间: 0s
 
 
select SLEEP(4)
> OK
> 时间: 4.001s
 
 
select * from test where age=22 for update
> Affected rows: 0
> 时间: 0.001s
 
 
select * from test where age>23 for update
> Affected rows: 0
> 时间: 0.001s
 
 
select sleep(20)
> OK
> 时间: 20s
 
 
commit
> OK
> 时间: 0.001s
 
 
***********************
事务2
 
start transaction
> OK
> 时间: 0.001s
 
 
select sleep(8)
> OK
> 时间: 8s
 
 
select * from test where age=20 for update
> Affected rows: 0
> 时间: 0.001s
 
 
select * from test where age=21 for update
> Affected rows: 0
> 时间: 0.001s
 
 
select * from test where age=22 for update
> Affected rows: 0
> 时间: 14.158s
 
 
select * from test where age=23 for update
> Affected rows: 0
> 时间: 0.001s
 
 
commit
> OK
> 时间: 0s

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