Re: Set log_lock_waits=on by default

On Thu, 2023-12-21 at 09:14 -0500, Robert Haas wrote:
>
> I think it depends somewhat on the lock type, and also on your
> threshold for what constitutes a problem. For example, you can wait
> for 1 second for a relation extension lock pretty easily, I think,
> just because the I/O system is busy. Or I think also a VXID lock held
> by some transaction that has a tuple locked could be not particularly
> exciting. A conflict on a relation lock seems more likely to represent
> a real issue, but I guess it's all kind of a judgement call. A second
> isn't really all that long on an overloaded system, and I see an awful
> lot of overloaded systems (because those are the people who call me).

Sure, you don't want "log_lock_waits = on" in all conceivable databases.
I have seen applications that use database locks to synchronize
application threads (*shudder*). If it is normal for your database
to experience long lock waits, disable the parameter.

My point is that in the vast majority of cases, long lock waits
indicate a problem that you would like to know about, so the parameter
should default to "on".

(Out of curiosity: what would ever wait for a VXID lock?)

> Just a random idea but what if we separated log_lock_waits from
> deadlock_timeout? Say, it becomes time-valued rather than
> Boolean-valued, but it has to be >= deadlock_timeout? Because I'd
> probably be more interested in hearing about a lock wait that was more
> than say 10 seconds, but I don't necessarily want to wait 10 seconds
> for the deadlock detector to trigger.

That is an appealing thought, but as far as I know, "log_lock_waits"
is implemented by the deadlock detector, which is why it is tied to
"deadlock_timeout". So if we want that, we'd need a separate "live
lock detector". I don't know if we want to go there.

Yours,
Laurenz Albe