Read lock concepts and differences

If you query data in a MySQL transaction and then insert or update related data in the same transaction, the regular SELECT statement does not provide sufficient protection. Other parallel transactions can update or delete the same row just queried in the first transaction. InnoDB supports two types of read locks for additional security:

  • SELECT … LOCK IN SHARE MODE

    Set a shared lock on the read line. Other sessions can read rows and continue to lock rows with a shared lock, but other sessions cannot modify rows with a shared lock until the current transaction commits. If any of these rows is changed by another transaction that has not yet committed, the current query will wait until the transaction ends and then use the latest value.

  • SELECT … FOR UPDATE

    Lock rows and any associated index entries with an exclusive lock as if an UPDATE statement were executed on those rows. Prohibit other transactions from updating, performing SELECT… LOCK IN SHARE MODE or read data at certain transaction isolation levels.

By comparison, it is found that FOR UPDATE locks are similar to write locks IN concurrent programming, while LOCK IN SHARE MODE is read locks. Only one write LOCK or multiple read locks are allowed on the same line at the same time. Once one lock is successfully locked on the data row, another lock attempt enters the wait.

Neither lock blocks a normal SELECT statement from reading these rows, and a consistent read (snapshot read) ignores any locks set on the row record. (Old versions of row records cannot be locked; You can reconstruct row records by applying Undo log to their in-memory copies.

Application scenarios

SELECT … LOCK IN SHARE MODE is applicable to write operations when two tables are related to each other. For example, if there are two tables related to each other: Is it safe to insert a CHILD row into the CHILD table using a normal SELECT statement (snapshot read) that queries the PARENT table and verifies that the PARENT row exists? The answer is no, because other sessions may delete the parent row at some point between your session’s SELECT and INSERT, and you won’t be aware of the deletion.

To avoid this potential problem, we use “add shared lock” to execute SELECT:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Copy the code

After the LOCK IN SHARE MODE query returns the data row named “Jones” IN the PARENT table, you can safely add the CHILD record to the CHILD table and commit the transaction. Any transaction that attempts to acquire an exclusive lock on the corresponding row of the PARENT table before the transaction commits will wait until you have completed the operation and committed the transaction.

However, if it is the application scenario of the same table, for example, the quantity of goods in the e-commerce system should be confirmed to be greater than 1 before the order is generated, and the quantity of goods should be reduced by 1 after the order is generated.

1. select id, amount from products where product_name=’Apple11′;
2. update product set amount=amount-1 where id = {id}’;

This is obviously a problem, because if 1 queries the value of amount as 1, but other sessions have also purchased the item and placed orders, the amount becomes 0, and the second step in the session is problematic.

Can you use LOCK IN SHARE MODE to LOCK this row? It is also unreasonable because when two sessions simultaneously use a shared read lock to lock that row, both sessions will wait for the read lock to be released by the other transaction when they perform a second UPDATE, which will inevitably result in a deadlock causing one of the transactions to roll back.

time Transaction 1 Transaction 2
1 SELECT id, amount FROM products WHERE product_name=’Apple11′ LOCK IN SHARE MODE;
2 Judge amount > 1 SELECT id, amount FROM products WHERE product_name=’Apple11′ LOCK IN SHARE MODE;
3 UPDATE products SET amount=amount-1 WHERE id = {id}’; Judge amount > 1
4 Wait for transaction 2 to release the read lock UPDATE products SET amount=amount-1 WHERE id = {id}’;
5 Continue to wait for A Deadlock was found when trying to get lock. Try restarting transaction)
6 Continue to wait for Roll back the transaction
7 Transaction commit

If two transactions LOCK the row IN shared MODE, no one will be able to update the row (updates are not allowed while other transactions hold the LOCK). What really happens in this case is that one of the two will time out, release the lock, and then the other will successfully update the row.

We need to block transaction 2 briefly by directly locking the write using FOR UPDATE. The following is a derivation of the two operations based on when they occurred

time Transaction 1 Transaction 2
1 SELECT id, amount FROM products WHERE product_name=’Apple11′ FOR UPDATE
2 Judge amount > 1 SELECT id, amount FROM products WHERE product_name=’Apple11′ FOR UPDATE
3 UPDATE products SET amount=amount-1 WHERE id = {id}’ Wait for transaction 1 to release the write lock
4 Transaction commit Wait for transaction 1 to release the write lock
5 Check that amount > 1 is not set
6 Transaction commit Exit

Through the analysis of the above two cases, we can get two kinds of read lock applicable scenarios. LOCK IN SHARE MODE is applicable to ensure service consistency when two tables have service relationships, while FOR UPDATE is applicable to ensure service consistency when operating the same table.

conclusion

  • LOCK IN SHARE MODE is a shared LOCK. Multiple transactions are allowed to hold a row of read locks at the same time.
  • FOR UPDATE is an exclusive lock. Locking a row with a FOR UPDATE transaction blocks other transactions from acquiring the write and read locks FOR that row, and vice versa.
  • Any row locks do not affect snapshot reads for normal SELECT queries, ensuring the concurrency of MySQL.
  • LOCK IN SHARE MODEApplicable to scenarios where two tables have service consistency requirements.
  • FOR UPDATEIt is applicable to ensure service consistency when operating the same table.