[YSQL] Integrate READ COMMITTED isolation with wait queue based Wait-on-Conflict concurrency control to detect deadlocks and for higher performance

[pkj415]

Jira Link: DB-2879

Description

Currently READ COMMITTED isolation achieves Wait-on-Conflict concurrency control semantics by retrying
conflicting statements internally with exponential backoff. Savepoints are leveraged to
clean-up any partial work done by the statement before retrying.

There is no deadlock detection currently when two transactions like below are deadlocked:

Txn1 Txn2
update ... k=1;
update ... k=2;
update ... k=2;
update ... k=1;

The latter two UPDATEs in both transactions will retry indefinitely since the transactions
are deadlocked.

Once we integrate the READ COMMITTED isolation level with the wait-queue based
Wait-on-Conflict concurrency control in #5680, deadlock detection will be performed as part of the wait
queues.

Details -

READ COMMITTED isolation follows Wait-on-Conflict concurrency control semantics by retrying a
query indefinitely on kConflict errors with exponential backoff. Once all
conflicting transactions end, the next retry of the query will successfully run.
The exponential backoff delay between retries is to ensure that the backend
doesn't overwhelm the system by retrying in a tight loop.

D17304 (dc81106) added the new wait queue based
implementation for Wait-on-Conflict concurrency control which can be used if the tserver gflag
enable_wait_queues is set to true. In this case, a read/ write rpc from
a YSQL backend to the transaction participant is blocked if there are conflicts
detected on the participant and unblocked once all conflicting transactions have
completed (either committed or aborted). There are 2 scenarios possible once
the rpc is unblocked -

(1) It is still conflicting because some transaction has committed which made a
conflicting modification to the data.
(2) No transaction with a conflicting modification to the data has committed.

For case (1), the kConflict error is returned to the YSQL backend. In (2), the
rpc makes progress and returns the appropriate result to the YQSL backend. The
waiting is transparent to the YSQL backend (i.e., not differentiable from a rpc
which wasn't blocked).

So, if enable_wait_queues is set, there is no need for a
READ COMMITTED transaction to sleep before retrying the query (because a
kConflict error, if at all [with case (1)] is sent only after all conflicting
transactions have ended).

[polarweasel]

Before you close this one @pkj415 please make sure to remove the note in the docs on the Read Committed architecture page.