Explicit locking

• YSQL

Explicit locking in YugabyteDB gives you granular control over concurrency and data integrity within your transactions. While YugabyteDB automatically handles the ACID properties through its underlying distributed transaction protocol, explicit locking allows you to prevent conflicts that might otherwise occur, or to implement custom concurrency patterns that extend beyond the default isolation levels.

By using explicit locks, you can guarantee exclusive or shared access to specific data, ensuring that subsequent transactions are appropriately blocked or permitted. This is particularly useful for complex business logic, long-running transactions, or scenarios where you require custom serializability guarantees.

Row-level locks

YugabyteDB's YSQL supports explicit row-level locking, similar to PostgreSQL. Explicit row-locks ensure that two transactions can never hold conflicting locks on the same row. When two transactions try to acquire conflicting lock modes, the semantics are dictated by YugabyteDB's concurrency control policies.

The following types of row locks are supported:

• FOR UPDATE
• FOR NO KEY UPDATE
• FOR SHARE
• FOR KEY SHARE

The following example uses the FOR UPDATE row lock with the fail-on-conflict concurrency control policy. First, a row is selected for update, thereby locking it, and subsequently updated. A concurrent transaction should not be able to abort this transaction by updating the value of that row after the row is locked.

Create a sample table and populate it with sample data, as follows:

yugabyte=# CREATE TABLE t (k VARCHAR, v VARCHAR);
yugabyte=# INSERT INTO t VALUES ('k1', 'v1');

Next, connect to the universe using two independent ysqlsh instances. You can connect both session ysqlsh instances to the same server or to different servers.

Begin a transaction in the first session and perform a SELECT FOR UPDATE on the row in the table t. This locks the row for an update as a part of a transaction that has a very high priority (that is, in the high priority bucket, as explained in Transaction priorities):

yugabyte=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN

yugabyte=# SELECT * from t WHERE k='k1' FOR UPDATE;

 k  | v
----+----
 k1 | v1
(1 row)

Before completing the transaction, try to update the same key in your other session using a basic update statement, as follows:

yugabyte=# UPDATE t SET v='v1.1' WHERE k='k1';

ERROR:  All transparent retries exhausted. Operation failed. Try again: bb3aace4-5de2-41f9-981e-d9ca06671419 Conflicts with higher priority transaction: d4dadbf8-ca81-4bbd-b68c-067023f8ee6b

This operation fails because it conflicts with the row-level lock and as per Fail-on-Conflict concurrency control policy, the transaction aborts itself because it has a lower priority.

Note that the error message appears after all best-effort statement retries have been exhausted.

Finally, in the first session, update the row and commit the transaction, as follows:

yugabyte=# UPDATE t SET v='v1.2' WHERE k='k1';

UPDATE 1

yugabyte=# COMMIT;

COMMIT

This should succeed.

Explicit row locking modes

YSQL supports PostgreSQL's explicit locking clause that provides advanced control over lock acquisition behavior when conflicts occur.

By default, the system waits to acquire locks when competing transactions hold the lock. You can modify this behavior using the NOWAIT and the SKIP LOCKED clauses. These locks can only be applied to row-level locks. They are not applicable to table locks.

NOWAIT clause

The NOWAIT clause causes a SELECT FOR UPDATE/SHARE to fail immediately if a row is locked, rather than waiting or aborting.

Example:

SELECT * FROM account WHERE id = 100 FOR UPDATE NOWAIT;

For support details and limitations, see Row-level explicit locking clauses.

SKIP LOCKED clause

The SKIP LOCKED clause allows a SELECT FOR UPDATE/SHARE to skip rows that are locked, returning only the unlocked rows. This is useful for applications that can process any available rows.

Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table.

Example:

SELECT * FROM orders WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 10;

For support details, limitations, and performance tuning options, see Row-level explicit locking clauses and Explicit row locking flags.

Advisory locks

YSQL also supports advisory locks, where the application manages concurrent access to resources through a cooperative locking mechanism. Advisory locks can be less resource-intensive than table or row locks for certain use cases because they don't involve scanning tables or indexes for lock conflicts. They are session-specific and managed by the client application.

In PostgreSQL, if an advisory lock is taken on one session, all sessions should be able to see the advisory locks acquired by any other session. Similarly, in YugabyteDB, if an advisory lock is acquired on one session, all the sessions should be able to see the advisory locks regardless of the node the session is connected to. This is achieved via the pg_advisory_locks system table, which is dedicated to hosting advisory locks. All advisory lock requests are stored in this system table.

Advisory locks are available in v2025.1 or later, and enabled by default. You can configure advisory locks using the Advisory lock flags.

For more information on using the locks, refer to Advisory locks.

Table-level locks

EA YSQL supports table-level locks (also known as object locks) to coordinate between DML and DDL operations. This ensures that DDLs wait for in-progress DMLs to finish before making schema changes, and gates new DMLs behind any waiting DDLs, providing concurrency handling that closely matches PostgreSQL behavior.

Table-level locks depend on:

• Transactional DDL, controlled by ysql_yb_ddl_transaction_block_enabled.
• YSQL lease, lease period controlled by master_ysql_operation_lease_ttl_ms.
• Per-database catalog caching, controlled by ysql_enable_db_catalog_version_mode.

Table-level locking provides serializable semantics between DMLs and DDLs for YSQL by introducing distributed locks on YSQL objects. PostgreSQL clients acquire locks to prevent DMLs and DDLs from running concurrently. DML locks are acquired only on the TServer hosting the PostgreSQL session, but DDL locks are acquired on every TServer in the universe to ensure no DML touching the locked object runs while the DDL does.

To prevent dead TServers holding locks from permanently blocking subsequent DMLs or DDLs, YugabyteDB internally uses the YSQL lease mechanism between TServers and the Master leader to serve any YSQL DMLs. All locks held by a TServer are released when its lease expires.

Enable table-level locks

Table-level locks are disabled by default. To enable the feature, set the yb-tserver flag enable_object_locking_for_table_locks to true.

Because enable_object_locking_for_table_locks is a preview flag, to use it, add the flag to the allowed_preview_flags_csv list (that is, allowed_preview_flags_csv=enable_object_locking_for_table_locks).

As the table-level locks feature depends on Transactional DDL (currently not enabled by default), you need to enable the flag ysql_yb_ddl_transaction_block_enabled.

For more information on the lock scopes and lifecycle, see Table-level locks.