FOR UPDATE and FOR SHARE

On this page

SELECT ... FOR UPDATE and SELECT ... FOR SHARE are used to issue locking reads at different lock strengths.

Syntax

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

Parameters

Under SERIALIZABLE isolation:

• Shared locks are not enabled by default. To enable shared locks for SERIALIZABLE transactions, configure the enable_shared_locking_for_serializable session setting. To perform foreign key checks under SERIALIZABLE isolation with shared locks, configure the enable_implicit_fk_locking_for_serializable session setting. This matches the default READ COMMITTED behavior.

• If the optimizer_use_lock_op_for_serializable session setting is enabled, the cost-based optimizer uses a Lock operator to construct query plans for SELECT statements using the FOR UPDATE and FOR SHARE clauses. This more closely matches the PostgreSQL behavior, but will create more round trips from gateway node to replica leaseholder in some cases.

Lock strengths

Lock "strength" determines how restrictive the lock is to concurrent transactions attempting to access the same row.

• SELECT FOR UPDATE obtains an exclusive lock on each qualifying row, blocking concurrent writes and locking reads on the row. Only one transaction can hold an exclusive lock on a row at a time, and only the transaction holding the exclusive lock can write to the row.

• SELECT FOR SHARE obtains a shared lock on each qualifying row, blocking concurrent writes and exclusive locking reads on the row. Multiple transactions can hold a shared lock on a row at the same time. When multiple transactions hold a shared lock on a row, none can write to the row. A shared lock grants transactions mutual read-only access to a row, and ensures that they read the latest version of the row.

When a SELECT FOR UPDATE or SELECT FOR SHARE read is issued on a row, only the latest version of the row is returned to the client. Under READ COMMITTED isolation, neither statement will block concurrent, non-locking reads.

Note that CockroachDB ensures serializability when using SERIALIZABLE isolation, regardless of the specified lock strength.

FOR UPDATE usage

SELECT ... FOR UPDATE can be used to:

• Strengthen the isolation of a READ COMMITTED transaction. If you need to read and later update a row within a transaction, use SELECT ... FOR UPDATE to acquire an exclusive lock on the row. This guarantees data integrity between the transaction's read and write operations. For details, see Locking reads.

• Order SERIALIZABLE transactions by controlling concurrent access to one or more rows of a table. These other transactions are placed into a queue based on when they tried to read the values 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, SELECT ... FOR UPDATE also prevents transaction retries that would otherwise occur due to contention.

  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. 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.

For a demo on SELECT FOR UPDATE and how it - alongside SERIALISABLE ISOLATION - can protect you against the ACID Rain attack, watch the following video:

FOR SHARE usage

SELECT ... FOR SHARE is primarily used with READ COMMITTED transactions.

If you need to read the latest version of a row, but not update the row, use SELECT ... FOR SHARE to block all concurrent writes on the row without unnecessarily blocking concurrent reads. This allows an application to build cross-row consistency constraints by ensuring that rows that are read in a READ COMMITTED transaction will not change before the writes in the same transaction have been committed. For details, see Locking reads.

Under READ COMMITTED isolation, CockroachDB uses the SELECT ... FOR SHARE locking mechanism to perform foreign key checks.

Lock promotion

A shared lock can be "promoted" to an exclusive lock.

If a transaction that holds a shared lock on a row subsqeuently issues an exclusive lock on the row, this will cause the transaction to reacquire the lock, effectively "promoting" the shared lock to an exclusive lock.

A shared lock cannot be promoted until all other shared locks on the row are released. If two concurrent transactions attempt to promote their shared locks on a row, this will cause deadlock between the transactions, causing one transaction to abort with a 40001 error (ABORT_REASON_ABORTED_RECORD_FOUND or ABORT_REASON_PUSHER_ABORTED) returned to the client. The remaining open transaction will then promote its lock.

Lock behavior under SERIALIZABLE isolation

By default under SERIALIZABLE isolation, locks acquired using SELECT ... FOR UPDATE and SELECT ... FOR SHARE are implemented as fast, in-memory unreplicated locks. If a lease transfer or range split/merge occurs on a range held by an unreplicated lock, the lock is dropped. The following behaviors can occur:

• The desired ordering of concurrent accesses to one or more rows of a table expressed by your use of SELECT ... FOR UPDATE may not be preserved (that is, a transaction B against some table T that was supposed to wait behind another transaction A operating on T may not wait for transaction A).
• The transaction that acquired the (now dropped) unreplicated lock may fail to commit, leading to transaction retry errors with code 40001 and the restart transaction error message.

When running under SERIALIZABLE isolation, SELECT ... FOR UPDATE and SELECT ... FOR SHARE locks should be thought of as best-effort, and should not be relied upon for correctness. Note that serialization is preserved despite this limitation. This limitation is fixed when the enable_durable_locking_for_serializable session setting is set to true.

Wait policies

Wait policies determine how a SELECT ... FOR UPDATE or SELECT ... FOR SHARE statement handles conflicts with locks held by other active transactions. By default, locking reads that are blocked by an active transaction must wait for the transaction to finish.

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.

Aliases

• FOR KEY SHARE is an alias for FOR SHARE.
• FOR NO KEY UPDATE is an alias for FOR UPDATE.

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):

cockroach sql --insecure

Next, create a table and insert some rows:

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:

BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;

Press Enter twice in the SQL client to send the statements 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):

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:

BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;

Press Enter twice to send the statements 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:

UPDATE kv SET v = v + 5 WHERE k = 1;

UPDATE 1

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:

UPDATE kv SET v = v + 5 WHERE k = 1;

UPDATE 1

Finally, commit the transaction in Terminal 2:

COMMIT;

COMMIT

Reserve rows for updates using exclusive locks

See Read Committed Transactions.

Reserve row values using shared locks

See Read Committed Transactions.

See also

• SELECT
• Selection Queries
• Transaction Contention
• Read Committed Transactions

Was this helpful?