Write skew anmalies are found in SERIALIZABLE isolation

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Write skew anmalies are found in SERIALIZABLE isolation

张舒燕

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  



Test2.java (4K) Download Attachment
Test1.java (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Write skew anmalies are found in SERIALIZABLE isolation

Peter Geoghegan-4
On Thu, Oct 3, 2019 at 9:18 AM 张舒燕 <[hidden email]> wrote:
> When we set the isolation level to SERIALIZABLE, we observed some wirte skew anmalies.

What version of PostgreSQL did you test? Does this bug affect all versions?

--
Peter Geoghegan


Reply | Threaded
Open this post in threaded view
|

Re: Write skew anmalies are found in SERIALIZABLE isolation

张舒燕
We have tested versions of 9.4.0, 9.6.0, 10.0, 11.0 and 11.5 and found the Bug on all of them.

We have wrong statements in the last email.
"If each transaction only read one item with secondary index and write another item with primary index, write skew anomalies are more ( -> less) likely to occur."
"At last, we run the same join SQL, and more ( -> less) anomalies can be found. The corresponding test program is Test2.java."
There are less anomalies can be found in the second situation using Test2.java.

And some mistakes in our test programs have been fixed.

Best regards ,
Shuyan Zhang, ECNU
 
------------------ Original ------------------
Date:  Fri, Oct 4, 2019 05:05 AM
To:  "张舒燕"<[hidden email]>;
Cc:  "pgsql-bugs"<[hidden email]>;
Subject:  Re: Write skew anmalies are found in SERIALIZABLE isolation
 
On Thu, Oct 3, 2019 at 9:18 AM 张舒燕 <[hidden email]> wrote:
> When we set the isolation level to SERIALIZABLE, we observed some wirte skew anmalies.

What version of PostgreSQL did you test? Does this bug affect all versions?

--
Peter Geoghegan

Test1.java (4K) Download Attachment
Test2.java (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Write skew anmalies are found in SERIALIZABLE isolation

konstantin knizhnik
On 04.10.2019 4:36, 张舒燕 wrote:
We have tested versions of 9.4.0, 9.6.0, 10.0, 11.0 and 11.5 and found the Bug on all of them.

We have wrong statements in the last email.
"If each transaction only read one item with secondary index and write another item with primary index, write skew anomalies are more ( -> less) likely to occur."
"At last, we run the same join SQL, and more ( -> less) anomalies can be found. The corresponding test program is Test2.java."
There are less anomalies can be found in the second situation using Test2.java.

And some mistakes in our test programs have been fixed.

Best regards ,
Shuyan Zhang, ECNU

I do not think that this problem is somehow related with "write skew anomalies".
The type of "col" is decimal, but you are extracting it as "double", making conversion and loosing precision.
Then you perform some manipulations using Java BigDecimal class, convert result to string (which also does some rounding) and subtract this value from the column.
As a result of this conversions  your original  assumption that (x+y)*0.9 < (x+y) may be wrong.
Please try to reproduce the problem using integer type  and I am almost sure that you will not able to get this anomalies.


 
------------------ Original ------------------
Date:  Fri, Oct 4, 2019 05:05 AM
To:  "张舒燕"[hidden email];
Cc:  "pgsql-bugs"[hidden email];
Subject:  Re: Write skew anmalies are found in SERIALIZABLE isolation
 
On Thu, Oct 3, 2019 at 9:18 AM 张舒燕 [hidden email] wrote:
> When we set the isolation level to SERIALIZABLE, we observed some wirte skew anmalies.

What version of PostgreSQL did you test? Does this bug affect all versions?

--
Peter Geoghegan

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company