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

This topic provides specific examples on how to 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.

Examples

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 records where id is 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 row where id is 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 name to A1A1A1 for the row in the test_tbl1 table where id is 1. The SQL statement waits until the transaction of Session 1 is rolled back or committed.

   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 in 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