Principle
For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition or a range-type search condition.
- For a unique index with a unique search condition,
InnoDBlocks only the index record found, not the gap before it. - For other search conditions, and for non-unique indexes,
InnoDBlocks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.
In other words, DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
Example
delete from transaction_tab where create_time < <<now - 1year>>;
大家都知道，加锁都是基于索引的，如果create_time 字段没索引，就会扫描到主键索引上，那么就算这个查询结果只有一条记录，也会锁整个表。
那么，可以怎么做呢？
# get the max_pk
DELETE FROM transaction_tab WHERE id > 0 and id <= 1000 and create_time < 1663689600 ;
# print the current_id
# sleep 1s
DELETE FROM transaction_tab WHERE id > 1000 and id <= 2000 and create_time < 1663689600 ;
# print the current_id
# sleep 1s
DELETE FROM transaction_tab WHERE id > 2000 and id <= 3000 and create_time < 1663689600 ;
# print the current_id
# sleep 1s
...
DELETE FROM transaction_tab WHERE id > <max_pk> - 1000 and id <= <max_pk> and create_time < 1663689600 ;
Reference
- https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
- https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
- https://blog.51cto.com/lookingdream/4779176