Description:
I have two transactions, on different connections, which I'll call T1 and T2.
I set the isolation level to SERIALIZABLE for both transactions.
My database initially has one table named t, with 1 row with {0} in it.
The first transaction says: (delete from t; commit).
The second transaction says: {insert into t select max(s1)+1 from t; commit).
If SERIALIZABLE works, then both transactions are serialized.
If they're serialized, then either {delete; commit} happens first
or {insert into t select s1+1 from t; commit} happens first.
If {delete; commit} happens first, then t will have 1 row with {NULL}.
If {insert into t select s1+1 from t; commit} happens first,
then t will have no rows.
But in fact, t has 1 row with {1}.
Therefore the transactions were not serialized.
Therefore SERIALIZABLE does not work.
Or (an acceptable answer to me) SERIALIZABLE is not ANSI SERIALIZABLE,
but in that case a clear statement is needed: what phenomena are stopped?
There is possible overlap between this and bug#22175.
How to repeat:
On T1, say:
create table t (s1 int, primary key (s1)) engine=falcon;
set @@autocommit=0;
insert into t values (0);
commit;
set transaction isolation level serializable;
start transaction;
On T2, say:
set @@autocommit=0;
commit;
set transaction isolation level serializable;
start transaction;
insert into t select max(s1)+1 from t;
On T1, say:
delete from t;
commit;
On T2, say:
commit;
On T1, say:
select * from t;
The final result is:
mysql> select * from t;
+----+
| s1 |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
Description: I have two transactions, on different connections, which I'll call T1 and T2. I set the isolation level to SERIALIZABLE for both transactions. My database initially has one table named t, with 1 row with {0} in it. The first transaction says: (delete from t; commit). The second transaction says: {insert into t select max(s1)+1 from t; commit). If SERIALIZABLE works, then both transactions are serialized. If they're serialized, then either {delete; commit} happens first or {insert into t select s1+1 from t; commit} happens first. If {delete; commit} happens first, then t will have 1 row with {NULL}. If {insert into t select s1+1 from t; commit} happens first, then t will have no rows. But in fact, t has 1 row with {1}. Therefore the transactions were not serialized. Therefore SERIALIZABLE does not work. Or (an acceptable answer to me) SERIALIZABLE is not ANSI SERIALIZABLE, but in that case a clear statement is needed: what phenomena are stopped? There is possible overlap between this and bug#22175. How to repeat: On T1, say: create table t (s1 int, primary key (s1)) engine=falcon; set @@autocommit=0; insert into t values (0); commit; set transaction isolation level serializable; start transaction; On T2, say: set @@autocommit=0; commit; set transaction isolation level serializable; start transaction; insert into t select max(s1)+1 from t; On T1, say: delete from t; commit; On T2, say: commit; On T1, say: select * from t; The final result is: mysql> select * from t; +----+ | s1 | +----+ | 1 | +----+ 1 row in set (0.00 sec)