Understanding MySQL 8.0.1 NOWAIT and SKIP LOCKED Features for Reducing Lock Wait

Preface

Since MySQL 8.0.x, Oracle has been delivering feature releases that add many new capabilities across 17 incremental versions. The NOWAIT and SKIP LOCKED keywords were introduced in 8.0.1, and this article presents their background, functionality, and underlying principles, illustrated with a fictional case to show their real‑world effect.

Background

Before MySQL 8.0, high‑concurrency scenarios such as flash sales or limited‑stock purchases typically relied on external systems like Redis, Memcached, or Zookeeper because using RDBMS exclusive locks would serialize client operations, leading to unacceptable performance penalties.

The core problem is the need for exclusivity —operations on a particular row or set of rows must be mutually exclusive at the business level. Traditional RDBMS exclusive locks force clients into a lock‑wait state, turning parallel execution into serial execution.

Feature Overview

In older MySQL versions, developers would use SELECT ... FOR UPDATE to acquire an exclusive lock, causing other clients to wait until the lock is released or until innodb_lock_wait_timeout expires, at which point an exception is thrown. MySQL 8.0.1 extends SELECT ... FOR UPDATE with two new modifiers: NOWAIT and SKIP LOCKED .

NOWAIT behaves as its name suggests: when a lock cannot be obtained, MySQL immediately raises an exception instead of entering a lock‑wait state, reducing connection pressure and avoiding large lock‑structure buildup.

SKIP LOCKED also skips the locked row and continues searching for the next matching row, returning an empty result set for the locked row without causing a lock‑wait.

Implementation Overview

The locking behavior is managed by a new select_mode enumeration. The relevant excerpt from the source code is shown below.

enum select_mode {
    SELECT_ORDINARY,    /* default behaviour */
    SELECT_SKIP_LOCKED, /* skip the row if row is locked */
    SELECT_NOWAIT       /* return immediately if row is locked */
};
...
/* Set select mode for SKIP LOCKED / NOWAIT */
if (lock_type != TL_IGNORE) {
    switch (table->pos_in_table_list->lock_descriptor().action) {
        case THR_SKIP:
            m_prebuilt->select_mode = SELECT_SKIP_LOCKED;
            break;
        case THR_NOWAIT:
            m_prebuilt->select_mode = SELECT_NOWAIT;
            break;
        default:
            m_prebuilt->select_mode = SELECT_ORDINARY;
            break;
    }
}

When a lock‑wait situation occurs, the following fragment from row_search_mvcc() shows how the engine branches based on the error code.

...
    switch (err) {
        case DB_SUCCESS_LOCKED_REC:
            err = DB_SUCCESS;
        case DB_SUCCESS:
            break;
        case DB_SKIP_LOCKED:
        case DB_LOCK_NOWAIT:
            ut_ad(0);
            goto next_rec;
        default:
            goto lock_wait_or_error;
    }
...

It is evident that when SKIP_LOCKED or LOCK_NOWAIT is encountered, execution jumps to next_rec instead of the lock‑wait/error path, allowing the engine to continue scanning.

Usage Restrictions

Transactions must be started explicitly with AUTOCOMMIT = OFF .

When binlog_format is set to STATEMENT , using SKIP or NOWAIT can cause safety issues; ROW format is required.

Using SKIP LOCKED may violate data consistency; it should only be employed to avoid lock‑wait situations, not as a general data‑retrieval method.

Fictional Case Study

The following example creates a simple mooncake table and inserts several rows representing different products. Four client sessions are started to simulate concurrent attempts to purchase the product "五仁" (five‑nut mooncake).

CREATE TABLE `mooncake` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(16) NOT NULL,
  `sold` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `mooncake` VALUES
(1,'五仁',0),(2,'五仁',0),(3,'蛋黄',0),(4,'蛋黄',0),
(5,'蛋黄',0),(6,'莲蓉',0),(7,'火腿',0),(8,'玫瑰',0);

When all four clients issue a query with SELECT ... FOR UPDATE SKIP LOCKED on the "五仁" rows, only one client receives the row while the others get an empty set, as shown in the screenshots. After the winning client updates the sold column, subsequent attempts allow other clients to acquire the remaining rows.

Using NOWAIT instead results in an immediate exception for the clients that cannot obtain the lock, without entering a lock‑wait state.

Conclusion

The introduction of NOWAIT and SKIP LOCKED does not eliminate the need for lock‑wait entirely, but it gives developers finer control over how to handle lock contention. Depending on business requirements, one can choose to wait, skip locked rows, or raise an exception, thereby improving application responsiveness and reducing unnecessary connection pressure.