Thread: Re: Patch: Show queries of processes holding a lock

Re: Patch: Show queries of processes holding a lock

From
wenhui qiu
Date:
Hi Alexey Orlov
     Thank you for your work on this path,The lock information is recorded in detail,Easy to trace the lock competition at that time there is a detailed lock competition log,But I have a concern,Frequent calls to this function (pgstat_get_backend_current_activity) in heavy lock contention or high concurrency environments may cause performance degradation, especially when processes frequently enter and exit lock waits. Can you add a guc parameter to turn this feature on or off?After all communities for this parameter( log_lock_waits )default values set to on many people concern (https://commitfest.postgresql.org/49/4718/)



Thanks

Alexey Orlov <aporlov@gmail.com> 于2024年10月1日周二 16:04写道:
Hi, there!

I created patch improving the log messages generated by
log_lock_waits.

Sample output (log_lock_waits=on required):

session 1:
CREATE TABLE foo (val integer);
INSERT INTO foo (val) VALUES (1);
BEGIN;
UPDATE foo SET val = 3;

session 2:
BEGIN;
UPDATE TABLE foo SET val = 2;

Output w/o patch:

LOG: process 3133043 still waiting for ShareLock on transaction 758
after 1000.239 ms
DETAIL: Process holding the lock: 3132855. Wait queue: 3133043.
CONTEXT: while updating tuple (0,7) in relation "foo"
STATEMENT: update foo SET val = 2;

Output with path

LOG: process 3133043 still waiting for ShareLock on transaction 758
after 1000.239 ms
DETAIL: Process holding the lock: 3132855. Wait queue: 3133043.
Process 3132855: update foo SET val = 3;
CONTEXT: while updating tuple (0,7) in relation "foo"
STATEMENT: update foo SET val = 2;

As you can see information about query that holds the lock goes into log.

If this approach proves unacceptable, we can make the log_lock_waits
parameter as an enum
and display the query if the log_lock_waits=verbose (for example).

What do you think?

Regards,

--
Orlov Alexey