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?
Show quoted text
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 transactionNot 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 transactionNot 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