Ann,

I think we are still into the exercise of missing each others' point,
although perhaps with asymptotically diminishing distance. Both your
examples demonstrate that snapshot isolation is not serializable and I
think we have agreed a long time ago in this thread that distributed
databases need distributed snapshot isolation to scale.

The point that I am making when talking about "ordering" is *not* the
ordering on a single node, but the relative ordering between nodes. If
snapshot isolated transaction A commits before snapshot isolated
transaction B and if both A and B updated the same record, then and only
then must A commit before B on all nodes.

What is not getting through my thick skull, so that you and Jim keep
demonstrating that snapshot isolation is not serializable, which to me is
a
truism?

Paul


On Tue, 09 Feb 2010 17:17:45 -0500, "Ann W. Harrison"
<aharrison@...> wrote:

> 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
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>