Thread: '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
preciseand it doesn't have to be repeated over and over, just once. Perhaps even controlled by a GUC, though NOTICEs
aregenerally 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
ofwhomever 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
todiscover that it was blocked because another transaction had a lock. This mainly happens to us when scripts do
thingslike drop tables while someone else has a transaction which was using that table some time in the past and they
havn'tcommitted 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 GUClog_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