More details in pg_locks for YugabyteDB

One benefit of Wait-on-Conflict concurrency control, also known as pessimistic locking, is its simplicity in observation and troubleshooting when it impacts performance, especially when all lock intents are observable.
Let's quickly explore how to use pg_locks in YugabyteDB to examine the blocking and blocked transactions when waiting to acquire a lock.

In the following example, I created a table with three rows. One session deletes them. Before it commits, another session tries to delete the same rows. This is fast, thanks to pessimistic locking, where it waits for the completion of the first one instead of raising an error. For demo purposes, to observe the locks on a small table, I exaggerate the transaction duration by waiting five seconds:

set yb_use_hash_splitting_by_default to off;

create table demo ( id bigserial, value int, primary key(id));
insert into demo (value) select generate_series(1,3);

\timing on
begin transaction;
delete from demo;

-- another session attempts the same delete
\! sleep 5 ; psql -ec '\timing on' -c 'delete from demo' & sleep 5

select locktype, mode, granted
, regexp_replace( ybdetails->>'transactionid'      ,'[-0-9a-f]{32}'        ,'','g') as txid
, regexp_replace( ybdetails->>'blocked_by'       ,'("[-0-9a-f]{32}|"|]|\[)','','g') as blockers
, ybdetails->'keyrangedetails'->>'cols' as cols
from pg_locks
where relation='demo'::regclass
order by blockers desc,txid, mode desc
;

commit;

I queried the pg_lock table, which is inherited from PostgreSQL. YugabyteDB adds more details in a ybdetails document accessible with JSON functions:

yugabyte=# begin transaction;
BEGIN
Time: 18.158 ms
yugabyte=*# delete from demo;
DELETE 3
Time: 21.419 ms
yugabyte=*# \! sleep 5 ; psql -ec '\timing on' -c 'delete from demo' & sleep 5
Timing is on.
delete from demo
yugabyte=*# select locktype, mode, granted
yugabyte-*# , regexp_replace( ybdetails->>'transactionid'      ,'.{32}'        ,'','g') as txid
yugabyte-*# , regexp_replace( ybdetails->>'blocked_by'       ,'(".{32}|"|]|\[)','','g') as blockers
yugabyte-*# , ybdetails->'keyrangedetails'->>'cols' as cols
yugabyte-*# from pg_locks
yugabyte-*# where relation='demo'::regclass
yugabyte-*# order by blockers desc,txid, mode desc
yugabyte-*# ;
 locktype |           mode           | granted | txid | blockers | cols
----------+--------------------------+---------+------+----------+-------
 relation | WEAK_READ,WEAK_WRITE     | t       | 1375 |          |
 row      | STRONG_READ,STRONG_WRITE | t       | 1375 |          | ["1"]
 row      | STRONG_READ,STRONG_WRITE | t       | 1375 |          | ["2"]
 row      | STRONG_READ,STRONG_WRITE | t       | 1375 |          | ["3"]
 relation | WEAK_READ,WEAK_WRITE     | f       | e5cb | 1375     |
 row      | STRONG_READ,STRONG_WRITE | f       | e5cb | 1375     | ["1"]
 row      | STRONG_READ,STRONG_WRITE | f       | e5cb | 1375     | ["2"]
 row      | STRONG_READ,STRONG_WRITE | f       | e5cb | 1375     | ["3"]
(8 rows)

Time: 21.732 ms
yugabyte=*# commit;
COMMIT
Time: 19.304 ms
yugabyte=# DELETE 0
Time: 4979.748 ms (00:04.980)

I can see two transactions. One (e5cb) is waiting for the other (1375) because it encountered conflicting locks.
The delete acquires STRONG_READ and STRONG_WRITE on the deleted rows, which conflicts with other transactions doing the same. A WRITE lock is an exclusive lock, which conflicts with READ, a shared lock, and WRITE, an exclusive lock.
The WEAK locks are there for performance, so a STRONG WRITE at the relation (table) level would not have to consider all row-level locks, but this is not the case here.

In ybdetails, there is enough information to identify which rows conflict. YugabyteDB provides all details because all transaction intents are stored in the provisional records (IntentsDB in the distributed storage). When you query pg_locks, it reads the tablets involved in the transaction from the global transaction table and fetches the relevant information from those tablets (IntentsDB). You should not query this view too often, but you can query it when troubleshooting a blocking situation.

PostgreSQL doesn't have this level of detail because the row locks are stored within the heap table, not separating provisional records like YugabyteDB does with IntentsDB and not organizing them by key as YugabyteDB does with LSM tree. To compare, here is what PostgreSQL displays in the same situation:

postgres=*> select locktype, mode, granted, virtualtransaction , *
from pg_locks
where relation='demo'::regclass
order by virtualtransaction, mode desc
;

 locktype |        mode         | granted | virtualtransaction | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |        mode         | granted | fastpath | waitstart
----------+---------------------+---------+--------------------+----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------------+---------+----------+-----------
 relation | RowExclusiveLock    | t       | 11/18              | relation |    16385 |   974858 |      |       |            |               |         |       |          | 11/18              | 1654 | RowExclusiveLock    | t       | t        |
 relation | RowExclusiveLock    | t       | 8/35               | relation |    16385 |   974858 |      |       |            |               |         |       |          | 8/35               | 2363 | RowExclusiveLock    | t       | t        |
 tuple    | AccessExclusiveLock | t       | 8/35               | tuple    |    16385 |   974858 |    0 |     1 |            |               |         |       |          | 8/35               | 2363 | AccessExclusiveLock | t       | f        |
(3 rows)

Time: 183.098 ms

RowExclusiveLock is not a row lock but a table lock. You know this when looking at the lock mode relation. It is an intent lock (the intention to acquire row locks on this table) equivalent to the WEAK locks acquired by YugabyteDB.

In PostgreSQL, The blocker/blocking relationship is not visible from here because there is no information about row-level locks, and this is where the conflict occurs. You can look at pg_stat_activity to see the wait events, but not pg_locks.
A tuple level AccessExclusiveLock shows the currently accessed row (with relation, page, and tuple number), which can provide a clue, but it doesn't list all locks acquired.

YugabyteDB locks are described in:

Transaction isolation levels | YugabyteDB Docs

Learn how YugabyteDB supports two transaction isolation levels Snapshot Isolation and Serializable.

docs.yugabyte.com

YugabyteDB pg_locks is described in:

Get lock information insights with pg_locks | YugabyteDB Docs

Using pg_locks to get insights into lock information specific to YugabyteDB's distributed SQL architecture.

docs.yugabyte.com

A few additional remarks for YugabyteDB:

• You may see multiple relation lock types for the same table, because it is distributed and locks are stored per tablet. The details are in ybdetails->>'node' and ybdetails->>'tablet_id'
• Lock information is available at the table, keyrange, row, and column levels, but the outputs when querying pg_locks is limited by yb_locks_max_transactions
• Short locks are not displayed when querying pg_locks as the goal is to see the blocking ones, this is controlled by yb_locks_min_txn_age
• Write operations are batched to avoid network latency, so you see many lock intents for the blocked transaction, even if it was blocked on the first row.

Like PostgreSQL, Oracle Database displays only the table-level locks in V$LOCK. You can know the row from the blocked transaction in V$SESSION, but there is no visible information about row-level locks acquired by the blocking transaction. Other databases store locks in memory rather than storage and can display them, but they do not scale because of lock escalation.
In contrast, YugabyteDB stores lock intents in a scalable and observable way. IntentsDB is a per-tablet LSM tree with its first level in memory. Long transactions can grow by flushing into SST files. YugabyteDB can display all locks acquired by a transaction, which helps troubleshoot conflict issues.