Mastering SKIP LOCKED in MySQL

Over the past few months, I've been deeply involved in designing a system that pushes MySQL's SKIP LOCKED feature to its limits. This journey revealed several nuances and optimization opportunities that can significantly impact performance. In this post, we'll explore these insights, demonstrating how to reduce lock overhead, optimize schema design, and fine-tune transaction isolation levels to achieve remarkable performance gains.

What is SKIP LOCKED?

SKIP LOCKED is part of SELECT expression that enables you to retrieve rows that aren't locked by other transactions, effectively allowing multiple transactions to work on different sets of rows simultaneously. You'd most often use that to build some sort of a ticketing system.

Let's consider the following schema:

CREATE TABLE `available_tickets` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  INDEX (event_id)
) ENGINE=InnoDB;

CREATE TABLE `reserved_tickets` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `quantity` int NOT NULL,
  `cart_token` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

A typical transaction using SKIP LOCKED might look like this:

BEGIN;
SELECT id FROM available_tickets ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;
-- ... on the client, verify that you got 10 rows and not less
INSERT INTO reserved_tickets (quantity, cart_token) VALUES (10, `some-uuid`);
DELETE FROM available_tickets WHERE id IN (...)
COMMIT;

Before the introduction of SKIP LOCKED in MySQL 8, this is not something you could achieve in a high concurrency fashion. Let's review the nuances of scaling this design.

Optimizing for Multiple Events

In real-world scenarios, you'd likely want to pool reservations by event. Let's modify our schema to include an event_id:

ALTER TABLE `available_tickets` ADD COLUMN `event_id` bigint NOT NULL;

Now our query becomes:

BEGIN;
SELECT id FROM available_tickets
WHERE event_id=? -- grab rows for a specific event
ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;

INSERT INTO reserved_tickets (quantity, cart_token) VALUES (10, `some-uuid`);
DELETE FROM available_tickets WHERE id IN (...)
COMMIT;

Let's see the side-effects of introducing that split by event_id.

Understanding InnoDB Locks

To optimize our system, we need to understand how InnoDB manages locks. The performance_schema.data_locks table is an invaluable tool for this. Let's examine the locks acquired during our transaction:

mysql> SELECT thread_id, object_name, index_name, lock_type, lock_mode, lock_data FROM performance_schema.data_locks order by object_name;
+-----------+-------------------+------------+-----------+---------------+-----------+
| thread_id | object_name       | index_name | lock_type | lock_mode     | lock_data |
+-----------+-------------------+------------+-----------+---------------+-----------+
|        51 | available_tickets | NULL       | TABLE     | IX            | NULL      |
|        51 | available_tickets | event_id   | RECORD    | X             | 1, 2      |
|        51 | available_tickets | PRIMARY    | RECORD    | X,REC_NOT_GAP | 2         |
+-----------+-------------------+------------+-----------+---------------+-----------+
3 rows in set (0.02 sec)

Notice that we have three locks: a table lock (which we can ignore), and two record locks on different indexes.

Because our SELECT statement includes where event_id=1, InnoDB has to track locks on both event_id index and on the primary key. I recommend reading A Comprehensive (and Animated) Guide to InnoDB Locking by my colleague Jahfer for a deeper dive into locks. Planetscale's post on B+trees is another great resource.

Optimizing with Composite Primary Keys

Our table uses PRIMARY KEY (id), which means that whenever you have WHERE event_id=1, InnoDB needs to do two operations:

Look up event_id index, find value of 1 on the B+tree.
Find that the first id under that event_id is 2 and look up that primary key

Which is what causes it to acquire two locks.

We can reduce that by using a composite primary key and making event_id part of the primary key. Let's modify our schema:

CREATE TABLE `available_tickets` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `event_id` bigint NOT NULL,
  PRIMARY KEY (`event_id`, `id`),
  KEY (`id`)
) ENGINE=InnoDB;

Now, let's check our locks again:

mysql> SELECT thread_id, object_name, index_name, lock_type, lock_mode, lock_data 
       FROM performance_schema.data_locks order by object_name;
+-----------+-------------------+------------+-----------+-----------+-----------+
| thread_id | object_name       | index_name | lock_type | lock_mode | lock_data |
+-----------+-------------------+------------+-----------+-----------+-----------+
|        59 | available_tickets | NULL       | TABLE     | IX        | NULL      |
|        59 | available_tickets | PRIMARY    | RECORD    | X         | 1, 2      |
+-----------+-------------------+------------+-----------+-----------+-----------+

By making our primary key a composite of (event_id, id) we've successfully reduced two locks to one, which can significantly improve performance in high-concurrency scenarios.

Fine-tuning Transaction Isolation Levels

InnoDB's default transaction isolation level is REPEATABLE READ (source). However, this can lead to undesired lock behavior, especially when dealing with empty result sets. Let's examine what happens when tickets are sold out:

mysql> SELECT @@GLOBAL.transaction_isolation;
+--------------------------------+
| @@GLOBAL.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from available_tickets where event_id=1 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
Empty set (0.01 sec)

mysql> SELECT thread_id, object_name, index_name, lock_type, lock_mode, lock_data FROM performance_schema.data_locks order by object_name;
+-----------+-------------------+------------+-----------+-----------+------------------------+
| thread_id | object_name       | index_name | lock_type | lock_mode | lock_data              |
+-----------+-------------------+------------+-----------+-----------+------------------------+
|        59 | available_tickets | NULL       | TABLE     | IX        | NULL                   |
|        59 | available_tickets | PRIMARY    | RECORD    | X         | supremum pseudo-record |
+-----------+-------------------+------------+-----------+-----------+------------------------+
2 rows in set (0.01 sec)

The supremum pseudo-record lock prevents other transactions from inserting new rows, which might not be desirable if you want to add more tickets later. To resolve this, we can use the READ COMMITTED isolation level:

mysql> set global TRANSACTION isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from available_tickets where event_id=1 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
Empty set (0.01 sec)

mysql> SELECT thread_id, object_name, index_name, lock_type, lock_mode, lock_data FROM performance_schema.data_locks order by object_name;
+-----------+-------------------+------------+-----------+-----------+-----------+
| thread_id | object_name       | index_name | lock_type | lock_mode | lock_data |
+-----------+-------------------+------------+-----------+-----------+-----------+
|        60 | available_tickets | NULL       | TABLE     | IX        | NULL      |
+-----------+-------------------+------------+-----------+-----------+-----------+
1 row in set (0.00 sec)

Now we've eliminated the extra supremum pseudo-record lock, allowing for more flexibility in our system.

I recommend checking InnoDB Locking Explained with Stick Figures by Bill Karwin if you want to dive deeper into how supremum pseudo-record works.

Performance Results

By implementing these optimizations, I've seen remarkable performance improvements: single MySQL server handling 2M+ ticket reservation transactions per minute while the average latency of SELECT ... FOR UPDATE SKIP LOCKED query staying under 400μS.

By reducing lock overhead, using composite primary keys, and fine-tuning isolation levels, we can build highly scalable systems capable of handling millions of transactions per minute with sub-millisecond latency.

Remember, while these optimizations can yield significant performance gains, always test thoroughly in your specific use case. Database optimization is often a balance between performance, consistency, and operational flexibility.