[YSQL] TableLocks: Create and re-use docdb transactions for read-only transactions

[basavaraj29]

Jira Link: DB-13634

Description

In #23045, we introduced a tserver local object lock manager. The intention was to tag locks to SessionIDHostPair = std::pair<const uint64_t, const std::string> since not all ysql transaction have a distributed transaction on the docdb side (and hence no txn id tag the lock could be tagged against)

But we could end up with deadlocks spanning table/object locks and row-level locks which would go undetected if we have different deadlock detectors running for table locks and row locks. For example, consider the below

begin;
select * from table A;
                                begin;
                                alter table B add column….
                                alter table A add column….
select * from table B;

Note: As of today, each DDL operates under its own transaction. But there is ongoing work to support DDL+DML transactionality, when we could run into the above deadlock.

But starting a distributed txn for every ysql txn would introduce additional latencies for read-only txn, fast path writes etc.

To address this problem, we could start a distributed docdb transaction early (even for read-only txns), and keep re-using the transaction until a ysql transaction write comes in. The next subsequent statement would start another docdb transaction.

That way, we would have an identifier to tag object/table locks to. For instance, consider the below example

ysqlsh>              
begin;
select * from t ...; // create txn1. use <txn1, serial no 1> for object locks
update t set ...;    // use <txn1, serial no 1> for object locks & use txn1 for row-locks/intents
commit;              // txn1 commit
select * from t ...; // create txn2. use <txn2, serial no 2> for object locks
update ... where k=1 // single shard write. use <txn2, serial no 3> for object locks
begin;
select * from t ...; // use <txn2, serial no 4> for object locks
commit;              // no need to switch txns since the prior was a read-only
begin;
update ...;          // use <txn2, serial no 5> for object locks & use txn2 for row-locks/intents
commit;              // txn2 commit

Issue Type

kind/new-feature

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.