Home >Database >Mysql Tutorial >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!