Re: Deadlock Problem

On Tue, Mar 16, 2004 at 09:50:43AM +0100, Matthias Schmitt wrote:
> 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. The pg_locks table shows:

First, you haven't tried to COMMIT anywhere. Nothing will happen in
that case. For all you know, one of these is going to ROLLBACK and
resolve the lock on its own. The second one you issue is just going
to sit there, sure.

You actually haven't describe a deadlock here. This is just a
straightforward wait-and-see lock for transaction 2: that update
statement will indeed wait forever until it sees what 1 has done.
You'd only get a deadlock in case transaction 2 first did something
that T1 _next_ had to depend on. You really need two conflicting
locks for a deadlock to happen.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun