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 provides specific examples on how to use the SELECT ... FOR UPDATE statement to lock query results.

Lock actions

If a row is locked by another transaction, you can use the NOWAIT and SKIP LOCKED options in the SELECT ... FOR UPDATE statement that locks reads without waiting for the transaction to release the row lock. Take note of the following considerations:

• The SELECT ... FOR UPDATE clause performs the following actions:

  • Lock wait: If a row is locked by another transaction, the current transaction waits until the lock is released or until a timeout expires. After the required lock is obtained, the transaction execution resumes.
  • Block other transactions: If the current transaction holds the lock of a row, another transaction that attempts to lock the same row will be blocked until the current transaction releases the row lock.

• The SELECT ... FOR UPDATE NOWAIT clause performs the following action:

  When the current transaction attempts to lock a row, if the row has been locked by another transaction, the current transaction immediately returns an error instead of waiting for the lock to be released.

• The SELECT ... FOR UPDATE SKIP LOCKED clause performs the following action:

  When the current transaction attempts to lock a row, if the row has been locked by another transaction, the current transaction skips the row and proceeds to the next row.

Examples

Create a table and insert test 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 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');
   

Use the FOR UPDATE option to lock query results

1. Disable the autocommit feature.

   SET GLOBAL autocommit = 0;
   

   For more information about the autocommit feature, see autocommit.

   Notice

   A global variable does not take effect for the current session but for new sessions established upon re-login.

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

   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. 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 preceding transaction is rolled back or committed. If it is not executed before timing out, a timeout error is returned.

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

   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. Commit the transaction in session 2.

   COMMIT;
   

7. Query the updated data in session 1.

   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
   

Use the NOWAIT or SKIP LOCKED option to lock query results

1. Use the FOR UPDATE option to lock the row where the order_id value is 7 in session 1.

   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      |       16.15 |       2021 |
   +----------+---------+-----------+-------------+------------+
   1 row in set
   

2. Use the FOR UPDATE NOWAIT option to lock the row where the order_id value is 7 in session 2.

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

   The return result is as follows:

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

3. Use the FOR UPDATE SKIP LOCKED option to lock the row where the order_id value is greater than or equal to 7 in session 3.

   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 statement