[YSQL] Support observability of actively held and contended locks via pg_locks

[pkj415]

Jira Link: DB-502

Description

This google doc is the the functional specification for this feature.

The functional specification and design document will be moved to a .md file once the design is finalized.

Postgres

• [YSQL] Expose yb_lock_status stored procedure to display lock status information #15736
• [YSQL] Redefine pg_locks to call yb_lock_status #16918
• [YSQL] Enable non-superuser or non-admin users to request information from pg_locks/yb_lock_status #16919
• [DocDB] Enable manually cancelling transaction #15726
• [DocDB] Expose endpoint in pg_client_session to cancel a transaction #16696

PG Client

• [DocDB] Implement GetLockStatus in pg_client_service #16914
• [DocDB] Handle tablet split when reading lock information from tserver #15648
• [DocDB] Support relation_id in pg_client_service GetLockStatus #16915
• [DocDB] Restrict pg_client_service GetLockStatus results when transaction_id is specified #16916

Transaction Coordinator

• [DocDB] Expose RPC endpoint to fetch long-running transactions from transaction coordinator #16912
• [DocDB] Use GetTransactionStatus RPC in pg_client_service for yb_lock_status calls to specific transaction #16913

Tablet

• [YSQL] View intentsdb information on the tserver web UI in debug builds #13540
• [YSQL] Expose persisted tablet lock information via tserver RPC #15444
• [DocDB] Augment GetLockStatus tserver endpoint to handle transaction_id list #16911
• [YSQL] Populate wait queue lock-info from tservers in yb_lock_status #14850
• [DocDB] Ensure locks from aborted subtransactions are excluded from pg_locks #16910

[robertsami]

We've tentatively agreed on the following schema:

Important fields
----------------
locktype           | relation, keyrange
database           | oid
relation           | oid
pid                | will use 0 for remote backends
mode               | map YSQL modes into Pg modes as much as possible. Add new ones when not possible
granted            | true/ false
fastpath           | Reuse field to indicate whether this is a single shard transaction. In Pg, this has a different meaning which doesn't concern us.
waitstart          | when did this backend start waiting for the lock
waitend            | extra field in YB that also indicates when a particular lock was acquired
ybdetails          | {
                        "node": where does the lock reside?,
                        "transactionid": transaction that holds the lock,
                        "subtransactionid": subtransaction in which lock was acquired,
                        "isexplicit": is this an explicit lock such as `FOR UPDATE` or just an implicit one like one taken by `UPDATE`,
                        "tabletid": which tablet?,
                        "ybkeyrangedetails": {
                          "hash cols": [list of hash col values],
                          "range cols": [list of range col values],
                          "is_full_pk": true/ false
                          "column_id": column id if this is a col level lock
                        }
                     }

Unimportannt fields
-------------------

page               | ****Removed****
tuple              | ****Removed****
virtualxid         | ****Removed****. (In Pg this is not the current txn's xid, it is the virtualxid of the txn that is to be locked. We don't allow locking virtual txn ids in YSQL)
Transactionid      | ****Removed****. (In Pg this is not the current txn's id, it is the id of the txn that is to be locked. We don't allow locking txn ids in YSQL)
classid            | NULL in YSQL since we don't support locking db objects
objid              | NULL in YSQL since we don't support locking db objects
objsubid           | NULL in YSQL since we don't support locking db objects
virtualtransaction | ****Removed**** we decided to preserve this in the meeting for single shard transactions, but we can reuse "transactionid" of "ybdetails" field and ignore this field

[rthallamko3]

Created #18901 to track the remaining GA items, so that we can close this GH issue. cc @robertsami . This helps with tracking on the release side.