Thread: WaitOnLock error - what does this mean?
I got the error:
ERROR: WaitOnLock: error on wakeup - Aborting this transaction
Can anyone explain what this means?
I am testing my database by having it do inserts continuously using a bash
while true loop that calls psql --file to do the inserts. When I try to do a
different kind of insert from a php script at the same time, I get the above
error. I can provide more details if needed. One thing, I have PL/pgSQL
triggers that cause a chain of updates to happen for each insert.
--
- Robert
"Robert B. Easter" <reaster@comptechnews.com> writes:
> ERROR: WaitOnLock: error on wakeup - Aborting this transaction
Is there any NOTICE coming out right before that?
For example:
psql #1:
begin;
lock table tenk1;
psql #2:
begin;
lock table tenk2;
lock table tenk1;
<waits>
psql #1:
lock table tenk2;
NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause.
ERROR: WaitOnLock: error on wakeup - Aborting this transaction
Not sure why this isn't just one ERROR, since anyone using a client that
discards NOTICEs is going to be confused...
regards, tom lane
On Sun, 23 Jul 2000, Tom Lane wrote: > "Robert B. Easter" <reaster@comptechnews.com> writes: > > ERROR: WaitOnLock: error on wakeup - Aborting this transaction > > Is there any NOTICE coming out right before that? Yes. After sending my email to this list, I kept trying the same thing again. After a few more tries, my php script managed to do an insert but when I looked at the inserts running from bash I saw both the NOTICE and ERROR. My php script only reports ERRORs when a query fails (not sure if php has any ability to report notices). Its random which inserts will be the cause of deadlock. I guess I'll just have to make the script output a more user-friendly message like: system is too busy, please try again later. Is my database design flawed if this occurs? > > For example: > > psql #1: > > begin; > lock table tenk1; > > psql #2: > > begin; > lock table tenk2; > lock table tenk1; > > <waits> > > psql #1: > > lock table tenk2; > > NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. > ERROR: WaitOnLock: error on wakeup - Aborting this transaction > > > Not sure why this isn't just one ERROR, since anyone using a client that > discards NOTICEs is going to be confused... >
"Robert B. Easter" <reaster@comptechnews.com> writes:
> Is my database design flawed if this occurs?
The db layout is probably not the issue, but I'd say your scripts are
flawed.
The usual recipe for avoiding deadlocks is pretty simple: all acquirers
of locks must grab the locks in the same order.
regards, tom lane
> "Robert B. Easter" <reaster@comptechnews.com> writes: > > ERROR: WaitOnLock: error on wakeup - Aborting this transaction > > Is there any NOTICE coming out right before that? > > For example: > > psql #1: > > begin; > lock table tenk1; > > psql #2: > > begin; > lock table tenk2; > lock table tenk1; > > <waits> > > psql #1: > > lock table tenk2; > > NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. > ERROR: WaitOnLock: error on wakeup - Aborting this transaction > > > Not sure why this isn't just one ERROR, since anyone using a client that > discards NOTICEs is going to be confused... Seems the code wants to throw the NOTICE, then keep going, then throw the error later. I don't know who did this, but is was not me. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> "Robert B. Easter" <reaster@comptechnews.com> writes: > > Is my database design flawed if this occurs? > > The db layout is probably not the issue, but I'd say your scripts are > flawed. > > The usual recipe for avoiding deadlocks is pretty simple: all acquirers > of locks must grab the locks in the same order. Of course, reading the lock(l) manual page helps too. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026