Isolation Anomalies, pt. 2

Covers 3 new anomalies proposed by A Critique of ANSI SQL Isolation Levels - Microsoft.

P4 (Lost Update)

*R1[x]...W2[x]...W1[x]...C1*

This happens when a transaction T1 reads a row ‘x’ and then T2 updates the row ‘x’ (possibly based on a previous read), then T1 (based on its earlier read value) updates the row and commits.

Here, the data written by T2 will be ‘lost’.

It might seem similar to P0 (dirty write), but it’s not. The difference is that T1 has to read the value first & commit this time (the order relative to commit of T2 doesn’t matter).

Example: r1[x=100] r2[x=100] w2[x=120] c2 w1[x=130] c1

T1 and T2 both read x as 100. T2 updates x to 130 & commits, T1 then updates x to 130 & commits. Value written by T2 is essentially ‘lost’.

It’s easy to confuse this with TOCTTOU, which is a similar but broader concept I’d rather call a race condition than just a database isolation problem. It may happen even with a serializable database if you don’t pay attention to properly using database transactions.

Now let’s talk about two anomalies arising from a database constraint between two rows x and y.

A5A Read Skew

r1[x]...w2[x]...w2[y]...c2...r1[y]...(c1 or a1)

Suppose transaction T1 reads x, and then a second transaction T2 updates x and y to new values and commits. If now T1 reads y, it may see an inconsistent state, and therefore produce an inconsistent state as output.

Example:  (x+y=100) r1[x=50] w2[x=10] w2[y=90] c2 r1[y=90] c1

Let’s suppose we are dealing with a table for joint bank accounts where each row is the individual account for the joint account holder. The database constraint is that at all times, the sum of x+y must be equal to 100.

The values of  x & y before start of any transaction is 50 each, so the sum is 100. Now T1 reads value of x as 50, then T2 transfers 40 from x to y and commits. Now T1 reads the value of y, which comes as 90, thus appearing to T1 that the db constraint is violated as 50 + 90 = 140.

A5B Write Skew

r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2)

Suppose T1 reads x and y, which are consistent with some database constraint, and then a T2 reads x and y, writes x, and commits. Then T1 writes y. If there were a constraint between x and y, it might be violated.

Example:  (x+y>=0) r1[x=50] r1[y=50] r2[x=50] r2[y=50] w1[y=-40] w2[x=-40] c1 c2

Same table as in the example for A5A, the database constraint this time is that at all times, x+y must be non-negative, even if any of the individual accounts are negative.

Now T1 reads value of x as 50, then T2 reads the value of y as 50. Now both deduct 90 from y & x respectively, thinking they are safely adhering to the database constraints. Once both T1 & T2 have committed, the database reads x+y = -80, violating the constraint.