Read Skew and Snapshot Isolation

Introduction

Running databases in read committed can sometimes cause an issue called nonrepeatable read or read skew. Consider the case of a crypto user who holds one bitcoin each in two online accounts A and B. She schedules a transfer of the sole bitcoin in her account A to account B, so that after the transfer is completed account A holds 0 bitcoins and account B holds 2 bitcoins. The following timeline of events shows how the user can end up seeing only 1 bitcoin in her portfolio instead of 2.

1. The read transaction to report the total number of bitcoins held by the user across the two accounts initiates.

2. The read transaction queries the balance of account B and is returned one.

3. The write transaction that actually performs the transfer initiates.

4. The write transaction updates the balance in account B to two bitcoins and in account A to zero bitcoins.

5. The write transaction commits.

6. The read transaction now reads the balance in account A which is zero bitcoins.

7. The user is reported an aggregate balance of the sum of the two balances in the two accounts which equals one. Note that the read transaction reads values that had been committed, i.e. there was no dirty read. In other words, the user is observing the database in an incosistent state.

8. If the user reloads her browser and she’ll see the correct bitcoin balance as two.