Date: Thu, 26 Jan 2006 06:39:02 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Changing the transaction isolation level within the	stored
Message-ID: <20060126113902.GB3995@phlogiston.dyndns.org>
References: <1138198348.8828.32.camel@localhost.localdomain>
	<43D79132.9050900@logix-tt.com>
	<1138259463.16164.4.camel@localhost.localdomain>
	<43D8999E.9090907@logix-tt.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <43D8999E.9090907@logix-tt.com>
User-Agent: Mutt/1.5.9i

On Thu, Jan 26, 2006 at 10:42:54AM +0100, Markus Schaber wrote:

> AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all,
> the only way to introduce deadlocks is to issue LOCK commands to take
> locks manually. And for this rare case, PostgreSQL contains a deadlock
> detection routine that will abort one of the insulting transactions, and
> the others can proceed.

You can too.  Consider this:

t1                                        t2

BEGIN;                                    BEGIN;
UPDATE table1 SET col1=                   UPDATE table2 SET col1=
     col1+5;                                  (SELECT col3 FROM
DELETE FROM table2 WHERE                          table3);
	col1 = col1+6;                    UPDATE table1 SET col1 =
		                                 col1 +5;
COMMIT;                                   COMMIT;

Suppose these are concurrent.  The problem here is that each
transaction need something in the other transaction either to
complete or rollback before the work can proceed.  So one of them has
to lose.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
		--Dennis Ritchie