Database Concurrency Anomalies

Applications nowadays handle a tremendous amount of data, with single or multiple instances of the applications, fronting them with load balancers.

Sometimes businesses need to run a set of logical operations that have to be performed in a user session as a single piece of work, which is called a Transaction.

Database transactions are defined by the four properties known as ACID. This property in totality, provides a mechanism to ensure the correctness and consistency of a database in a way such that each transaction is a group of operations that acts as a single unit, produces consistent results, acts in isolation from other operations, and updates that it makes are durably stored.

There occurs the requirement of using the database by multiple users for performing different operations, and in that case, concurrent execution of the database is performed. The Isolation Level allows you to trade off data integrity for performance. It introduces a set of problems to which different transaction isolation (level) tend to respond. So the weaker the isolation level, the more anomalies can occur. Following are various phenomenon anomalies can occur.

Dirty Read

Ex: Cesar at ATM, needs to withdraw an amount of 2000/-. He enters the amount, and the balance gets updated as 3000/-. At the same time Transaction 2 starts, loan calculation for the account. It reads the balance as 3000 and starts the calculation. But somehow, the withdrawal at the Atm gets roll backed, so the balance is reverted to original balance as 5000. Here the loan calculation which committed was based on the balance 3000 instead of 5000. The calculation amount will be incorrect.

To prevent dirty reads, the database engine must hide uncommitted changes from all other concurrent transactions. Each transaction is allowed to see its own changes because otherwise the read-your-own-writes consistency guarantee is compromised. If the underlying database uses 2PL (Two-Phase Locking), the uncommitted rows are protected by write locks which prevent other concurrent transactions from reading these records until they are committed.

Normally, the Read Uncommitted isolation level is rarely needed (non-strict reporting queries where dirty reads are acceptable), so Read Committed is usually the lowest practical isolation level.

Lost Update

A lost update problem occurs in concurrent transactions, when two transactions try to read and update the same column on the same row within a database at the same time making the values of the items incorrect thus making the database inconsistent.

Ex: In the above scenario, there exists two concurrent transactions trying to debit Alice’s account with 20$ and 50$. Once both the transactions are completed, Alice’s account is supposed to have balance as 30$. But here, the user2 in the second transaction reads only the committed data, its unaware the first concurrent transaction has taken place. As a result the first transaction gets overlooked with the second transaction and thus the final balance gets inaccurate.

The transactions are run under the Read committed isolation level which is default isolation level for SQL Server that’s why it got the Lost Update problem. The transactions should be run under any of the higher isolation levels such as REPEATABLE_READ, Snapshot, or Serializable.

Non-repeatable Read

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

Once an object is loaded into memory, there is no need to fetch it from the database each time a member is accessed.

Ex: Suppose in Transaction1, User reads the balance in Bob’s account. In the meantime, Transaction2 starts and another user updates Bob’s account with 250$. If the user in transaction 1 again reads the balance for Bob’s account, it retrieves different row values from the first retrieval.

Non-repeatable Reads are prevented with the REPEATABLE_READ transaction isolation level.

Phantom Reads

If the same query is executed twice during a transaction, but the set of rows differ, a phantom read happened. The excluded rows are the phantoms.

Ex: Transaction T1 reads a set of data items satisfying some search condition. Transaction T2 then creates data items that satisfy T1’s search condition and commits. If T1 then repeats it’s read with the same search condition, it gets a set of data items different from the first read.

Phantom Reads are prevented with the SERIALIZABLE isolation level according to the ANSI SQL-92 standard.

Summary

Here is an overview of which transaction isolation levels prevent which read phenomena: