Paul Ruizendaal wrote:

>
> In my opinion the definition you give above is satisfied if conflicting
> updates have a consistent order, and all other access has a fifo order
> (both terms as used with GCS's). Agree?
>

A bit late, but here are two different examples where write ordering
alone does not provide serializability. Jim referenced one.
In the example below, SQL1 is one connection, SQL2 is a second
connection

C:\harrison>\firebird\bin\isql
Use CONNECT or CREATE DATABASE to specify a database
SQL1> create database 'foo.fdb';
SQL1> create table counter (f1 integer);
SQL1> insert into counter select count(*) from counter;
SQL2> insert into counter select count(*) from counter;
SQL1> insert into counter select count(*) from counter;
SQL1> insert into counter select count(*) from counter;
SQL2> insert into counter select count(*) from counter;
SQL2> insert into counter select count(*) from counter;
SQL1> insert into counter select count(*) from counter;
SQL2> insert into counter select count(*) from counter;

SQL1> commit;
SQL2> commit;
SQL1> select * from counter;

F1
============
0
0
1
2
1
2
3
3


No way that could happen with two transactions run in
series. On the other hand, it can be prevented with a
unique index, which would cause no more failures than
running in a lock-based serializable transaction.

The second problem is harder to describe and harder to prevent,
as far as I know. Again, SQL1 is one connection, SQL2 is another.
Each updates the contents of one table, using values from another
table, but their updates are inverse: t1->t2 for one and t2->t1
for the other.

SQL1> create table t1 (f1 integer, f2 char(1));
SQL1> create table t2 (f1 integer, f2 char(1));
SQL1> insert into t1 values (1, 'a');
SQL1> insert into t1 values (2, 'b');
SQL1> insert into t1 values (3, 'c');
SQL1> insert into t2 values (1, 'z');
SQL1> insert into t2 values (2, 'y');
SQL1> insert into t2 values (3, 'x');
SQL1> commit;
SQL1> update t1
CON1> set f2 = (select first 1 f2 from t2
CON1> where t1.f1 = t2.f1);
SQL2> update t2
CON2> set f2 = (select first 1 f2 from t1
CON2> where t1.f1 = t2.f1);
SQL1> commit;
SQL2> commit;
SQL1> select * from t1

F1 F2
============ ======
1 z
2 y
3 x

SQL2> select * from t2;

F1 F2
============ ======
1 a
2 b
3 c

If the two transactions ran separately, both tables
would have the same values. The character values might
be a,b,c or z,y,x, depending on the order of transactions,
but both tables would be the same.


In a system that enforces serializability with locks,
one of two things would have happened.

With luck, the second transaction would try to get a
read lock on the first record in t2, which succeeds
because the first transaction only reads t2, and two
read locks are compatible. It would then try to get
a read lock on the first record in t1, and would wait
for transaction 1 which already had a write lock on
that record. When transaction 1 commits, transaction
2 proceeds.

With less luck each transaction would acquire read
locks on records in both tables and attempt to upgrade
one of them to a write lock, leading to a deadlock.


Note that no transaction mix that uses sequence generators,
timestamps, or any other volatile data from the outside
world is going to be serializable ... AFAIK


Best regards,


Ann