Use the SELECT ... LOCK IN SHARE MODE statement to lock query results

You can use the SELECT ... LOCK IN SHARE MODE statement to obtain a shared lock when you query data. This prevents other transactions from writing data but allows other transactions to read data. This statement sets a shared lock on the read data rows. Other sessions can read these rows but cannot modify them until the current transaction is committed.

This topic provides specific examples on how to use the SELECT ... LOCK IN SHARE MODE statement to lock query results.

Examples

1. In Session 1, start a transaction.

   START TRANSACTION;
   

2. In Session 1, use the LOCK IN SHARE MODE syntax to query the row where the id value is 1 in the test_tbl1 table and obtain a shared lock.

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

   The return result is as follows:

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

3. In Session 2, start a transaction.

   START TRANSACTION;
   

4. In Session 2, query the row where the id value is 1 in the test_tbl1 table.

   SELECT * FROM test_tbl1 WHERE id = 1;
   

   The return result is as follows:

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

5. In Session 2, change the name value to A1A1A1 for the row where the id value is 1 in the test_tbl1 table. This transaction will remain pending until the transaction in Session 1 is rolled back or committed.

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

   The return result is as follows:

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

6. In Session 1, commit the transaction.

   COMMIT;
   

7. In Session 2, execute the UPDATE statement in Step 5 again.

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

   The return result is as follows:

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