Anomalies

An anomaly is an inconsistency between different parts in the data. There are a number of different types with different causes.

Table 1. Anomalies

Anomaly

Description

Dirty Read

A Dirty Read occurs when one transaction reads a value that has been written by another still in-flight transaction. It does not matter if the value is finally committed or rolled back, it is still a dirty read.

Dirty Write

A Dirty Write occurs when one transaction overwrites a value that has previously been written by another still in-flight transaction.

Lost Update

Lost Update relates to concurrent reads and updates to data, in a system where readers do not block writers, so that writing is done based on reading data that is already being modified.

Non-Repeatable (Fuzzy) Read

Non-repeatable reads happen when a query returns data that would be different if the query were repeated within the same transaction. Non-repeatable reads can occur when other transactions are modifying data that a transaction is reading.

Phantom read

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

Read Skew

Read skew can occur when there are integrity constraints between two or more data items. When the transaction reads one item while the constrained item is modified, the transaction will see an inconsistent state, and generate the wrong output.

Write Skew

Write skew occurs when constrained items at least one safety feature for a system is disregarded, due to write-write synchronization problems, which causes Snapshot Isolation not to be serializable.

Write skew in detail

Consider two transactions, P and Q. P copies the value in a register x to y, and Q copies the value in a register y to x. There are only two serial executions of these two, P, Q or Q, P. In either, the end result is that x = y. However, Snapshot Isolation allows for another outcome:

  • Transaction 1 reads x

  • Transaction 2 reads y

  • Transaction 1 writes the value it read to y

  • Transaction 2 writes the value it read to x

In Snapshot Isolation this is still valid - both transaction were consistent and the writes didn’t overlap - but there is still an error: x and y have been swapped.

LeanXcale anomaly handling

The Snapshot Isolation criterion avoids all anomalies described in the standard.

For the write skew anomaly, LeanXcale provides specific SELECT FOR UPDATE semantics.

This means that when a SELECT is launched, all conflict managers are informed about rows involved in the SELECT.

Any other transactions are considered in conflict and are prevented from modifying these rows.

This prevents any write skew operation from happening.