LOCK IN SHARE MODE

SELECT ... LOCK IN SHARE MODE is used to obtain a shared lock on the data being queried, preventing other transactions from performing write operations on the data, but allowing other transactions to read the data. When this statement is used, shared mode locks are set on the read data rows. Other sessions can read these rows, but they cannot modify them until the current transaction is committed.

tip

seekdb simulates the functionality of the LOCK IN SHARE MODE shared lock syntax by using a write lock. This approach meets the compatibility requirements of some software while ensuring the correctness of the syntax. However, using write locks can cause read operations with the LOCK IN SHARE MODE clause to block each other. Therefore, it is generally not recommended to use this syntax, especially in performance-sensitive scenarios.

This topic provides an example of how to use SELECT ... LOCK IN SHARE MODE to lock query results.

Example

1. In Session 1, execute the following SQL statement to start a transaction.

   START TRANSACTION;

2. In Session 1, use the LOCK IN SHARE MODE syntax to query the record with id equal to 1 in the test_tbl1 table and obtain a shared lock.

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

   The result is as follows:

   +------+------+
   | id   | name |
   +------+------+
   |    1 | A1   |
   +------+------+
   1 row in set

3. In Session 2, execute the following SQL statement to start a transaction.

   START TRANSACTION;

4. In Session 2, execute the following SQL statement to query the record with id equal to 1 in the test_tbl1 table.

   SELECT * FROM test_tbl1 WHERE id = 1;

   The result is as follows:

   +------+------+
   | id   | name |
   +------+------+
   |    1 | A1   |
   +------+------+
   1 row in set

5. In Session 2, execute the following SQL statement to change the value of the name field to 'A1A1A1' for the row with id equal to 1 in the test_tbl1 table. This SQL statement will wait 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 result is as follows:

   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

6. In Session 1, execute the following SQL statement to commit the transaction.

   COMMIT;

7. In Session 2, execute the update statement from Step 4 again.

   UPDATE test_tbl1 SET name = 'A1A1A1' WHERE id = 1;

   The result is as follows:

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0

References

SELECT