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:

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

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:

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

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:

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

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:

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:

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.

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:

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:

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!

6 thoughts on “MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows

  1. SKIP LOCKED seem to make things MySql provides about locking kind of useful, finally. But I made some tests with 8.0.3RC and the result about locked records seems incomprehensible for me. I have a simple table with 3 columns (id, number, status)id is primary key autoincrement, status is enum ‘yes’/’no'(has an index) and number is some random int number. Table has 12 records(12 rows). The status on row 7 and 11 is ‘no’ (rest are ‘yes’) and I run:

    START TRANSACTION;
    SELECT * FROM table1 WHERE id > 6 AND status=’yes’ FOR UPDATE;

    data_lock table show :

    https://i.imgur.com/IoQkA5K.png

    When I edit row 8 status=no and run the above statement ,result is:

    https://i.imgur.com/fMJ0Vfp.png

    Can you explain why is so different

  2. In this case, the query is executed using the index merge algorithm https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html. The tell-tale sign is the different entries in the INDEX_NAME column, and the fact that two values are present in LOCK_DATA despite the indexes being over single columns.

    Please note that all scanned rows are locked, not just those filtered out by the WHERE condition: https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html.

    You can also check how many rows were actually read by examining the handler_read status variables:

    show status like ‘handler_read%’;

    I would recommend either using a FORCE INDEX(primary) hint or creating an index over (status, primary) instead of just status. This will ensure that only the rows with status=’yes’ are scanned.

    This scenario does not change with the introduction of SKIP LOCKED and NOWAIT

    1. Martin, thanks for the replay first. Yes, I know that this scenario doesn’t change the introduction of SKIP LOCKED. But if I run 2 sessions, where the first locks ‘yes’ records, and the second ‘no’ records.. without SKIP LOCKED, second session have to wait , the first to complete.Wit SKIP LOCKED, works as it was expected. I will try creating an index over (status, primary)

  3. Pingback: Mysql Skip Error

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.