MySQL

【MySQL】Lock Demo（锁实验）- Delete

Posted by 西维蜀黍 on 2023-09-28, Last Modified on 2023-09-28

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, InnoDB locks only the index record found, not the gap before it.
  • For other search conditions, and for non-unique indexes, InnoDB locks 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

FEATURED TAGS
algorithm algorithmproblem architecturalpattern architecture aws c# cachesystem codis compile concurrentcontrol database dataformat datastructure debug design designpattern distributedsystem django docker domain engineering freebsd git golang grafana hackintosh hadoop hardware hexo http hugo ios iot java javaee javascript kafka kubernetes linux linuxcommand linuxio lock macos markdown microservices mysql nas network networkprogramming nginx node.js npm oop openwrt operatingsystem padavan performance programming prometheus protobuf python redis router security shell software testing spring sql systemdesign truenas ubuntu vmware vpn windows wmware wordpress xml zookeeper
FRIENDS

TOC