# 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.

T1 | T2 |
---|---|

BEGIN | |

R(x) | |

BEGIN | |

W(x) | |

W(x) | |

COMMIT | |

COMMIT |

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.