Skip site navigation (1) Skip section navigation (2)

Correction for 12.2.2.1. Serializable Isolation versus TrueSerializability

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-docs(at)postgresql(dot)org>
Subject: Correction for 12.2.2.1. Serializable Isolation versus TrueSerializability
Date: 2005-09-07 15:54:03
Message-ID: s31ec6d5.059@gwmta.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-docs
At least two other popular production database products provide true serializability, as described in PostgreSQL documentation (section 12.2.2.1 of 8.1 devel).  I'm a big fan of PosgreSQL, but let's not overstate things.  Some users may have applications with do depend on the true serializability of their current product and may have subtle bugs under PostreSQL if they are not careful about the difference in behavior.

At a minimum we should remove the clause ", and so far as we are aware no other production DBMS does either".  If we could explicitly warn people about when these differences may cause problems, it could help smooth transitions from other products -- people could either ensure that they were safe, or they would know where they need to change code to be safe under PostgreSQL.
 
For example, on Sybase ASE 12.5.1:
 
-- Connection A:
create table mytab (class int not null, value int not null, primary key (class, value)) lock datarows
-- Using the least restrictive blocking level.  If it happens here it will happen with the other schemes.
 
-- Connection A:
insert into mytab values (1, 10)
insert into mytab values (1, 20)
insert into mytab values (2, 100)
insert into mytab values (2, 200)
 
-- Connection A:
set transaction isolation level serializable
begin transaction
SELECT SUM(value) FROM mytab WHERE class = 1
-- Query returns the value 30.
 
-- Connection B:
set transaction isolation level serializable
begin transaction
SELECT SUM(value) FROM mytab WHERE class = 2
-- Query returns the value 300.
 
-- Connection A:
insert into mytab values (2, 30)
-- Query blocks indefinitely, waiting for locks from Connection B.
 
-- Connection B:
insert into mytab values (1, 300)
-- Query blocks, waiting for locks from Connection A.
-- After a configurable delay, deadlock checking kicks in.
-- One query or the other will get an error like the following:
 
Your server command (family id #0, process id #706) encountered a deadlock situation. Please re-run your command.
Error code: 1205
SQL state: 40001


pgsql-docs by date

Next:From: Richard HuxtonDate: 2005-09-08 19:58:46
Subject: 8.1 CURRENT_USER / SESSION_USER
Previous:From: Marc G. FournierDate: 2005-09-05 02:07:27
Subject: Re: Typo in PostgreSQL 8.1 release notes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group