In this post, we will be discussing about transaction and ACID properties in dbms. What are ACID properties (Atomicity, Consistency, Isolation, Durability)? Why do we need these properties? etc.

Transaction (Getting Started)

A transaction is a program or logical unit (or, series of actions) whose execution may change the contents of a database.

If the database was in the consistent state before a transaction, then on completion of the execution of the program unit corresponding to the transaction, the database will be in a consistent state. This requires that the transactions must be considered atomic. It is executed successfully or in case of errors, the user can view the transaction as not having been executed at all.

Let’s take an example of simple transaction where we need to increase salary of an employee by 1.1 times. We can write queries as below –

READ (empid =102, salary);
Salary=salary*1.1;
WRITE (empid=102, salary);

• In the above transaction, we have used two database queries i.e. READ and WRITE and a non-database query i.e. Salary=salary*1.1;
• The transaction is said to be completed successfully if the salary is updated successfully.
• Else it can be aborted and rolled back.
• Status of a transaction and information about its action is not visible from outside of the transaction until it terminates.
• Any notification of what a transaction is doing is not communicated to any terminal until the it is committed.
• Once a transaction terminates, the user may be notified of its success or failure.
• The database operations that form a transaction can be embedded within an application program or can be specified interactively via a high level query language such as SQL.

ACID Properties in DBMS

Transactions should follow some properties to prevail integrity and quality in database. These properties are generally called as ACID properties which are enforced by the concurrency and recovery techniques of database.

The ACID properties are as below:

As above image depicts, there are 4 ACID properties in DBMS –

1. A – Atomicity: This property ensures that either all actions in a transaction takes place or none at all.
2. C – Consistency: This property ensures that database should be consistent before and after transaction.
3. I – Isolation: This property ensures that multiple concurrent transactions should not effect each other.
4. D – Durability:  This property ensures that committed changes in dbms should persists even if there are system failure.

Let’s see each property one by one in detail –

A – Atomicity

It is an all or none property. It means this property ensures that transaction is either performed entirely or not performed at all. For example,
Assume X have Rs. 1000 and Y have Rs. 2000. For some reason, X gives Rs. 100 to Y.
Can you imagine what should be the transaction running in dbms?
It should be like below –

Here,
Transaction related to X is T1 and that of Y is T2.

T1 and T2 together create transaction T.

What if transaction T1 is complete, but T2 is not?

Can you say  X has given Rs. 100 to Y ?

Definitely NOT!

That’s why we need to make sure both transaction T1 and T2 are completed successfully. Then only we can say that transaction T is complete.
The property which makes sure all actions are successfully done is called Atomicity. If any action within transaction is not complete, then completed actions are reverted back.

 Points to remember:

• It is the responsibility of the Recovery System (or Recovery Manager) of the DBMS to ensure atomicity.
• If a transaction fails to complete successfully for some reasons, the recovery technique must undo all the effects of the transaction on the database.

C – Consistency

What do we mean by consistency in dbms? Why do we need consistency in dbms?

Consistency refers to the correctness of the database. It means database should be consistent before and after the transaction. Considering example used in atomicity –
Before transaction,
X and Y have Rs. 1000 and Rs. 2000 respectively. So, total amount is Rs. 3000.
Now, consistency makes sure that total amount remains Rs. 3000 even after transaction.
After transaction,
X and Y have Rs. 900 and Rs. 2100 respectively. So, total amount still remains Rs. 3000.

So, System remains consistent before and after transaction.

Points to remember:

• It is the constraint satisfying property which takes the database from one consistent state to another. This property ensures that DBMS satisfies all the constraints available in the database.
• It is the responsibility of the programmer who writes the database programs and enforces integrity constraints on the DBMS.
• The programmer ensures that database is in consistent state i.e. whether the database satisfies that constraints specified in the schema and the database constraints which are unavoidable.

I – Isolation

Isolation means multiple concurrent transactions can execute in the system without effecting each other. Changes in a particular transaction won’t be visible to other transactions until those changes in that transaction are not committed to the system. Final result after transaction will be same as if they were executed serially in same order. For example,

Let X = 1000, Y = 2000
Here, we have two transactions T1 and T2 running concurrently. Now, let’s assume T1 has been executed till Read(Y), then T2 starts executing.

What is value of Y when T2 starts executing ?
– It is Rs. 2000

Now,

Z = X + Y

=> Z = 10000 + 2000
=> 12000

But, is this correct value of Z ?

What if T2 starts after T1 has been executed successfully ?

Value of Y, after T1 is complete, is Rs. 1900.

So, value of Z = X + Y will be –
=> Z = 10000 + 1900
=> 11900

Definitely 12000 is not equal to 11900. So, our database is not consistent before and after transaction. So, transaction must take place in isolation. Also, changes should be visible only after they have been made to the main memory.

So,
Isolation is the non-interference property. Even if there are n number of transactions running concurrently, they should be independent on each other i.e. they shouldn’t affect each other. Even if one of the transaction aborts or rollbacks, it shouldn’t affect other.

Points to remember:

• Execution of one transaction should not be interfered with the other transaction running concurrently.
• It is the responsibility of the concurrency control mechanism to ensure isolation and this is achieved by hiding the updates made by a transaction until it is committed.
• The concurrency control mechanism ensures that updates made by transaction T1 are not visible to other transactions during the process of the transaction T1 until transaction T1 is completed successfully.
• Isolation provides results of the concurrent transaction which are similar to serial transaction.

D – Durability

This property ensures that changes made to the database by the committed transaction should persist in the database. These changes shouldn’t be lost if any system failure occurs.

If transaction is committed, the database should be updated with changes made and shouldn’t be lost in any situation.

• It is the responsibility of the recovery system of the DBMS to ensure durability.

Thus, we went through ACID properties in DBMS. ACID properties are Atomicity, Consistency, Isolation and Durability.