Use the SELECT ... LOCK IN SHARE MODE statement to lock query results|OceanBase Cloud| docs|Distributed Database

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

Last Updated：2025-06-24 11:54:40 Updated

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.

Notice

OceanBase Cloud simulates the feature of the LOCK IN SHARE MODE syntax using write locks. This can meet the compatibility requirements of some software while ensuring the correctness of the syntax. Write locks can cause blocking between read operations that use the LOCK IN SHARE MODE syntax. Therefore, we recommend that you do not use this syntax, especially in performance-sensitive scenarios.

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

Examples

In Session 1, start a transaction.
```
START TRANSACTION;
```
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
```
In Session 2, start a transaction.
```
START TRANSACTION;
```

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

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
```
In Session 1, commit the transaction.
```
COMMIT;
```

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