"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Postgresql supports Serializable transactions, which are 100% ACID
> compliant.
How can I activate it? 8-)
Yes, I know about SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, please
read on.
Given the two tables:
CREATE TABLE items (item INTEGER);
CREATE TABLE counts (count INTEGER);
And transactions following this pattern:
number := <some number>; INSERT INTO items VALUES (number); nr := SELECT COUNT(*) FROM items; INSERT INTO counts
VALUES(nr); COMMIT;
If these transactions are executed serially, the following condition
always holds once the tables are non-empty:
(*) (SELECT COUNT(*) FROM items) = (SELECT MAX(count) FROM counts)
Now look at the following history:
Session 1 Session 2
number := <some number>; number := <some number>;
INSERT INTO items VALUES (number); INSERT INTO items VALUES (number);
nr := SELECT COUNT(*) FROM items; nr := SELECT COUNT(*) FROM items;
INSERT INTO counts VALUES (nr); INSERT INTO counts VALUES (nr);
COMMIT; COMMIT;
If you enter these commands in two parallel psql sessions, in the
order indicated, condition (*) no longer holds once both transactions
are completed. Therefore, PostgreSQL must have generated a
non-serializable history.
Is this a bug, or is SQLxx serializability defined in different terms?