Concurrent transactions can read the same row and then overwrite each other’s changes. MySQL 8.0 with InnoDB supports two common ways to prevent this: pessimistic locking and optimistic locking.

Pessimistic locking reserves the row before changing it. Optimistic locking allows concurrent work but rejects an update if the row changed after it was read.

Example table

Run this setup once before the examples. The table uses InnoDB, which provides transactional row-level locking.

DROP DATABASE IF EXISTS locking_demo;
CREATE DATABASE locking_demo;
USE locking_demo;

CREATE TABLE t_goods (
    id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    status TINYINT UNSIGNED NOT NULL,
    version BIGINT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

INSERT INTO t_goods (id, name, status, version)
VALUES (1, 'Example product', 1, 1);

Pessimistic locking

Use pessimistic locking when a transaction must prevent another transaction from changing a selected row until the current operation finishes.

SELECT ... FOR UPDATE takes an exclusive lock on the selected index record. The lock remains until COMMIT or ROLLBACK, so the statement must run inside an explicit transaction.

Open two MySQL 8.0 sessions connected to the same server. In session A, start a transaction, lock product 1, and update only that row:

USE locking_demo;

START TRANSACTION;

SELECT id, name, status, version
FROM t_goods
WHERE id = 1
FOR UPDATE;

UPDATE t_goods
SET status = 2
WHERE id = 1;

-- Leave the transaction open while session B runs.

While session A is still open, run this complete transaction in session B:

USE locking_demo;

START TRANSACTION;

SELECT id, name, status, version
FROM t_goods
WHERE id = 1
FOR UPDATE;

UPDATE t_goods
SET status = 3
WHERE id = 1;

COMMIT;

Session B waits at SELECT ... FOR UPDATE. Return to session A and release its lock:

USE locking_demo;
COMMIT;

Session B then continues. Its locking read returns the committed state from session A, its UPDATE changes the same row to status 3, and its COMMIT releases the lock.

Keep transactions short. Waiting transactions consume resources, and transactions that lock rows in inconsistent orders can deadlock. InnoDB detects deadlocks and rolls back one participant.

Indexes determine lock scope

InnoDB row locks are locks on index records. The primary-key predicate WHERE id = 1 uses a unique index lookup, so it targets one record.

A predicate without a usable index can require a full scan. A locking read may then lock every index record it scans, potentially making most or all rows unavailable to competing transactions.

That behavior is not an automatic conversion to a table lock. It is a large set of record locks caused by the scan. Verify access paths with EXPLAIN and index predicates used by locking queries.

Avoid waiting when appropriate

MySQL 8.0 supports NOWAIT and SKIP LOCKED on locking reads. NOWAIT returns an error immediately if a selected row is locked:

USE locking_demo;

START TRANSACTION;
SELECT id, name, status, version
FROM t_goods
WHERE id = 1
FOR UPDATE NOWAIT;
ROLLBACK;

SKIP LOCKED omits locked rows instead of waiting. It is useful for queue-like consumers, but it provides an intentionally inconsistent view and is unsuitable for general business reads.

USE locking_demo;

START TRANSACTION;
SELECT id, name, status, version
FROM t_goods
WHERE status = 1
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
ROLLBACK;

Optimistic locking

Optimistic locking does not hold a row lock while application code processes previously read data. Instead, a version column detects whether another transaction changed the row.

First read the row and retain its version:

USE locking_demo;

SELECT id, name, status, version
FROM t_goods
WHERE id = 1;

Suppose the application read version 1 and wants to set status to 2. The following MySQL 8.0 example uses a prepared statement, so each ? is a real bound parameter rather than framework-specific syntax:

USE locking_demo;

SET @new_status = 2;
SET @id = 1;
SET @expected_version = 1;

PREPARE optimistic_update FROM
    'UPDATE t_goods
     SET status = ?, version = version + 1
     WHERE id = ? AND version = ?';

EXECUTE optimistic_update
USING @new_status, @id, @expected_version;

SELECT ROW_COUNT() AS rows_updated;

DEALLOCATE PREPARE optimistic_update;

An affected-row count of 1 means the update succeeded and incremented the version atomically. A count of 0 means the row was deleted, or its version no longer matches because another writer updated it first.

On a version conflict, read the current row again, recompute the intended change, and retry with the new version. Use a bounded retry count and return a conflict if repeated contention makes progress unlikely.

Do not retry by blindly resubmitting stale values. The application must decide whether its original change is still valid against the latest state.

Which strategy to choose

Choose pessimistic locking when conflicts are frequent, the protected transaction is short, and subsequent work must depend on a stable row. Inventory allocation and queue claiming are common examples.

Choose optimistic locking when conflicts are uncommon or work occurs outside a database transaction. It avoids long-held locks, but callers must handle zero-row updates and retries correctly.

Both strategies still use InnoDB locks during the final UPDATE. The difference is whether contention is coordinated before the work with FOR UPDATE, or detected at write time with a version predicate.