Lock query results with SELECT FOR UPDATE

seekdb supports multi-version concurrency control (MVCC). By default, read transactions do not block write transactions. However, you can lock objects in read transactions by using SELECT ... FOR UPDATE, thereby blocking write transactions.

This topic provides an example of how to use SELECT ... FOR UPDATE to lock query results.

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. Here are the details:

• When you use the SELECT ... FOR UPDATE clause, the following behaviors occur:

  • Lock wait: If a row is locked by another transaction, the current transaction will wait until the lock is released or the wait timeout occurs. Once the required lock is obtained, the transaction will continue to execute.
  • Block other transactions: If the current transaction holds a lock on a row and another transaction attempts to lock the same row, the other transaction will be blocked until the current transaction releases the row lock.

• When you use the SELECT ... FOR UPDATE NOWAIT clause, the following behavior occurs:

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

• When you use the SELECT ... FOR UPDATE SKIP LOCKED clause, the following behavior occurs:

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

Example

Create a sample table and insert test data.

1. Create the fruit_order table.

   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 'Order year',
     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 by using 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.

   tip

   Settings at the Global level are not effective for the current session. You need to log in again to create a new session.

2. Execute the following statement in Session 1 to lock the query result for the order with the order ID of 7.

   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. Execute the following statement in Session 2 to change the fruit_price value of the row with the order ID of 7 to 16.15. This statement will wait until the transaction in Session 1 is rolled back or the COMMIT statement is executed. Otherwise, an error will be returned after the wait timeout.

   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. Execute the following statement in Session 1 to commit the transaction.

   COMMIT;

5. Execute the following statement in Session 2 to change the fruit_price value of the row with the order ID of 7 to 16.15.

   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. Execute the following statement in Session 2 to commit the transaction.

   COMMIT;

7. Execute the following statement in Session 1 to 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

Lock query results by using NOWAIT or SKIP LOCKED

1. Execute the following statement in Session 1 to lock the query result for the order with the order ID of 7 by using FOR UPDATE.

   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. Execute the following statement in Session 2 to lock the query result for the order with the order ID of 7 by using FOR UPDATE NOWAIT.

   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. Execute the following statement in Session 3 to lock the query results for orders with the order ID greater than or equal to 7 by using FOR UPDATE SKIP LOCKED.

   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