Message-Id: <s31ec6d5.059@gwmta.wicourts.gov>
Date: Wed, 07 Sep 2005 10:54:03 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-docs@postgresql.org>
Subject: Correction for 12.2.2.1. Serializable Isolation versus True
	Serializability
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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.
=20
For example, on Sybase ASE 12.5.1:
=20
-- 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.
=20
-- 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)
=20
-- Connection A:
set transaction isolation level serializable
begin transaction
SELECT SUM(value) FROM mytab WHERE class =3D 1
-- Query returns the value 30.
=20
-- Connection B:
set transaction isolation level serializable
begin transaction
SELECT SUM(value) FROM mytab WHERE class =3D 2
-- Query returns the value 300.
=20
-- Connection A:
insert into mytab values (2, 30)
-- Query blocks indefinitely, waiting for locks from Connection B.
=20
-- 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:
=20
Your server command (family id #0, process id #706) encountered a deadlock =
situation. Please re-run your command.
Error code: 1205
SQL state: 40001