Thread: Deadlock error!?

Deadlock error!?

From
Jean-Christian Imbeault
Date:
I got the following error while running some php code:

Warning: pg_exec() query failed: ERROR: deadlock detected in
/www/htdocs/jc/import/import_orders.php on line 189
CONNECTION: could not execute query (update invoices set
total_price=(select sum(price) from invoice_li where invoice_id=id))

This happened while the PHP code was running and and on the console I
did this query:

psql TMP -c "update invoices set payment_rcvd=true"

The PHP code was executing SQL inside a transaction and the total
running time is about 1 minute. Why a deadlock?

Whatever is happening inside a transaction is given a snapshot of the DB
and should be oblivious to other queries no?

Jc


Re: Deadlock error!?

From
Stephan Szabo
Date:
On Thu, 19 Sep 2002, Jean-Christian Imbeault wrote:

> I got the following error while running some php code:
>
> Warning: pg_exec() query failed: ERROR: deadlock detected in
> /www/htdocs/jc/import/import_orders.php on line 189
> CONNECTION: could not execute query (update invoices set
> total_price=(select sum(price) from invoice_li where invoice_id=id))
>
> This happened while the PHP code was running and and on the console I
> did this query:
>
> psql TMP -c "update invoices set payment_rcvd=true"
>
> The PHP code was executing SQL inside a transaction and the total
> running time is about 1 minute. Why a deadlock?
>
> Whatever is happening inside a transaction is given a snapshot of the DB
> and should be oblivious to other queries no?

Does invoices have any foreign key? My prime suspect is that the over
strong locks there did it.  And if they were oblivious snapshots,
which of those two statements did you want to take effect since one
would blast away the other's changes? ;)