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
mayhave applications with do depend on the true serializability of their current product and may have subtle bugs under
PostreSQLif 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
couldexplicitly warn people about when these differences may cause problems, it could help smooth transitions from
otherproducts -- people could either ensure that they were safe, or they would know where they need to change code to
besafe 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