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

OceanBase Cloud supports multiversion 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.

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

Examples

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

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

   1. Create a table named test_tbl1.

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

   2. 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. 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 return 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 return result is as follows:

   ORA-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 return 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. In Session 1, query the updated data.

   SELECT name,c_date FROM test_tbl1 WHERE id = 1;
   

   The return result is as follows:

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