Concurrency control

Concurrency control in databases ensures that multiple transactions can execute concurrently while preserving data integrity. Concurrency control is essential for correctness in environments where two or more transactions can access the same data at the same time.

YugabyteDB provides two policies to handle conflicts between concurrent transactions as described in the following sections.

For information on how row-level explicit locking clauses behave with these concurrency control policies, refer to Row-level explicit locking clauses.

Fail-on-Conflict

This is the default concurrency control strategy and is applicable for Repeatable Read and Serializable isolation levels. It is not applicable for Read Committed isolation.

In this mode, transactions are assigned random priorities with some exceptions as described in Transaction Priorities. If a conflict occurs, a transaction with the lower priority is aborted. There are two possibilities when a transaction T1 tries to read, write, or lock a row in a mode conflicting with other concurrent transactions:

• Wound: If T1 has a higher priority than all the other conflicting transactions, T1 will abort them and make progress.
• Die: If any other conflicting transaction has an equal or higher priority than T1, T1 will abort itself.

Suppose you have a table with some data in it. The following examples describe the wound and die approaches when a conflict occurs.

create table test (k int primary key, v int);
insert into test values (1, 1);

Wound example

SET yb_transaction_priority_upper_bound = 0.4;

SET yb_transaction_priority_lower_bound = 0.6;

begin transaction isolation level repeatable read;

select * from test where k=1 for update;

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

begin transaction isolation level repeatable read;

select * from test where k=1 for update;

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

select * from test;

ERROR:  Operation expired: Heartbeat:
Transaction 13fb5a0a-012d-4821-ae1d-5f7780636dd4 expired
or aborted by a conflict: 40001

rollback;

commit;

Die example

SET yb_transaction_priority_lower_bound = 0.6;

SET yb_transaction_priority_upper_bound = 0.4;

begin transaction isolation level repeatable read;

select * from test where k=1 for update;

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

begin transaction isolation level repeatable read;

select * from test where k=1 for update;

ERROR:  All transparent retries exhausted. could not serialize
access due to concurrent update

rollback;

commit;

Best-effort internal retries for first statement in a transaction

Note that we see the error message All transparent retries exhausted in the preceding example because if the transaction T1, when executing the first statement, finds another concurrent conflicting transaction with equal or higher priority, then T1 will perform a few retries with exponential backoff before giving up in anticipation that the other transaction will be done in some time. The number of retries are configurable by the ysql_max_write_restart_attempts YB-TServer gflag and the exponential backoff parameters are the same as the ones described in Performance tuning.

Each retry will use a newer snapshot of the database in anticipation that the conflicts might not occur. This is done because if the read time of the new snapshot is higher than the commit time of the earlier conflicting transaction T2, the conflicts with T2 would essentially be voided since T1 and T2 would no longer be "concurrent".

Note that the retries will not be performed in case the amount of data to be sent from YSQL to the client proxy exceeds the TServer gflag ysql_output_buffer_size.

Wait-on-Conflict [Beta]

This mode of concurrency control is applicable only for YSQL and provides the same semantics as PostgreSQL.

In this mode, transactions are not assigned priorities. If a conflict occurs when a transaction T1 tries to read, write, or lock a row in a conflicting mode with a few other concurrent transactions, T1 will wait until all conflicting transactions finish by either committing or rolling back. Once all conflicting transactions have finished, T1 will:

1. make progress if the conflicting transactions didn’t commit any permanent modifications that conflict with T1.
2. abort otherwise.

Wait-on-Conflict behavior can be enabled by setting the YB-TServer gflag enable_wait_queues=true, which will enable use of in-memory wait queues that provide waiting semantics when conflicts are detected between transactions. A rolling restart is needed for the gflag to take effect. Without this flag set, transactions operate in the priority based Fail-on-Conflict mode by default.

Since T1 can make progress only if the conflicting transactions didn’t commit any conflicting permanent modifications, there are some intricacies in the behaviour. The list of exhaustive cases possible are detailed below in the Examples section.

Examples

The following examples describe different use cases detailing the Wait-on-Conflict behavior.

1. Note that the examples require you to set the YB-TServer gflag enable_wait_queues=true.
2. Also, set the YB-TServer gflag ysql_max_write_restart_attempts=0 to disable internal query layer retries on conflict. This is only done to easily illustrate the Wait-on-Conflict concurrency control semantics separately without query layer retries. It is not recommended to disable these retries in production.

A restart is necessary for these flags to take effect.

Start by setting up the table you'll use in all of the examples in this section.

create table test (k int primary key, v int);
insert into test values (1, 1);
insert into test values (2, 2);

Conflict between two explicit row-level locks

begin transaction isolation level repeatable read;

begin transaction isolation level repeatable read;

select * from test where k=1 for update;

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

select * from test where k=1 for update;

(waits)

commit;

rollback;

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

commit;

Explicit row-level lock followed by a conflicting write

begin transaction isolation level repeatable read;

begin transaction isolation level repeatable read;

select * from test where k=1 for share;

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

update test set v=1 where k=1;

(waits)

commit;

rollback;

UPDATE 1

commit;

Write followed by a conflicting explicit row-level lock

begin transaction isolation level repeatable read;

begin transaction isolation level repeatable read;

update test set v=1 where k=1;

UPDATE 1

select * from test where k=1 for share;

(waits)

rollback;

commit;

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

commit;

ERROR:  All transparent retries exhausted. could not serialize
access due to concurrent update

rollback;

Write followed by a conflicting write

begin transaction isolation level repeatable read;

begin transaction isolation level repeatable read;

update test set v=1 where k=1;

UPDATE 1

update test set v=1 where k=1;

(waits)

rollback;

commit;

UPDATE 1

commit;

ERROR:  All transparent retries exhausted. Operation failed.
Try again: Value write after transaction start: { days: 19299
time: 17:07:42.577762 } >= { days: 19299 time: 17:07:40.561842 }:
kConflict

rollback;

Wait queue jumping is allowed

A transaction can jump the queue even if it does conflict with waiting transactions but doesn’t conflict with any active transactions.

begin transaction isolation level repeatable read;

begin transaction isolation level repeatable read;

begin transaction isolation level repeatable read;

select * from test where k=1 for share;

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

select * from test where k=1 for update;

(waits for T1 to end...)

select * from test where k=1 for share;

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

commit;

commit;

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

commit;

Rollback of sub-transaction with conflicting write

Suppose a transaction T1 is blocked on some operation of another transaction T2. If that blocking operation was part of a subtransaction which is later rolled back, then T1 may proceed:

begin transaction isolation level repeatable read;

begin transaction isolation level repeatable read;

savepoint a;

update test set v=1 where k=1;

UPDATE 1

update test set v=1 where k=1;

(waits)

rollback to savepoint a;

UPDATE 1

commit;

commit;

Distributed deadlock detection

In the Wait-on-Conflict mode, transactions can wait for each other and result in a deadlock. Setting the YB-TServer gflag enable_deadlock_detection=true runs a distributed deadlock detection algorithm in the background to detect and break deadlocks. It is always recommended to keep deadlock detection on when enable_wait_queues=true, unless it is absolutely certain that the application or workload behavior makes deadlocks impossible. A rolling restart is required for the change to take effect.

Add enable_deadlock_detection=true to the list of TServer gflags and restart the cluster.

begin transaction isolation level repeatable read;

begin transaction isolation level repeatable read;

update test set v=2 where k=1;

UPDATE 1

update test set v=4 where k=2;

UPDATE 1

update test set v=6 where k=2;

(waits)

update test set v=6 where k=1;

ERROR:  Internal error: Transaction 00da00cd-87fa-431b-9521-253582fb23fe
was aborted while waiting for locks

commit;

Metrics

All metrics are per tablet.

Histograms:

1. wait_queue_pending_time_waiting (ms): the amount of time a still-waiting transaction has been in the wait queue
2. wait_queue_finished_waiting_latency (ms): the amount of time an unblocked transaction spent in the wait queue
3. wait_queue_blockers_per_waiter: the number of blockers a waiter is stuck on in the wait queue

Counters:

1. wait_queue_waiters_per_blocker: the number of waiters stuck on a particular blocker in the wait queue
2. wait_queue_num_waiters: the number of waiters stuck on a blocker in the wait queue
3. wait_queue_num_blockers: the number of unique blockers tracked in a wait queue

Limitations

Refer to #5680 for limitations.

Row-level explicit locking clauses

The NOWAIT clause for row-level explicit locking doesn't apply to the Fail-on-Conflict mode as there is no waiting. It does apply to the Wait-on-Conflict policy but is currently supported only for Read Committed isolation. #12166 will extend support for this in the Wait-on-Conflict mode for the other isolation levels.

The SKIP LOCKED clause is supported in both concurrency control policies and provides a transaction with the capability to skip locking without any error when a conflict is detected. However, it isn't supported for Serializable isolation. #11761 tracks support for SKIP LOCKED in Serializable isolation.