Re: Add “FOR UPDATE NOWAIT” lock details to the log.

On 2024/10/18 9:37, Seino Yuki wrote:
> Currently, we are discussing two improvements:
>
> 1. Log output when NOWAIT fails.
> 2. Adding control via GUC parameters (NOWAIT, lock_timeout, cancellation).
>
>> I'm not sure why it's challenging to provide detailed log messages for lock waits canceled
>> by lock_timeout or user cancellation, while it's considered feasible for the NOWAIT case.
>
> Does this statement mean that for 2, why can NOWAIT but lock_timeout,cancellation cannot?
>
> For item 2, the lock_timeout and cancellation will log outputs after the deadlock_timeout(e.g. 1s) has elapsed (by log_lock_waits).

This log message isn't directly related to lock_timeout or cancellations. It appears
if log_lock_waits is enabled and the lock wait time exceeds deadlock_timeout,
regardless of whether lock_timeout is set or if users cancel the operation.

I understood your original proposal as suggesting detailed log output whenever
a NOWAIT lock attempt fails. However, as I mentioned earlier, NOWAIT failures can
happen frequently, so logging every failure in detail could be too noisy for some users.
That’s why I proposed adding a new GUC parameter (or extending log_lock_waits)
to control whether such detailed logs should be generated for NOWAIT failures.

During our discussion, I also thought it would be useful to provide detailed logs
for lock waits canceled by user actions or lock_timeout. This behavior could be
controlled by a similar GUC parameter. However, this is an additional feature
beyond your original proposal, so I think it's fine to leave it out of the initial patch.

> At the time this log is output, it is unclear whether the lock will be cancellation or lock_timeout.
> This means that the timing at "error is determined" and "output logged" do not match.
>
>> However, I think it's reasonable to implement this feature step by step. We can start
>> by adding support for the NOWAIT case and consider extending it to handle lock_timeout and
>> cancellation scenarios later if possible.
>
> +1.
> I will send the version with the GUC parameter added from the previous patch.

The choice between adding a new GUC or extending the existing one (e.g., log_lock_waits)
is debatable, but I prefer the latter. I'm considering extending log_lock_waits
to accept a value like "fail". If set to "on" (the current behavior),
detailed logs are generated when the lock wait time exceeds deadlock_timeout.
If set to "fail", logs are generated whenever a lock wait fails. If both are
specified, logs would be triggered when the wait time exceeds deadlock_timeout or
when a lock wait fails.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION