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 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.

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. In Session 1, start a transaction.

   BEGIN;
   

2. In Session 1, lock the query result of the row where the order_id value is 7.

   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. In Session 2, change the fruit_price value to 16.15 for the row where the order_id value is 7. This transaction will remain pending until the transaction in Session 1 is rolled back or committed. If it is not executed before timing out, a timeout error will be 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. In Session 1, commit the transaction.

   COMMIT;
   

5. In Session 2, change the fruit_price value to 16.15 for the row where the order_id value is 7 again.

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

   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