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.
For the full SELECT
statement syntax documentation, see Selection Queries.
Parameters
Parameter | Description |
---|---|
FOR SHARE |
New in v23.2: Acquire a shared lock on the rows returned by the SELECT statement. Shared locks are not enabled by default for SERIALIZABLE transactions. For details, see FOR SHARE usage. |
FOR UPDATE |
Acquire an exclusive lock on the rows returned by the SELECT statement. For details, see FOR UPDATE usage. |
Under SERIALIZABLE
isolation:
Shared locks are not enabled by default. To enable shared locks for
SERIALIZABLE
transactions, configure theenable_shared_locking_for_serializable
session setting. To perform foreign key checks underSERIALIZABLE
isolation with shared locks, configure theenable_implicit_fk_locking_for_serializable
session setting. This matches the defaultREAD COMMITTED
behavior.If the
optimizer_use_lock_op_for_serializable
session setting is enabled, the cost-based optimizer uses aLock
operator to construct query plans forSELECT
statements using theFOR UPDATE
andFOR 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, useSELECT ... 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 thenUPDATE
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.
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 the enable_implicit_select_for_update
session variable to false
.
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
New in v23.2:
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.
Shared locks are not enabled by default for SERIALIZABLE
transactions. 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.
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 therestart 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
.
This limitation does not apply to READ COMMITTED
transactions.
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.
Parameter | Description |
---|---|
SKIP LOCKED |
Skip rows that cannot be immediately locked. |
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.
Aliases
FOR KEY SHARE
is an alias forFOR SHARE
.FOR NO KEY UPDATE
is an alias forFOR 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.