'Waiting on lock'
Greetings,
It'd be nice to have a NOTICE printed when a wait-on-lock takes longer
than a few seconds. It doesn't need to be precise and it doesn't have
to be repeated over and over, just once. Perhaps even controlled by a
GUC, though NOTICEs are generally ignored by non-interactive
applications anyway, I believe.
I'd say if the lock hasn't been aquired after 5 seconds, print
something like:
NOTICE: Waiting on lock
If it's available easily we might also list the type of lock, the OID,
the table name, whatever, and perhaps the PID of whomever has the lock
currently.
It's just very frustrating to kick off something you expect to run for
a while and come back quite a long time later to discover that it was
blocked because another transaction had a lock. This mainly happens
to us when scripts do things like drop tables while someone else has a
transaction which was using that table some time in the past and they
havn't committed yet.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
It'd be nice to have a NOTICE printed when a wait-on-lock takes longer
than a few seconds.
It'd be relatively painless to make that happen as part of the
deadlock-check timeout function, but that's typically only a one-second
delay not a "few seconds". I think it'd likely be overly chatty.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
It'd be nice to have a NOTICE printed when a wait-on-lock takes longer
than a few seconds.It'd be relatively painless to make that happen as part of the
deadlock-check timeout function, but that's typically only a one-second
delay not a "few seconds". I think it'd likely be overly chatty.
Yeah, I wouldn't want one per second. Do we already track how long
we've been waiting? Easy enough to % off that if we do, or just have a
local boolean variable of "have we printed the wait-on-lock notice yet?"
and only print it once when we first drop into the timeout function.
I really was thinking it'd only be printed once since I expect this to
be going to an interactive session where someone's going to notice a
'NOTICE' being sent. I could maybe see another message when we actually
aquire the lock being sent if we've sent the 'wait-on-lock' message.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
It'd be relatively painless to make that happen as part of the
deadlock-check timeout function, but that's typically only a one-second
delay not a "few seconds". I think it'd likely be overly chatty.
Yeah, I wouldn't want one per second. Do we already track how long
we've been waiting?
No, because we're *asleep*. You'd have to add an additional
timeout-interrupt reason. Plus there's a ton of interesting questions
about what's safe to do from an interrupt service routine.
In fact, I am scandalized to see that someone has inserted a boatload
of elog calls into CheckDeadLock since 8.2 --- that seems entirely
unsafe. [ checks revision history... ]
2007-03-03 13:46 momjian
* doc/src/sgml/config.sgml, src/backend/storage/lmgr/deadlock.c,
src/backend/storage/lmgr/proc.c, src/backend/utils/misc/guc.c,
src/backend/utils/misc/postgresql.conf.sample,
src/include/storage/lock.h, src/include/storage/proc.h: Add GUC
log_lock_waits to log long wait times.
Simon Riggs
Bruce, Simon, kindly fix this or revert it.
regards, tom lane
Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
It'd be nice to have a NOTICE printed when a wait-on-lock takes longer
than a few seconds.It'd be relatively painless to make that happen as part of the
deadlock-check timeout function, but that's typically only a one-second
delay not a "few seconds". I think it'd likely be overly chatty.Yeah, I wouldn't want one per second.
It's not one per second, it's after one second (actually
deadlock_timeout) has elapsed since you started to sleep waiting for a
lock. If a deadlock is not detected the process won't be awakened
again.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
* Alvaro Herrera (alvherre@commandprompt.com) wrote:
Stephen Frost wrote:
Yeah, I wouldn't want one per second.
It's not one per second, it's after one second (actually
deadlock_timeout) has elapsed since you started to sleep waiting for a
lock. If a deadlock is not detected the process won't be awakened
again.
Ah, I see.. Actually, if it's just one NOTICE after one second, I think
that'd be fine. Sorry, misunderstood what was going on.
Thanks!
Stephen
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
It'd be relatively painless to make that happen as part of the
deadlock-check timeout function, but that's typically only a one-second
delay not a "few seconds". I think it'd likely be overly chatty.Yeah, I wouldn't want one per second. Do we already track how long
we've been waiting?No, because we're *asleep*. You'd have to add an additional
timeout-interrupt reason. Plus there's a ton of interesting questions
about what's safe to do from an interrupt service routine.
Eh, I wouldn't really want to add an additional timeout-interrupt if we
could avoid it. Sorry, hadn't followed what you meant (honestly, I
expected us to already have some kind of timeout loop when waiting, nice
to know that we don't :). As I mentioned to Alvaro, a single NOTICE
after a full second of waiting would be fine for my use case, at least.
My main concern was that it'd be one-per-second, which would be too
much.
I agree with your concern about doing things from an ISR though...
Thanks,
Stephen
On Wed, 2007-05-30 at 12:27 -0400, Tom Lane wrote:
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
It'd be relatively painless to make that happen as part of the
deadlock-check timeout function, but that's typically only a one-second
delay not a "few seconds". I think it'd likely be overly chatty.Yeah, I wouldn't want one per second. Do we already track how long
we've been waiting?No, because we're *asleep*. You'd have to add an additional
timeout-interrupt reason. Plus there's a ton of interesting questions
about what's safe to do from an interrupt service routine.In fact, I am scandalized to see that someone has inserted a boatload
of elog calls into CheckDeadLock since 8.2 --- that seems entirely
unsafe. [ checks revision history... ]2007-03-03 13:46 momjian
* doc/src/sgml/config.sgml, src/backend/storage/lmgr/deadlock.c,
src/backend/storage/lmgr/proc.c, src/backend/utils/misc/guc.c,
src/backend/utils/misc/postgresql.conf.sample,
src/include/storage/lock.h, src/include/storage/proc.h: Add GUC
log_lock_waits to log long wait times.Simon Riggs
Bruce, Simon, kindly fix this or revert it.
'twas me. Looking at it now.
Good news is the semantics are exactly what Stephen has requested...
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com