Message-ID: <43D8C5CF.6030000@logix-tt.com>
Date: Thu, 26 Jan 2006 13:51:27 +0100
From: Markus Schaber <schabi@logix-tt.com>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Changing the transaction isolation level within the	stored
References: <1138198348.8828.32.camel@localhost.localdomain>
	<43D79132.9050900@logix-tt.com>
	<1138259463.16164.4.camel@localhost.localdomain>
	<43D8999E.9090907@logix-tt.com>
	<20060126113902.GB3995@phlogiston.dyndns.org>
In-Reply-To: <20060126113902.GB3995@phlogiston.dyndns.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi, Andrew,

Andrew Sullivan 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;

Hmm, are you shure that this is correct? The delete will always delete 0
rows.

http://www.postgresql.org/docs/8.0/static/transaction-iso.html#XACT-SERIALIZABLE
contains a nice example in '12.2.2.1. Serializable Isolation versus True
Serializability' that you should probably read.

> 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.

Despite the fact that I don't see such a collision in your example:

Depending on the transaction isolation level and exact timings,
colliding queries may lead to different results or even one transaction
aborted, but there is no deadlock under MVCC.

Not needing such locks is the whole point in using MVCC at all.

Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org