Lock query results with SELECT FOR UPDATE

OceanBase Database supports multiversion concurrency control (MVCC). By default, read transactions do not block the execution of write transactions. However, you can lock objects in read transactions by using the SELECT ... FOR UPDATE statement, thereby blocking write transactions.

This topic provides specific examples to demonstrate how to lock query results using SELECT ... FOR UPDATE.

Locking behavior

If a row is locked by another transaction, you can use the NOWAIT and SKIP LOCKED options with the SELECT ... FOR UPDATE statement to avoid waiting for the lock to be released. The behavior is as follows:

• When you use the SELECT ... FOR UPDATE statement, the behavior is as follows:

  • Lock wait: If a row is being locked by another transaction, the current transaction will wait until the lock is released or the wait times out. Once the required lock is obtained, the transaction will resume.
  • Lock block: If the current transaction holds a lock on a row and another transaction attempts to lock the same row, the second transaction will be blocked until the current transaction releases the lock.

• When you use the SELECT ... FOR UPDATE NOWAIT statement, the behavior is as follows:

  If a transaction attempts to lock a row that is locked by another transaction, an error is immediately returned to the transaction instead of waiting for the lock to be released.

• When you use the SELECT ... FOR UPDATE SKIP LOCKED statement, the behavior is as follows:

  If a transaction attempts to lock a row that is locked by another transaction, it will skip that row and continue processing the next row.

Examples

Create a sample table and insert test data.

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 order placement',
     PRIMARY KEY (order_id)
     ) COMMENT 'Order table';
   

2. Insert test data into the fruit_order table.

   INSERT INTO fruit_order(user_id, user_name, fruit_price, order_year) VALUES
     (1011,'Zhang San',13.11,'2019'),
     (1011,'Zhang San',22.21,'2020'),
     (1011,'Zhang San',58.83,'2020'),
     (1022,'Li Si',23.34,'2019'),
     (1022,'Li Si',12.22,'2019'),
     (1022,'Li Si',14.66,'2021'),
     (1022,'Li Si',34.44,'2021'),
     (1033,'Wang Wu',51.55,'2020'),
     (1033,'Wang Wu',63.66,'2021');
   

Lock query results with FOR UPDATE

1. Execute the following statement to disable the auto-commit feature.

   SET GLOBAL autocommit = 0;
   

   For more information about the auto-commit feature, see autocommit.

   Notice

   Setting a global-level variable does not affect the current session. You must log in again and start a new session for the change to take effect.

2. In session 1, execute the following statement to lock the query result of the row whose order ID is 7.

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   

   The return result is as follows:

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

3. In session 2, execute the following statement to set the value of fruit_price in the row with the order ID of 7 to 16.15. This SQL statement will wait until the transaction in session 1 is rolled back or the COMMIT statement is executed. Otherwise, an error will be returned once the timeout is exceeded.

   obclient [test]> 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, execute the following statement to commit the transaction.

   COMMIT;
   

5. In session 2, execute the following statement again to set the value of fruit_price in the row with the order ID of 7 to 16.15.

   obclient [test]> 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 2, execute the following statement to commit the transaction.

   COMMIT;
   

7. In session 1, execute the following statement to query the updated data.

   obclient [test]> 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
   

Lock query results with the NOWAIT or SKIP LOCKED option

1. In session 1, execute the following statement to lock the query result of the row with the order ID of 7 using FOR UPDATE.

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   

   The return result is as follows:

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

2. In session 2, execute the following statement to lock the query result of the row with the order ID of 7 using FOR UPDATE NOWAIT.

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE NOWAIT;
   

   An error is immediately returned to session 2.

3. In session 3, execute the following statement to lock the query results of rows whose order IDs are greater than or equal to 7 using FOR UPDATE SKIP LOCKED.

   obclient [test]> SELECT * FROM fruit_order WHERE order_id >= 7 FOR UPDATE SKIP LOCKED;
   

   The return result is as follows:

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        8 |    1033 | Wang Wu      |       51.55 |       2020 |
   |        9 |    1033 | Wang Wu      |       63.66 |       2021 |
   +----------+---------+-----------+-------------+------------+
   2 rows in set
   

References

SELECT