SELECT ... LOCK IN SHARE MODE is used to acquire a shared lock while querying data, preventing other transactions from writing to the data but allowing other transactions to read it. When this statement is used, a shared-mode lock is placed on the rows being read. Other sessions can read these rows, but they cannot modify them until the current transaction is committed.
Notice
OceanBase AI Database emulates the functionality of the LOCK IN SHARE MODE shared lock syntax by using write locks. This meets compatibility requirements for some software while ensuring syntactic correctness. However, since write locks cause read operations with the LOCK IN SHARE MODE command to block each other, this syntax is generally not recommended, especially in performance-sensitive scenarios.
This topic demonstrates how to use SELECT ... LOCK IN SHARE MODE to lock query results through examples.
Example
In Session 1, execute the following SQL statement to start a transaction.
START TRANSACTION;In Session 1, use the
LOCK IN SHARE MODEsyntax to query the record withidequal to1in thetest_tbl1table and acquire a shared lock.SELECT * FROM test_tbl1 WHERE id = 1 LOCK IN SHARE MODE;The query result is as follows:
+------+------+ | id | name | +------+------+ | 1 | A1 | +------+------+ 1 row in setIn Session 2, execute the following SQL statement to start a transaction.
START TRANSACTION;In Session 2, execute the following SQL statement to query the record with
idequal to1in thetest_tbl1table.SELECT * FROM test_tbl1 WHERE id = 1;The query result is as follows:
+------+------+ | id | name | +------+------+ | 1 | A1 | +------+------+ 1 row in setIn Session 2, execute the following SQL statement to change the
namevalue to 'A1A1A1' for the row withidequal to1in thetest_tbl1table. This SQL statement waits until the transaction in Session 1 is rolled back or committed before it can be executed.UPDATE test_tbl1 SET name = 'A1A1A1' WHERE id = 1;The query result is as follows:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionIn Session 1, execute the following SQL statement to commit the transaction.
COMMIT;In Session 2, execute the update statement from Step 5 again.
UPDATE test_tbl1 SET name = 'A1A1A1' WHERE id = 1;The query result is as follows:
Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0