Postgres has a configuration to limit the amount of time a given statement waits to acquire an individual lock: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT

Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object. The time limit applies separately to each lock acquisition attempt. The limit applies both to explicit locking requests (such as LOCK TABLE, or SELECT FOR UPDATE without NOWAIT) and to implicitly-acquired locks. If this value is specified without units, it is taken as milliseconds. A value of zero (the default) disables the timeout.

Unlike statement_timeout, this timeout can only occur while waiting for locks. Note that if statement_timeout is nonzero, it is rather pointless to set lock_timeout to the same or larger value, since the statement timeout would always trigger first. If log_min_error_statement is set to ERROR or lower, the statement that timed out will be logged.

Setting lock_timeout in postgresql.conf is not recommended because it would affect all sessions.

We currently accept this session variable, but ignore it.

This would provide a more targeted solution for the same "lock bloat" problem detailed in #66017. The implementation of this would be fairly straightforward. We would introduce a new lock_timeout field on roachpb.Header and plumb this down to the lockTableWaiter. It would be similar to #52388.

// lock_timeout specifies the maximum amount of time that the batch request
// will wait while attempting to acquire a lock on a key or while blocking on
// an existing lock in order to perform a non-locking read on a key. The time
// limit applies separately to each lock acquisition attempt.
//
// Unlike other systems, where non-locking reads do not wait on conflicting
// locks, in CockroachDB, non-locking reads do wait for conflicting locks to
// be released. Because of this, the lock_timeout configuration applies to
// non-locking reads in read-write and read-only transactions as well.
//
// Only the (default) Block wait policy will allow a request to wait on
// conflicting locks, so the timeout only makes sense in conjunction with the
// Block wait policy.
google.protobuf.Duration lock_timeout = 19 [(gogoproto.nullable) = false,
                                            (gogoproto.stdduration) = true];