Transactions

In database systems, transactions denote a boundary within which a certain number of tasks are performed as a single logical unit. At the completion of a transaction, either all tasks are successful or all tasks are not successful.

Traditionally transactions are said to be ACID in nature. Below is the list of ACID properties which define a transaction.

1. Atomicity:

Atomicity ensures that each operation is carried out in its entirety or is not performed at all and the operation cannot be done partially. This, all or nothing approach is essential to ensure that a transaction either succeeds or fails. For example, transferring of money from one account to another consists of two steps namely, deducting amount from source and adding amount to target account. Both of these operations will be part of a transaction, and both operations should succeed for the transaction to complete.

2. Consistency:

Consistency ensures that at the end of each operation, the system is left in a valid, consistent state. i.e. the changes conform to all the rules, constraints and triggers etc. which are put in place in the system. For example, if creation of account is an operation, then there could be a database rule which specifies that the account number be unique. At the end of the transaction, this rule should be satisfied in totality.

3. Isolation:

Isolation ensures that each transaction is executed in its own execution context and is not impacted in any way by any other transactions running concurrently. This way, different transactions do not step on each other. Isolation levels provide control over transaction visibility and it affects the integrity of a transaction in various scenarios. There are four Isolation levels which could be chosen in a database system, depending on the use case:

• Read Uncommitted:
• Read Committed
• Repeatable Read
• Serializable

Before explaining these Isolation levels, let us take a look at few related concepts:

1. Dirty read:

Dirty read happens when one transaction is allowed to read uncommitted changes from another transaction. It is called as dirty read, since the changes are not permanent and if that transaction is rolled back, then those changes are of no value. For example, in the money transfer scenario, at the point of time, when the money is deducted from the source, but not yet added to the target account, the account balance in the source account should not reflect the deducted money. Because, there is a chance that the second operation might fail and transaction could be rolled back.

2. Non Repeatable read:

A non-repeatable read happens when multiple reads to a data or a record, results in differing data within the returned collection of results. This happens when another transaction is updating the data in between the reads. So we are actually looking at stale data in between updates. For example, while browsing through a list of articles for sale in an online application, it is possible that the article details are updated as we browse and the description which is seen a few moments back could have been updated when a moment later.

3. Phantom read:

A phantom read happens when multiple reads to a data or a record, results in different collection of results. Unlike dirty read, here the size of the collection returned itself might vary, since new records would have been inserted by other transactions. For example, in a shopping cart application, while browsing through the available products in stock, the products might disappear (or new products appear) as we navigate back and forth. This happens, since other users could be ordering those products at the same time (or new products could be added into stock). Phantom read could be an unavoidable scenario and might be a desirable behavior most of the times.

The below table shows how do the Isolation levels impact the read related issues.

4. Durability:

Durability ensures that once a transaction is committed, the data is persisted permanently guarding against power loss, crashes, network failures etc.

Note:

Serializable is the highest level of Isolation level which provides very strict control, but it severely affects performance. Read Committed is generally considered a reasonable Isolation level.