ANSI SQL G2 Anti dependency anomaly when using transactions isolation level set to snapshot table stability

[AniruddhaAchar]

Issue:
G2 anti dependency anomaly detected with Firebird 4.0 when transactions are executed under snapshot table stability isolation level. Transactions are not serializable.

Firebird version used: 4.0
Client used: isql

Steps to reproduce

Table setup:

CREATE TABLE main(key varchar(99),value_store varchar(99),second_key varchar(99),PRIMARY KEY (key));
CREATE TABLE second_table(key varchar(99),value_store varchar(99),second_key varchar(99),PRIMARY KEY (key));

Start three instances of isql. Let's call them C1, C2 and C3. All commands with suffix C1 are run on client 1 and C2 on 2 and so on. The order in which the steps are presented would be the order of execution of the commands.

C1: SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT TABLE STABILITY;
C1: INSERT INTO second_table (key,second_key,value_store) VALUES ('1', '1','1');
C2: SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT TABLE STABILITY;
C2: DELETE FROM main WHERE key ='5';
C2: INSERT INTO main (key,second_key,value_store) VALUES ('5', '5', '10');
C2: COMMIT;
C3: SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT TABLE STABILITY;
C3: Select value_store from main where key = '5'; -> Result 10
C1: Select * from main where second_key <= '5'; -> Result
C1: COMMIT;
C3: select value_store from second_table where key = '1'; -> Result
C3: COMMIT;

The history we see above is not serializable since there is no permutation of transactions that will allow the above results to be seen. Reading documentation from ibphoenix, Snapshot Table Stability (or Consistency) is serializable. Is this document accurate?

[hvlad]

C3: Select value_store from main where key = '5'; -> Result 10

How it can be possible on empty table ?
Please, provide complete steps to reproduce the issue.

[AniruddhaAchar]

C3: Select value_store from main where key = '5'; -> Result 10

How it can be possible on empty table ? Please, provide complete steps to reproduce the issue.

Sorry. Edited the reproduction setps. I missed the insert.

[hvlad]

I still see no anomaly, could you explain more ?

[hvlad]

C1: SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT TABLE STABILITY;
C1: INSERT INTO second_table (key,second_key,value_store) VALUES ('1', '1','1');

C2: SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT TABLE STABILITY;
C2: DELETE FROM main WHERE key ='5';
C2: INSERT INTO main (key,second_key,value_store) VALUES ('5', '5', '10');
C2: COMMIT;

C3: SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL SNAPSHOT TABLE STABILITY;
C3: Select value_store from main where key = '5'; -> Result 10

Ok, as C2 was committed before C3 started

C1: Select * from main where second_key <= '5'; -> Result

Ok, as C2 was committed after C1 started and C1 can't see changes by C2

C1: COMMIT;

C3: select value_store from second_table where key = '1'; -> Result

Ok, as C1 was committed after C3 started and C3 can't see changes by C1

C3: COMMIT;

So, what anomaly you see ?

PS I found explanation of G2 anomaly here
https://blog.acolyer.org/2016/02/25/generalized-isolation-level-definitions/
and it is very different from sampe above.
At first look it is impossible in Firebird even with CONCURRENCY isolation level.

[aafemt]

AFAIU he assumes that "serializable" == "produce the same result as if all transactions are executed strictly one after another" so C3 should see values inserted by C1. From this point of view IBPhoenix document is indeed wrong, there is no way to achieve such serialization with any TIL.

[AniruddhaAchar]

AFAIU he assumes that "serializable" == "produce the same result as if all transactions are executed strictly one after another" so C3 should see values inserted by C1. From this point of view IBPhoenix document is indeed wrong, there is no way to achieve such serialization with any TIL.

This is indeed my assumption. If such serialization cannot be achieved, would it be fair to request for this to be added to the documentation? Why I request this is because .Net and Java libraries provide developers with Serializable isolation level as an option when executing transactions and if developers use them with the same expectation as "serializable" == "produce the same result as if all transactions are executed strictly one after another", they will see anomalies that I presented here. I understand that I may have to open issues in the .Net and Java libraries to document this. A note here would help with the requests on the .Net and JDBC repositories.

[aafemt]

Firebird itself doesn't provide TIL named "serializable" so this ticket is plainly invalid and you indeed should complain to .NET and Java driver developers but I'm afraid that the only way to solve this problem for them is to stop providing "serializable" level completely which perhaps is not what you want.