MySQL :: MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows

MySQL 8.0.1 introduces two new features which allow you to better manage situations where you have tables with hot row contention. This issue frequently presents itself in scenarios such as worker threads all accessing the same tables trying to find new work, and ecommerce websites trying to keep accurate inventory counts.

My example for today will be trying to book tickets to a Hockey game.

The Booking Process

When booking tickets to the game, I am usually prompted to first pick a section of the stadium where I would like to sit. I then get a nice overview, with already-taken seats in grey, the free ones in blue and the seats I’m currently pondering in white. I am rest-assured that for the next two minutes, I own the white seats:

I expect the booking system to place a temporary hold on my seats while I complete (or abandon) my order. Similarly, as I am selecting my seats I do not expect to be shown seats that are part of a temporary hold for another user:

It has always been possible to manage this in MySQL by having meta-data associated with each seat (sold, available, pending) as well as a timeout for when ‘pending’ seats should become available again. But starting with MySQL 8.0.1 there is a better (and easier) way…

Introducing SKIP LOCKED

Starting with MySQL 8.0.1 we are introducing the SKIP LOCKED modifier which can be used to non-deterministically read rows from a table while skipping over the rows which are locked. This can be used by our booking system to skip orders which are pending. For example:

CREATE TABLE seats (
  seat_no INT PRIMARY KEY,
  booked ENUM('YES', 'NO') DEFAULT 'NO'
);

# generate 100 sample rows
INSERT INTO seats (seat_no)
WITH RECURSIVE my_cte AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_cte WHERE n<100
)
SELECT * FROM my_cte;

CREATE TABLE seats (

seat_no INT PRIMARY KEY,

booked ENUM('YES', 'NO') DEFAULT 'NO'

);

# generate 100 sample rows

INSERT INTO seats (seat_no)

WITH RECURSIVE my_cte AS

(

SELECT 1 AS n

UNION ALL

SELECT 1+n FROM my_cte WHERE n<100

)

SELECT * FROM my_cte;

If I want a lock on seats 2 to 3, I just need to:

START TRANSACTION;
SELECT * FROM seats WHERE seat_no BETWEEN 2 AND 3 AND booked = 'NO'
FOR UPDATE SKIP LOCKED;

START TRANSACTION;

SELECT * FROM seats WHERE seat_no BETWEEN 2 AND 3 AND booked = 'NO'

FOR UPDATE SKIP LOCKED;

This gives the seats I’m interested in, if they are free and nobody else is considering them right now. If not all of them are available, my result set contains the ones that are.

I can either take them:

UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 2 AND 3
COMMIT;

1 2	UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 2 AND 3 COMMIT;

Or unlock them, by doing a ROLLBACK.

FOR UPDATE SKIP LOCKED does just that; in its result set the rows that are locked are skipped, as it were. And the FOR UPDATE part takes an exclusive lock. This can be verified with performance_schema.data_locks (also new in 8.0.1):

START TRANSACTION;
SELECT * FROM seats WHERE seat_no IN (3, 4) FOR UPDATE;

1 2	START TRANSACTION; SELECT * FROM seats WHERE seat_no IN (3, 4) FOR UPDATE;

SELECT object_name, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks WHERE object_name = 'seats';

+-------------+------------+-----------+-----------+-----------+
| object_name | index_name | lock_type | lock_mode | lock_data |
+-------------+------------+-----------+-----------+-----------+
| seats       | NULL       | TABLE     | IX        | NULL      |
| seats       | PRIMARY    | RECORD    | X         | 3         |
| seats       | PRIMARY    | RECORD    | X         | 4         |
+-------------+------------+-----------+-----------+-----------+

SELECT object_name, index_name, lock_type, lock_mode, lock_data

FROM performance_schema.data_locks WHERE object_name = 'seats';

+-------------+------------+-----------+-----------+-----------+

+-------------+------------+-----------+-----------+-----------+

+-------------+------------+-----------+-----------+-----------+

Here, we see that an exclusive (X) lock was taken for records representing seats with number 3 and 4. If the table has a primary key the lock_data column contains the primary key value. Handy, huh? We also see that the intention lock (IX) is taken on the entire table. But fret not, this lock is compatible with read locks. For more details on InnoDB locks, see the MySQL manual.

Quick Tip: If you are not seeing any rows in performance_schema it could be because you forgot to run START TRANSACTION. Locks will be held for the duration of a transaction, so it is important to not use auto-commit in these examples. A ROLLBACK or COMMIT would also result in the performance_schema tables being empty.

Lock only the tables you want.

You may not want to lock all of your tables. For instance, let’s say that seat numbers are not unique to a stadium. In this case you might need the row number, and seat number:

DROP TABLE IF EXISTS seats;
CREATE TABLE seat_rows ( row_no INT PRIMARY KEY, cost DECIMAL );
CREATE TABLE seats (
 seat_no INT NOT NULL,
 row_no INT NOT NULL,
 booked ENUM('YES', 'NO') DEFAULT 'NO',
 PRIMARY KEY (seat_no, row_no)
);

# generate 20 stadium rows with 100 seats/row
INSERT INTO seats (seat_no, row_no)
WITH RECURSIVE my_seats AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_seats WHERE n<100
), my_rows AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_rows WHERE n<20
)
SELECT * FROM my_seats, my_rows;

# Add pricing information for rows

INSERT INTO seat_rows (row_no, cost)
WITH RECURSIVE my_rows AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_rows WHERE n<20
)
SELECT n, 100-(n*2) FROM my_rows;

DROP TABLE IF EXISTS seats;

CREATE TABLE seat_rows ( row_no INT PRIMARY KEY, cost DECIMAL );

CREATE TABLE seats (

seat_no INT NOT NULL,

row_no INT NOT NULL,

booked ENUM('YES', 'NO') DEFAULT 'NO',

PRIMARY KEY (seat_no, row_no)

);

# generate 20 stadium rows with 100 seats/row

INSERT INTO seats (seat_no, row_no)

WITH RECURSIVE my_seats AS

(

SELECT 1 AS n

UNION ALL

SELECT 1+n FROM my_seats WHERE n<100

), my_rows AS

(

SELECT 1 AS n

UNION ALL

SELECT 1+n FROM my_rows WHERE n<20

)

SELECT * FROM my_seats, my_rows;

# Add pricing information for rows

INSERT INTO seat_rows (row_no, cost)

WITH RECURSIVE my_rows AS

(

SELECT 1 AS n

UNION ALL

SELECT 1+n FROM my_rows WHERE n<20

)

SELECT n, 100-(n*2) FROM my_rows;

Now, in order to lock rows only in seats, you would add an OF part to the locking clause:

START TRANSACTION;
SELECT seat_no, row_no, cost
FROM seats s JOIN seat_rows sr USING ( row_no )
WHERE seat_no IN ( 3,4 ) AND sr.row_no IN ( 5,6 )
AND booked = 'NO'
FOR UPDATE OF s SKIP LOCKED;

START TRANSACTION;

SELECT seat_no, row_no, cost

FROM seats s JOIN seat_rows sr USING ( row_no )

WHERE seat_no IN ( 3,4 ) AND sr.row_no IN ( 5,6 )

AND booked = 'NO'

FOR UPDATE OF s SKIP LOCKED;

Aliases in the locking clause work just like aliases in the rest of the query block.

This makes sure only records in the seats table are locked. (I use the term record here, so I don’t confuse you: I mean rows in a database table, not rows in the stadium.) Verifying again with performance_schema.data_locks:

SELECT object_name, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks WHERE object_name = 'seats';
+-------------+------------+-----------+-----------+-----------+
| object_name | index_name | lock_type | lock_mode | lock_data |
+-------------+------------+-----------+-----------+-----------+
| seats       | NULL       | TABLE     | IX        | NULL      |
| seats       | PRIMARY    | RECORD    | X         | 3, 5      |
| seats       | PRIMARY    | RECORD    | X         | 3, 6      |
| seats       | PRIMARY    | RECORD    | X         | 4, 5      |
| seats       | PRIMARY    | RECORD    | X         | 4, 6      |
+-------------+------------+-----------+-----------+-----------+
5 rows in set (0.00 sec)

SELECT object_name, index_name, lock_type, lock_mode, lock_data

FROM performance_schema.data_locks WHERE object_name = 'seats';

+-------------+------------+-----------+-----------+-----------+

+-------------+------------+-----------+-----------+-----------+

+-------------+------------+-----------+-----------+-----------+

5 rows in set (0.00 sec)

Now for a somewhat contrived example. Suppose I want to release seats in stages so that I can maximize the density of the audience for those great panoramic shots that will appear on TV. I could do this by setting a lock on these rows:

START TRANSACTION;
SELECT * FROM seat_rows WHERE row_no >= 10 FOR UPDATE;

1 2	START TRANSACTION; SELECT * FROM seat_rows WHERE row_no >= 10 FOR UPDATE;

I can then modify the previous query that tried to find available seats to require a shared lock on the row. Since shared locks are compatible with other shared locks this will mean that rows 1-9 will be available, but rows 10+ will not.

START TRANSACTION;

# Attempt to book available row (works) 
SELECT seat_no
FROM seats JOIN seat_rows USING ( row_no )
WHERE seat_no IN (3,4) AND seat_rows.row_no IN (5,6)
AND booked = 'NO'
FOR UPDATE OF seats SKIP LOCKED
FOR SHARE OF seat_rows;

# Attempt to book row on hold (lock waits)
SELECT seat_no
FROM seats JOIN seat_rows USING ( row_no )
WHERE seat_no IN (3,4) AND seat_rows.row_no IN (12)
AND booked = 'NO'
FOR UPDATE OF seats SKIP LOCKED
FOR SHARE OF seat_rows;

START TRANSACTION;

# Attempt to book available row (works)

SELECT seat_no

FROM seats JOIN seat_rows USING ( row_no )

WHERE seat_no IN (3,4) AND seat_rows.row_no IN (5,6)

AND booked = 'NO'

FOR UPDATE OF seats SKIP LOCKED

FOR SHARE OF seat_rows;

# Attempt to book row on hold (lock waits)

SELECT seat_no

FROM seats JOIN seat_rows USING ( row_no )

WHERE seat_no IN (3,4) AND seat_rows.row_no IN (12)

AND booked = 'NO'

FOR UPDATE OF seats SKIP LOCKED

FOR SHARE OF seat_rows;

Here is the output of Performance Schema while the attempt to acquire the shared lock on seat_rows is taking place. Notice the additional column of lock_status showing that the shared (S) lock is pending:

SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;
+-------------+-----------+-----------+------------------------+-------------+
| object_name | lock_type | lock_mode | lock_data              | lock_status |
+-------------+-----------+-----------+------------------------+-------------+
| seats       | TABLE     | IX        | NULL                   | GRANTED     |
| seats       | RECORD    | X         | 3, 5                   | GRANTED     |
| seats       | RECORD    | X         | 3, 6                   | GRANTED     |
| seats       | RECORD    | X         | 4, 5                   | GRANTED     |
| seats       | RECORD    | X         | 4, 6                   | GRANTED     |
| seat_rows   | TABLE     | IS        | NULL                   | GRANTED     |
| seat_rows   | RECORD    | S         | 5                      | GRANTED     |
| seat_rows   | RECORD    | S         | 6                      | GRANTED     |
| seat_rows   | RECORD    | S         | 12                     | WAITING     |
| seat_rows   | TABLE     | IX        | NULL                   | GRANTED     |
| seat_rows   | RECORD    | X         | 10                     | GRANTED     |
| seat_rows   | RECORD    | X         | supremum pseudo-record | GRANTED     |
| seat_rows   | RECORD    | X         | 11                     | GRANTED     |
| seat_rows   | RECORD    | X         | 12                     | GRANTED     |
| seat_rows   | RECORD    | X         | 13                     | GRANTED     |
| seat_rows   | RECORD    | X         | 14                     | GRANTED     |
| seat_rows   | RECORD    | X         | 15                     | GRANTED     |
| seat_rows   | RECORD    | X         | 16                     | GRANTED     |
| seat_rows   | RECORD    | X         | 17                     | GRANTED     |
| seat_rows   | RECORD    | X         | 18                     | GRANTED     |
| seat_rows   | RECORD    | X         | 19                     | GRANTED     |
| seat_rows   | RECORD    | X         | 20                     | GRANTED     |
+-------------+-----------+-----------+------------------------+-------------+
22 rows in set (0.00 sec)

SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;

+-------------+-----------+-----------+------------------------+-------------+

+-------------+-----------+-----------+------------------------+-------------+

+-------------+-----------+-----------+------------------------+-------------+

22 rows in set (0.00 sec)

You can mix and match freely between the lock strengths UPDATE and SHARE for as many tables as you like. Funny enough, the machinery for doing this was there all the time, but there was no syntax for it. Previously you only had the syntax FOR UPDATE or LOCK IN SHARE MODE. You still do, and LOCK IN SHARE MODE is a synonym for FOR SHARE, but the new syntax lets you specify the tables and what to do when you encounter a locked row.

NOWAIT

SKIP LOCKED is an action that the storage engine takes when encountering a locked row. The action is to skip the row and the next one goes into the result set instead. This may or may not be what you want.

Consider the previous case, where the exclusive lock (X) on row_no >= 10 was held being held with no intention of releasing it any time soon. In this case it may be better for the statement to fail immediately:

START TRANSACTION;

# Attempt to acquire lock, fail immediately
# if not possible 

SELECT seat_no
FROM seats JOIN seat_rows USING ( row_no )
WHERE seat_no IN (3,4) AND seat_rows.row_no IN (12)
AND booked = 'NO'
FOR UPDATE OF seats SKIP LOCKED
FOR SHARE OF seat_rows NOWAIT;

START TRANSACTION;

# Attempt to acquire lock, fail immediately

# if not possible

SELECT seat_no

FROM seats JOIN seat_rows USING ( row_no )

WHERE seat_no IN (3,4) AND seat_rows.row_no IN (12)

AND booked = 'NO'

FOR UPDATE OF seats SKIP LOCKED

FOR SHARE OF seat_rows NOWAIT;

Without NOWAIT, this query would have waited for innodb_lock_wait_timeout (default: 50) seconds while attempting to acquire the shared lock on seat_rows. With NOWAIT, it is now instructed to throw an error immediately:

ERROR 3572 (HY000): Do not wait for lock.

1	ERROR 3572 (HY000): Do not wait for lock.

Conclusion

Syntax for handling hot rows has been one of the most requested features amongst our large scale users, and we are very happy to see it ship with MySQL 8.0. While my example here applies to booking seats, you can probably see how SKIP LOCKED and NOWAIT apply in other scenarios:

SKIP LOCKED is very handy in the case of multi-threaded workers trying to find the next N rows in a table that need processing.
You can use NOWAIT if you don’t anticipate locked rows, and your business logic does not make sense in case there are any.
You can mix and match between SKIP LOCKED and NOWAIT within the same query, just as long as you don’t try and use both on the same table.

Good luck and thank you for using MySQL!