Detailed explanation of row-level locks in MySQL

There are many types of locks in MySQL. If you distinguish them from the granularity of the lock, you can divide them intoTable-level lock, row-level lock, page-level lock。

This article mainly introduces

Row-level lock

The row-level lock in MySQL is a fine-grained lock provided by the InnoDB storage engine in MySQL.Based on index implementation, locks are added to the index, not the data row itself.

Also for row-level locks, the InnoDB storage engine can be subdivided intoRecord Lock, Gap Lock, Next-Key Lock and Insert Intention Lock。

Record Lock

Record lock is commonly referred to as line lock, as the name suggests, locks a certain line of data.

-- id = 1 The guild will be added with record exclusive lock
update product_sale_record set sold_num = sold_num + 1 where id = 1;
-- id = 1 The guild will be shared with the
select * from product_sale_record  where id = 1; LOCK IN SHARE MODE;

Examples of scenarios

Suppose two transactions A and B execute the following SQL concurrently:

start transaction;
update t_shop_product_sale_record set sold_num = sold_num +1 where id = 1;
commit;

When transaction A executes update, it will lock the data for the row id = 1.

When transaction A is not committed, if transaction B is enabled to execute update, a lock wait will occur and timeout will occur: Lock wait timeout exceeded; try restarting transaction

Gap Lock

Locks the gap between index records to prevent other transactions from inserting new data in scope.

-- id exist 1 and 10 The gap between them will be locked exclusively。
update product_sale_record WHERE id > 1 AND id < 10;
-- id exist 1 and 10 The gap between them will be shared locked。
select * from product_sale_record  WHERE id > 1 AND id < 10 LOCK IN SHARE MODE;

Next-Key Lock

A key lock can be understood as a combination of a record lock and a gap lock, locking the index record and its previous gap.

-- id > 1 The record and the gaps ahead will be locked exclusively
update product_sale_record WHERE id > 1;
-- id > 1 The record and its previous gap will be shared locked
select * from product_sale_record where id > 1 LOCK IN SHARE MODE;

This is the end of this article about row-level locks in MySQL. For more related content of row-level locks in mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!