MySQL's conceptual understanding of various locks

Optimistic Locking

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

For example, placing an order:

Query product information.

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

Generate orders based on product information.

Decrease the item quantity by 1.

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

Pessimistic Lock

Pessimistic locking is implemented by the locking mechanism provided by the database. Both shared locks and exclusive locks in MySQL are pessimistic locks. By default, database addition, deletion, and modification operations will add exclusive locks, while queries will not add any locks.

Shared lock (read lock)

A shared lock means that multiple different transactions share the same lock for a resource. By adding a shared lock to a resource, you can read the resource yourself and others can also read the resource (you can also add another shared lock, that is, the shared lock shares multiple memories), but it cannot be modified. 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: Add a shared lock to a piece of data in an unfinished transaction.

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

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

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

When updating [Err] 1205 - Lock wait timeout exceeded; try restarting transaction restarting transaction is displayed. You need to wait until all shared locks are released before you can perform the update operation.

UPDATE t_red_packet
SET user_id = 2

Exclusive lock (write lock)

An exclusive lock means that for multiple different transactions, there can only be one lock on the same resource. By adding an exclusive lock to a resource, you can perform add, delete, modify, and query operations on it, but others cannot lock it, let alone perform add, delete, and modify operations. Syntax: select * from table for update .

Window 1: An exclusive lock is added to a piece of data in an unfinished transaction.

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

Window 1, update the row data successfully.

UPDATE t_red_packet
SET user_id = 2

In window 2, query the row of data and you can find it.

SELECT * FROM t_red_packet WHERE id = 1

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

SELECT * FROM t_red_packet WHERE id = 1 FOR UPDATE;

To summarize, a shared lock means that everyone reads together and shares the lock, but no one can modify the locked data. An exclusive lock means that I just want to modify it, you can read it, but you cannot lock it or modify the data.

Row Lock

Row lock is to lock a row of data.

Table Lock

A table lock is to lock a table.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links