select-for-update-limitations.md

Locks acquired using {% if page.name == "select-for-update.md" %} SELECT ... FOR UPDATE {% else %} [SELECT ... FOR UPDATE]({% link {{ page.version.version }}/select-for-update.md %}) {% endif %} are dropped on [lease transfers]({% link {{ page.version.version }}/architecture/replication-layer.md %}#epoch-based-leases-table-data) and [range splits and merges]({% link {{ page.version.version }}/architecture/distribution-layer.md %}#range-merges). 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 unreplicated locks.

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 [transaction retry errors with code 40001 and the restart transaction error message]({% link {{ page.version.version }}/common-errors.md %}#restart-transaction).

We intend to improve the reliability of these locks. For details, see cockroachdb/cockroach#75456.

Note that [serializable isolation]({% link {{ page.version.version }}/transactions.md %}#serializable-isolation) is preserved despite this limitation.