SELECT FOR UPDATE

On this page Carat arrow pointing down
Warning:
CockroachDB v21.2 is no longer supported as of May 16, 2023. For more details, refer to the Release Support Policy.

The SELECT FOR UPDATE statement is used to order transactions by controlling concurrent access to one or more rows of a table.

It works by locking the rows returned by a selection query, such that other transactions trying to access those rows are forced to wait for the transaction that locked the rows to finish. These other transactions are effectively put into a queue based on when they tried to read the value of the locked rows.

Because this queueing happens during the read operation, the thrashing that would otherwise occur if multiple concurrently executing transactions attempt to SELECT the same data and then UPDATE the results of that selection is prevented. By preventing thrashing, CockroachDB also prevents transaction retries that would otherwise occur.

As a result, using SELECT FOR UPDATE leads to increased throughput and decreased tail latency for contended operations.

Note that using SELECT FOR UPDATE does not completely eliminate the chance of serialization errors, which use the SQLSTATE error code 40001, and emit error messages with the string restart transaction. These errors can also arise due to time uncertainty. To eliminate the need for application-level retry logic, in addition to SELECT FOR UPDATE your application also needs to use a driver that implements automatic retry handling.

CockroachDB currently does not support the FOR SHARE/FOR KEY SHARE locking strengths, or the SKIP LOCKED wait policy.

Note:

By default, CockroachDB uses the SELECT FOR UPDATE locking mechanism during the initial row scan performed in UPDATE and UPSERT statement execution. To turn off implicit SELECT FOR UPDATE locking for UPDATE and UPSERT statements, set enable_implicit_select_for_update to false.

Syntax

The following diagram shows the supported syntax for the optional FOR locking clause of a SELECT statement.

FOR NO KEY UPDATE KEY SHARE OF table_name , SKIP LOCKED NOWAIT

For the full SELECT statement syntax documentation, see Selection Queries.

Parameters

Locking strengths

Locking strength dictates the row-level locking behavior on rows retrieved by a SELECT statement.

Parameter Description
FOR SHARE/FOR KEY SHARE This syntax is a no-op, allowed for PostgreSQL compatibility. Specifying FOR SHARE/FOR KEY SHARE does not cause CockroachDB to use shared locks over the rows retrieved by a statement.

Note that CockroachDB always ensures serializability, regardless of the specified locking strength.
FOR UPDATE/FOR NO KEY UPDATE Lock the rows returned by the SELECT statement, such that other transactions trying to access the rows must wait for the transaction to finish.

Note that in CockroachDB, the FOR NO KEY UPDATE locking strength is identical to the FOR UPDATE locking strength.

Wait policies

Wait policies determine how a SELECT FOR UPDATE statement handles conflicts with locks held by other active transactions. By default, SELECT FOR UPDATE queries on rows that are already locked by an active transaction must wait for the transaction to finish.

Parameter Description
SKIP LOCKED This syntax is not supported, as skipping locked rows is not yet supported by CockroachDB.
NOWAIT Return an error if a row cannot be locked immediately.

For documentation on all other parameters of a SELECT statement, see Selection Queries.

Required privileges

The user must have the SELECT and UPDATE privileges on the tables used as operands.

Examples

Enforce transaction order when updating the same rows

This example uses SELECT FOR UPDATE to lock a row inside a transaction, forcing other transactions that want to update the same row to wait for the first transaction to complete. The other transactions that want to update the same row are effectively put into a queue based on when they first try to read the value of the row.

This example assumes you are running a local unsecured cluster.

First, connect to the running cluster (call this Terminal 1):

icon/buttons/copy
cockroach sql --insecure

Next, create a table and insert some rows:

icon/buttons/copy
CREATE TABLE kv (k INT PRIMARY KEY, v INT);
INSERT INTO kv (k, v) VALUES (1, 5), (2, 10), (3, 15);

Next, we'll start a transaction and lock the row we want to operate on:

icon/buttons/copy
BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;

Hit enter twice in the SQL client to send the input so far to be evaluated. This will result in the following output:

  k | v
+---+----+
  1 | 5
(1 row)

Now open another terminal and connect to the database from a second client (call this Terminal 2):

icon/buttons/copy
cockroach sql --insecure

From Terminal 2, start a transaction and try to lock the same row for updates that is already being accessed by the transaction we opened in Terminal 1:

icon/buttons/copy
BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;

Hit enter twice to send the input so far to be evaluated. Because Terminal 1 has already locked this row, the SELECT FOR UPDATE statement from Terminal 2 will appear to "wait".

Back in Terminal 1, update the row and commit the transaction:

icon/buttons/copy
UPDATE kv SET v = v + 5 WHERE k = 1;
UPDATE 1
icon/buttons/copy
COMMIT;
COMMIT

Now that the transaction in Terminal 1 has committed, the transaction in Terminal 2 will be "unblocked", generating the following output, which shows the value left by the transaction in Terminal 1:

  k | v
+---+----+
  1 | 10
(1 row)

The transaction in Terminal 2 can now receive input, so update the row in question again:

icon/buttons/copy
UPDATE kv SET v = v + 5 WHERE k = 1;
UPDATE 1

Finally, commit the transaction in Terminal 2:

icon/buttons/copy
COMMIT;
COMMIT

See also


Yes No
On this page

Yes No