From: Markus Schaber <schabi@logix-tt.com>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Changing the transaction isolation level within the stored
Date: Thu, 26 Jan 2006 13:51:27 +0100
Message-ID: <43D8C5CF.6030000@logix-tt.com> (raw)
In-Reply-To: <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>
<20060126113902.GB3995@phlogiston.dyndns.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: schabi@logix-tt.com, ajs@crankycanuck.ca
Subject: Re: Changing the transaction isolation level within the stored
In-Reply-To: <43D8C5CF.6030000@logix-tt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox