Use the SELECT FOR UPDATE statement to lock query results

OceanBase Database 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 uses examples to describe how to use the SELECT FOR UPDATE statement to lock query results.

Examples

Use the SELECT FOR UPDATE statement to lock query results.

1. Execute the following statements in session 1:

   1. Create a table named dws_ny.

      obclient> CREATE TABLE dws_ny(
                       order_id number,
                       user_id number,
                       user_name VARCHAR(18),
                       fruit_price number,
                       order_date date,
                       PRIMARY KEY (order_id)
                );
      Query OK, 0 rows affected
      

   2. Insert data to the dws_ny table.

      obclient> INSERT INTO dws_ny VALUES
                (1,1011,'Zhang San',13.11,date'2019-09-09'),
                (4,1011,'Zhang San',22.21,date'2020-08-08'),
                (6,1011,'Zhang San',58.83,date'2020-07-07'),
                (2,1022,'Li Si',23.34,date'2019-06-06'),
                (3,1022,'Li Si',12.22,date'2019-05-05'),
                (7,1022,'Li Si',14.66,date'2021-04-04'),
                (8,1022,'Li Si',34.44,date'2021-03-03'),
                (5,1033,'Wang Wu',51.55,date'2020-02-02'),
                (9,1033,'Wang Wu',63.66,date'2021-01-01');
      Query OK, 9 rows affected
      Records: 9  Duplicates: 0  Warnings: 0
      

   3. Query data that meets order_id = 7 in the dws_ny table.

      obclient> SELECT user_name, fruit_price, order_date FROM dws_ny WHERE order_id = 7 FOR UPDATE;
      +-----------+-------------+------------+
      | USER_NAME | FRUIT_PRICE | ORDER_DATE |
      +-----------+-------------+------------+
      | Li Si     |       14.66 | 04-APR-21  |
      +-----------+-------------+------------+
      1 row in set
      

2. Execute the following statement in session 2. This SQL statement will not be executed until the preceding transaction is rolled back or committed.

   obclient> SELECT user_name, fruit_price, order_date FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   ORA-00904: invalid identifier 'ORDER_DATE' in 'field list'
   

3. Execute the following statement in session 1.

   obclient> COMMIT;
   Query OK, 0 rows affected
   

4. Execute the following statement in session 2 again.

   obclient> UPDATE fruit_order SET fruit_price = 16.5  WHERE order_id = 7;
   Query OK, 0 rows affected
   Rows matched: 1  Changed: 0  Warnings: 0
   

5. Query the updated data in session 1.

   obclient> SELECT user_name, fruit_price, order_date FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   +-----------+-------------+------------+
   | USER_NAME | FRUIT_PRICE | ORDER_DATE |
   +-----------+-------------+------------+
   | Li Si     |        16.5 | 04-APR-21  |
   +-----------+-------------+------------+
   1 row in set