MySQL Conceptual Understanding of Various Locks

Optimistic lock

Optimistic locking is mostly based on data version recording mechanism, 1 is to add a database table "version" field. When reading data, read this version number 1 together, and when updating later, add 1 to this version number. At this time, the version data of the submitted data is compared with the current version information of the corresponding record of the database table. If the version number of the submitted data is greater than the current version number of the database table, it will be updated, otherwise it will be considered as expired data.

For example, placing an order:

Find out the commodity information.

select (quantity, version)
from t_goods
where id = #{id}

Generate orders based on commodity information.

Reduce the quantity of goods by 1.

update t_goods
set quantity = quantity - 1
where id = #{id} and version = #{version}

Pessimistic lock

Pessimistic locking relies on the locking mechanism provided by the database. Both shared and exclusive locks in MySQL are pessimistic locks. By default, exclusive locks will be added to database additions, deletions and modifications, while no locks will be added to queries.

Shared lock (read lock)

Shared lock means that for multiple different transactions, one resource shares the same lock. If you add a shared lock to a 1 resource, you can read the resource by yourself, and others can also read the resource (you can also add a shared lock, that is, the shared lock shares multiple memories), but you cannot modify it. If you want to modify it, you must wait until all shared locks are released. Syntax: select * from table lock in share mode; .

For example:

Window 1, which places a shared lock on 1 piece of data in 1 open transaction.

BEGIN;
SELECT * FROM t_red_packet WHERE id = 1 LOCK IN SHARE MODE;

Window 2, add a shared lock to the same data, and the lock is successful.

SELECT * FROM t_red_packet WHERE id = 1 LOCK IN SHARE MODE;

Window 1 and Window 2, update the row data, prompt [Err] 1205 - Lock wait timeout exceeded; try restarting transaction . You need to wait until all shared locks are released before you can perform update operations.

UPDATE t_red_packet
SET user_id = 2

Exclusive lock (write lock)

Exclusive lock means that there can only be one lock on the same resource for multiple different transactions. If you add exclusive lock to a certain resource, you can add, delete and change it yourself, but others can't lock it, let alone add, delete and change it. Syntax: select * from table for update .

Window 1, which places an exclusive lock on one piece of data in one open transaction.

BEGIN;
SELECT * FROM t_red_packet WHERE id = 1 FOR UPDATE;

Window 1, update the data of this row, successful.

UPDATE t_red_packet
SET user_id = 2

Window 2, query the data of this row, which can be queried.

SELECT * FROM t_red_packet WHERE id = 1

Window 2 locks the data, prompting [Err] 1205 - Lock wait timeout exceeded; try restarting transaction .

SELECT * FROM t_red_packet WHERE id = 1 FOR UPDATE;

To sum up, the shared lock means that everyone reads it together and shares it together, but no one should modify the locked data. The exclusive lock means that I just want to modify it. You can read it, but you can't get to the lock or modify the data.

Row lock

Row lock is to lock one row of data.

Table lock

Table lock is to lock a table.

Summarize