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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
