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

# SELECT FOR UPDATE

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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

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][selection], 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](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, CockroachDB also prevents [transaction retries][retries] that would otherwise occur due to <InternalLink path="performance-best-practices-overview#transaction-contention">contention</InternalLink>.

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 <InternalLink path="transaction-retry-error-reference">serialization errors</InternalLink>, which use the `SQLSTATE` error code `40001`, and emit error messages with the string `restart transaction`. These errors can also arise due to <InternalLink path="architecture/transaction-layer#transaction-conflicts">time uncertainty</InternalLink>. To eliminate the need for application-level retry logic, in addition to `SELECT FOR UPDATE` your application also needs to use a <InternalLink path="transaction-retry-error-reference#client-side-retry-handling">driver that implements automatic retry handling</InternalLink>.

CockroachDB does not support the `FOR SHARE` or `FOR KEY SHARE` <InternalLink path="select-for-update#locking-strengths">locking strengths</InternalLink>.

<Note>
  By default, CockroachDB uses the `SELECT FOR UPDATE` locking mechanism during the initial row scan performed in <InternalLink path="update">`UPDATE`</InternalLink> and <InternalLink path="upsert">`UPSERT`</InternalLink> statement execution. To turn off implicit `SELECT FOR UPDATE` locking for `UPDATE` and `UPSERT` statements, set the `enable_implicit_select_for_update` <InternalLink path="set-vars">session variable</InternalLink> to `false`.
</Note>

[retries]: transactions.html#transaction-retries

[selection]: selection-queries.html

## Syntax

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

<img src="https://mintcdn.com/cockroachlabs/ulDoMGhKYg9RNabd/images/sql-diagrams/v23.1/for_locking.svg?fit=max&auto=format&n=ulDoMGhKYg9RNabd&q=85&s=fc9a8ea0395633cf4dc890403d065c0c" alt="for_locking syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="601" height="255" data-path="images/sql-diagrams/v23.1/for_locking.svg" />

For the full `SELECT` statement syntax documentation, see <InternalLink path="selection-queries">Selection Queries</InternalLink>.

## 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.<br /><br />Note that CockroachDB always <InternalLink path="demo-serializable">ensures serializability</InternalLink>, regardless of the specified locking strength. |
| `FOR UPDATE`/`FOR NO KEY UPDATE` | Lock the rows returned by the <InternalLink path="selection-queries">`SELECT`</InternalLink> statement, such that other transactions trying to access the rows must wait for the transaction to finish.<br /><br />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` | 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 <InternalLink path="selection-queries">Selection Queries</InternalLink>.

## Required privileges

The user must have the `SELECT` and `UPDATE` <InternalLink path="security-reference/authorization#managing-privileges">privileges</InternalLink> on the tables used as operands.

## Known limitations

Locks acquired using  `SELECT ... FOR UPDATE`  <InternalLink path="select-for-update">`SELECT ... FOR UPDATE`</InternalLink>  are dropped on <InternalLink path="architecture/replication-layer#epoch-based-leases-table-data">lease transfers</InternalLink> and <InternalLink path="architecture/distribution-layer#range-merges">range splits and merges</InternalLink>. `SELECT ... FOR UPDATE` locks should be thought of as best-effort, and should not be relied upon for correctness, as they are implemented as fast, in-memory <InternalLink path="architecture/transaction-layer">unreplicated locks</InternalLink>.

If a lease transfer or range split/merge occurs on a range held by an unreplicated lock, the lock is dropped, and 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 <InternalLink path="common-errors#restart-transaction">transaction retry errors with code `40001` and the `restart transaction` error message</InternalLink>.

Note that <InternalLink path="transactions#serializable-isolation">serializable isolation</InternalLink> is preserved despite this limitation.

## 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 <InternalLink path="start-a-local-cluster">local unsecured cluster</InternalLink>.

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 <InternalLink path="transactions">transaction</InternalLink> 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 <InternalLink path="cockroach-sql">SQL client</InternalLink> 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
```

## See also

* <InternalLink path="select-clause">`SELECT`</InternalLink>
* <InternalLink path="selection-queries">Selection Queries</InternalLink>
* [Transaction Contention][transaction_contention]

[transaction_contention]: performance-best-practices-overview.html#transaction-contention

[retries]: transaction-retry-error-reference.html#client-side-retry-handling

[select]: /docs/v23.1/select-clause