Lock Rows for Update With SELECT FOR UPDATE

Acquire a row-level write lock inside a transaction so no other session can modify those rows before your update completes.

4/4 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Reserve available inventory within a transaction

The SELECT FOR UPDATE locks row 1 exclusively. Any other transaction trying to read the same row FOR UPDATE (or to UPDATE it) will block until this transaction commits. After the UPDATE, qty drops from 5 to 4. The COMMIT releases the lock. Without the lock, two concurrent sessions could both read qty=5, both decrement to 4, and write 4 — losing one decrement.

MySQL MariaDB PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE inventory (id INT PRIMARY KEY, item VARCHAR(50), qty INT);

INSERT INTO
  inventory
VALUES
  (1, 'Widget', 5),
  (2, 'Gadget', 0);

Validated query

SQL

BEGIN;

SELECT
  id,
  item,
  qty
FROM
  inventory
WHERE
  id = 1 FOR
UPDATE;

UPDATE inventory
SET
  qty = qty - 1
WHERE
  id = 1;

COMMIT;

SELECT
  id,
  item,
  qty
FROM
  inventory
ORDER BY
  id;

Expected result

id	item	qty
1	Widget	4
2	Gadget	0

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE inventory (id INT PRIMARY KEY, item VARCHAR(50), qty INT);

INSERT INTO
  inventory
VALUES
  (1, 'Widget', 5),
  (2, 'Gadget', 0);

Validated query

SQL

BEGIN TRANSACTION;

SELECT
  id,
  item,
  qty
FROM
  inventory
WITH
  (UPDLOCK, ROWLOCK)
WHERE
  id = 1;

UPDATE inventory
SET
  qty = qty - 1
WHERE
  id = 1;

COMMIT;

SELECT
  id,
  item,
  qty
FROM
  inventory
ORDER BY
  id;

Expected result

id	item	qty
1	Widget	4
2	Gadget	0

SQL Server uses WITH (UPDLOCK, ROWLOCK) instead of FOR UPDATE. SQLite does not support row-level locking. Final inventory state is identical across supported engines.

Useful when

Where this command helps.

reading an account balance and then debiting it without a race condition from a concurrent session
claiming a task from a work queue so no other worker processes the same row

Explanation

What the command is doing.

SELECT … FOR UPDATE reads rows and immediately locks them for the duration of the current transaction. Other transactions that try to update or lock the same rows will block until your transaction commits or rolls back. This prevents the lost-update anomaly when two sessions read the same row and both try to write back a modified version. MySQL, MariaDB, and PostgreSQL support FOR UPDATE directly. SQL Server uses the WITH (UPDLOCK) table hint inside the SELECT, or relies on a higher isolation level like SERIALIZABLE. SQLite uses a single-writer model — it locks the entire database file on the first write in a transaction, making row-level locking unnecessary.

Lock Rows for Update With SELECT FOR UPDATE

Reserve available inventory within a transaction

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.