Thread: Re: Patch: Show queries of processes holding a lock
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