> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# FOR UPDATE and FOR SHARE
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
`SELECT ... FOR UPDATE` and `SELECT ... FOR SHARE` are used to issue locking reads at different [lock strengths](#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` | 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-share-usage). |
| `FOR UPDATE` | Acquire an exclusive lock on the rows returned by the `SELECT` statement. For details, see [`FOR UPDATE` usage](#for-update-usage). |
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. For an example, see [Reserve rows for updates using exclusive locks](#reserve-rows-for-updates-using-exclusive-locks).
* `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. For an example, see [Reserve values using shared locks](#reserve-row-values-using-shared-locks).
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``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` exclusively locks the rows returned by a [selection query][selection], such that other transactions trying to access those rows must wait for the transaction that locked the rows to commit or rollback.
`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](https://wikipedia.org/wiki/Thrashing_\(computer_science\)) 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][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`.
[retries]: transactions.html#transaction-retries
[selection]: selection-queries.html
For a demo on `SELECT FOR UPDATE` and how it - alongside SERIALISABLE ISOLATION - can protect you against the [ACID Rain attack](http://www.bailis.org/papers/acidrain-sigmod2017.pdf), 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.
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 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
* `SKIP LOCKED` cannot be used for tables with multiple column families.
* 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`. 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 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):
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cockroach sql --insecure
```
Next, create a table and insert some rows:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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):
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE kv SET v = v + 5 WHERE k = 1;
```
```
UPDATE 1
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPDATE kv SET v = v + 5 WHERE k = 1;
```
```
UPDATE 1
```
Finally, commit the transaction in Terminal 2:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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][transaction_contention]
* Read Committed Transactions
[transaction_contention]: performance-best-practices-overview.html#transaction-contention
[retries]: transaction-retry-error-reference.html#client-side-retry-handling
[select]: /docs/v25.3/select-clause
