Menu

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:

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATETABLEseats(

seat_noINTPRIMARY KEY,

bookedENUM('YES','NO')DEFAULT'NO'

);

# generate 100 sample rows

INSERTINTOseats(seat_no)

WITHRECURSIVEmy_cteAS

(

SELECT1ASn

UNION ALL

SELECT1+nFROMmy_cteWHEREn<100

)

SELECT*FROMmy_cte;

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

1

2

3

START TRANSACTION;

SELECT *FROM seats WHERE seat_no BETWEEN2AND3ANDbooked='NO'

FORUPDATE 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:

MySQL

1

2

UPDATEseatsSETbooked='YES'WHEREseat_noBETWEEN2AND3

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):

MySQL

1

2

START TRANSACTION;

SELECT*FROMseatsWHEREseat_noIN(3,4)FOR UPDATE;

MySQL

1

2

3

4

5

6

7

8

9

10

SELECTobject_name,index_name,lock_type,lock_mode,lock_data

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

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

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:

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

DROPTABLEIF EXISTSseats;

CREATETABLEseat_rows(row_noINTPRIMARY KEY,costDECIMAL);

CREATETABLEseats(

seat_noINTNOT NULL,

row_noINTNOT NULL,

bookedENUM('YES','NO')DEFAULT'NO',

PRIMARY KEY(seat_no,row_no)

);

# generate 20 stadium rows with 100 seats/row

INSERTINTOseats(seat_no,row_no)

WITHRECURSIVEmy_seatsAS

(

SELECT1ASn

UNION ALL

SELECT1+nFROMmy_seatsWHEREn<100

),my_rowsAS

(

SELECT1ASn

UNION ALL

SELECT1+nFROMmy_rowsWHEREn<20

)

SELECT*FROMmy_seats,my_rows;

# Add pricing information for rows

INSERTINTOseat_rows(row_no,cost)

WITHRECURSIVEmy_rowsAS

(

SELECT1ASn

UNION ALL

SELECT1+nFROMmy_rowsWHEREn<20

)

SELECTn,100-(n*2)FROMmy_rows;

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

MySQL

1

2

3

4

5

6

START TRANSACTION;

SELECTseat_no,row_no,cost

FROMseatssJOINseat_rowssrUSING(row_no)

WHEREseat_noIN(3,4)ANDsr.row_noIN(5,6)

ANDbooked='NO'

FOR UPDATEOFsSKIPLOCKED;

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:

1

2

3

4

5

6

7

8

9

10

11

12

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|

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

5rows inset(0.00sec)

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:

MySQL

1

2

START TRANSACTION;

SELECT*FROMseat_rowsWHERErow_no>=10FOR 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.

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

START TRANSACTION;

# Attempt to book available row (works)

SELECTseat_no

FROMseatsJOINseat_rowsUSING(row_no)

WHEREseat_noIN(3,4)ANDseat_rows.row_noIN(5,6)

ANDbooked='NO'

FOR UPDATEOFseatsSKIPLOCKED

FORSHAREOFseat_rows;

# Attempt to book row on hold (lock waits)

SELECTseat_no

FROMseatsJOINseat_rowsUSING(row_no)

WHEREseat_noIN(3,4)ANDseat_rows.row_noIN(12)

ANDbooked='NO'

FOR UPDATEOFseatsSKIPLOCKED

FORSHAREOFseat_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:

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:

MySQL

1

2

3

4

5

6

7

8

9

10

11

START TRANSACTION;

# Attempt to acquire lock, fail immediately

# if not possible

SELECTseat_no

FROMseatsJOINseat_rowsUSING(row_no)

WHEREseat_noIN(3,4)ANDseat_rows.row_noIN(12)

ANDbooked='NO'

FOR UPDATEOFseatsSKIPLOCKED

FORSHAREOFseat_rowsNOWAIT;

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:

MySQL

1

ERROR3572(HY000):Donotwaitforlock.

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.