When we set the isolation level to SERIALIZABLE, we observed some wirte skew anmalies.
In order to simplify the problem, suppose there are only two items, i.e., X and Y, which are initialized with positive values. The first kind of transactions read X and Y, then write X = X - (X+Y) * 0.9. The second kind of transactions read X and Y, then write Y = Y - (X+Y) * 0.9. These transactions execute concurrently. If serializability is guaranteed, the value of (X+Y) must be a positive number.
In our test, there are two tables, i.e., xtable and ytable. The values in these two tables are all positive. Each table has 50 tuples. In our experiments, there are 50 test threads where each thread establishes a separate database connection.
Transaction 1:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
X ß select xcol from xtable where id= 3;
Y ß select ycol from ytable where id=3;
update ytable set ycol = ycol-(X+Y) * 0.9 where id =3;
COMMIT
Transaction 2:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
X ß select xcol from xtable where id=3;
Y ß select ycol from ytable where id=3;
update xtable set xcol = xcol-(X+Y) * 0.9 where id =3;
COMMIT
At last, we run: select xtable.id from xtable join ytable using (id) where xtable.xcol + ytable.ycol < 0. Sometimes the result set is not empty, which means the serializability is violated. We observe this issue with Test1.java.
If each transaction only read one item with secondary index and write another item with primary index, write skew anomalies are more likely to occur.
Transaction 1:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
X ß select xcol from xtable where id=3;
update ytable set ycol = ycol-ycol * 0.9 - X * 0.9 where id =3;
COMMIT
Transaction 2:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Y ß select ycol from ytable where id=3;
update xtable set xcol = xcol-xcol * 0.9 - Y * 0.9 where id =3;
COMMIT
At last, we run the same join SQL, and more anomalies can be found. The corresponding test program is Test2.java.
If you need any additional information, please tell us.
Bset regards,
Shuyan Zhang, ECNU