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.

SELECT ... FOR UPDATE

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.

Examples

Create a table and insert proper data into the table.

1. Create a table named fruit_order.

   CREATE TABLE fruit_order(
     order_id INT NOT NULL AUTO_INCREMENT COMMENT 'Order ID',
     user_id BIGINT NOT NULL COMMENT 'Customer ID',
     user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Customer name',
     fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Order amount',
     order_year SMALLINT NOT NULL COMMENT 'Year of orders',
     PRIMARY KEY (order_id)
     ) COMMENT 'Order table';
   

2. Insert data into the fruit_order table.

   INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
     (1,1011,'Zhang San',13.11,'2019'),
     (4,1011,'Zhang San',22.21,'2020'),
     (6,1011,'Zhang San',58.83,'2020'),
     (2,1022,'Li Si',23.34,'2019'),
     (3,1022,'Li Si',12.22,'2019'),
     (7,1022,'Li Si',14.66,'2021'),
     (8,1022,'Li Si',34.44,'2021'),
     (5,1033,'Wang Wu',51.55,'2020'),
     (9,1033,'Wang Wu',63.66,'2021');
   

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

1. Start a transaction in Session 1.

   BEGIN;
   

2. Lock the row where the order_id value is 7 in Session 1.

   SELECT user_name, fruit_price, order_year FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   

   The return result is as follows:

   +-----------+-------------+------------+
   | user_name | fruit_price | order_year |
   +-----------+-------------+------------+
   | Li Si      |       14.66 |       2021 |
   +-----------+-------------+------------+
   1 row in set
   

3. Change the fruit_price value to 16.15 for the row where the order_id value is 7 in Session 2. This transaction remains pending until the transaction in Session 1 is rolled back or committed. If it is not executed before timing out, a timeout error is returned.

   UPDATE fruit_order SET fruit_price = 16.15  WHERE order_id = 7;
   

   The return result is as follows:

   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   

4. Commit the transaction in Session 1.

   COMMIT;
   

5. Change the fruit_price value to 16.15 for the row where the order_id value is 7 again in Session 2.

   UPDATE fruit_order SET fruit_price = 16.15  WHERE order_id = 7;
   

   The return result is as follows:

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

6. Query the updated data in Session 1.

   SELECT * FROM fruit_order WHERE order_id = 7;
   

   The return result is as follows:

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        7 |    1022 | Li Si      |       16.15 |       2021 |
   +----------+---------+-----------+-------------+------------+
   1 row in set