Use the SELECT...FOR UPDATE statement to lock query results

This topic provides specific examples on how to use the SELECT...FOR UPDATE statement to lock query results.

OceanBase Database supports multi-version concurrency control (MVCC). By default, a read transaction does not block a write transaction. You can use the SELECT...FOR UPDATE statement to lock the objects of the read transaction to block the write transaction.

Examples

Use the SELECT...FOR UPDATE statement to lock query results.

1. Create a table and insert proper data into the table.

   1. Execute the following SQL statement to create a table named test_tbl1:

      CREATE TABLE test_tbl1(id number,
         name VARCHAR(18),
         c_date date,
         PRIMARY KEY (id)
         );
      

   2. Execute the following SQL statement to insert data into the test_tbl1 table:

      INSERT INTO test_tbl1 VALUES(1,'A1',date'2019-09-09'),
         (2,'B1',date'2019-06-06'),
         (3,'C1',date'2019-05-05'),
         (4,'D1',date'2020-02-02'),
         (5,'F1',date'2021-01-01');
      

   3. Execute the following SQL statement to commit the transaction:

      COMMIT;
      

2. In Session 1, use the FOR UPDATE syntax to query the row where id equals 1 in the test_tbl1 table and add a row lock. Then, all concurrent updates are blocked and pending.

   SELECT name,c_date FROM test_tbl1 WHERE id = 1 FOR UPDATE;
   

   The result is as follows:

   +------+-----------+
   | NAME | C_DATE    |
   +------+-----------+
   | A1   | 09-SEP-19 |
   +------+-----------+
   1 row in set
   

3. In Session 2, execute the following SQL statement to change the name value to A1A1 in the row where id equals 1 in the test_tbl1 table. The SQL statement waits until the ROLLBACK or COMMIT statement is executed for the transaction in Session 1.

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

   The result is as follows:

   OBE-30006: resource busy; acquire with WAIT timeout expired
   

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

   COMMIT;
   

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

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

   The result is as follows:

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

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

   COMMIT;
   

7. Query the updated data in Session 1.

   SELECT name,c_date FROM test_tbl1 WHERE id = 1;
   

   The result is as follows:

   +------+-----------+
   | NAME | C_DATE    |
   +------+-----------+
   | A1A1 | 09-SEP-19 |
   +------+-----------+
   1 row in set
   

References

Lock mechanism