Re: Deadlock Problem

Matthias Schmitt <freak002@mmp.lu> writes:
> I did the following in two psql shell environments:

> shell no 1:

> CREATE TABLE the_test (
> id   int4 PRIMARY KEY,
> name varchar(32)
> );

> insert into the_test values (1, 'hello world');

> begin;
> update the_test set name = 'still alive' where id = 1;

> To keep the transaction open I did not issue any commit or rollback
> command.

> shell no 2:

> begin;
> update the_test set name = 'still alive' where id = 1;

> The second shell hangs now forever.

Well, of course.  It has to wait to see if the previous update of the
row commits or not, so that it knows which version of the row to start
from.  (In this trivial case it doesn't really matter, but in more
complex cases such as where different fields are being updated, it
does.)

This is *not* a deadlock, however, as transaction 1 is free to make
progress.  The fact that you've got a client holding an open transaction
and not doing anything is a client-side design error, not a deadlock.

> Shouldn't a time-out error resolve those problems?

Sure, and it works fine:

regression=# set statement_timeout TO 10000;
SET
regression=# begin;
BEGIN
regression=# update the_test set name = 'still alive' where id = 1;
-- about ten seconds elapse, then:
ERROR:  canceling query due to user request
regression=#

            regards, tom lane