ACID properties in dbms
Here are the ACID properties in DBMS:
What is Transaction:
A logical unit of work on the database. An action that is being performed by a user or system program, which reads or updates the contents of the database.
A → Atomicity:
The entire transaction takes place at a stretch or does not happen at all.
For example:
Input:
x: 500
y: 300
Transaction T
| T1 | T2 |
| Read(x) | Read(y) |
| x=x-100 | y=y+100 |
| write(x) | write(y) |
Output:
x:400
y:400
In the above example, atomicity is, if both t1 and t2 transaction takes place at a time. In case if the system fails after t1 and before t2. 100 rs is deducted in the x and no operation is performed on y. Thus there will be a 100 rs loss. so data inconsistency occurs.
C → Consistency:
Consistency means correctness. The integrity constraints must be maintained.
From the above example,
Before transaction:
Total = x+y = 500+300 = 800
After transaction:
Total = x+y=400+400 = 800
I → Isolation:
Multiple transactions will be concurrent without leading to the inconsistent state of the database. Here all the transactions occur independently. The changes occurring in one transaction will not be visible to other transactions until committed. Responsibility of concurrency control subsystem.
For example:
There are two transactions t1 and t2
x: 500
y:150
| T1 | T2 |
| Read(x) | |
| x:=x*100 | |
| write(x) | Read(x) |
| Read(y) | Read(y) |
| y:=y-50 | z:=x+y |
| write(y) | write(z) |
Initially, in t1 x is read and the x value becomes 5000. Then y is read and it is written as 100. In t2 the x will be read as 5000 and y as 150 so the z is written as 5150. But in the t1 y is written as 100. So data inconsistency occurs. If the transactions occur independently.
D →Durability:
Once the transaction is committed, the updates and changes to the database are stored and written to the disk. And they persist even there is a system failure.
The transactions effects are never lost. It is permanent.
For example:
Banking system failure
These properties are often called the ACID properties in DBMS.
Also, read Display a proof for the following theorems.