Tuesday, 27 May 2014

ACID Properties in DBMS Explained



What are ACID properties? / Why ACID properties must be held by a transaction? / ACID properties explained with  example / What would be the problems with transactions without ACID properties?

ACID Properties

ACID properties are set of properties that guarantee a reliable, error free and consistent database transaction. It means a safer transaction. [Click on the following links Atomicity, Consistency, Isolation, Durability to know more]


It refers to “all or nothing”. That means, in a transaction every instruction is executed and the changes (specified through instructions) are made successfully in the database.
It refers to a consistent database after execution. The consistent state may be old or new.
All transactions that are executed simultaneously need to ensure the individuality. That is, the resultant state of database, after the simultaneous execution of several transactions should be equivalent to the serial execution of all the transactions.
It refers to the persistence of the changes made in the database. That is, after the successful completion of the transaction, the system must store the changes permanently in the hard disk.
 


Example:
Let us assume that a transaction T1 transfers 5000 from, say account A to account B. Also assume that the initial balance of A is 10000 and B is 10000. Transaction T1 shown below depicts the actual implementation of transfer of money from one account to the other;

Transaction T1
1. Read (A)
2. A:=A-5000;
3. Write(A)
4. Read(B)
5. B:=B+5000;
6. Write(B)
 Figure 1 - Transaction T1


Figure 2 - Transaction States


The table shown above clearly shows the instructions that are executed to complete the transaction T1. At first, T1 reads the value of A and perform subtraction (the amount that is to be transferred to B must be deduced from A). Then T1 writes the new value of A into the table. Next, T1 reads B, perform an addition, and writes the new value of A into the table. Hence, at the end of successful completion of T1, we will have 5000 in account A and 15000 in account B as shown in Figure 2.
Let us assume that system which executes the transaction T1 crashes exactly after the instruction ‘Write (A)’. After the crash, when the system recovers, we would find a new value of A, i.e, 5000. But, the value of B will be 10000, i.e, B is untouched. The reason is that the amount deduced from A is not added with B. This is an unwanted situation.
To get rid of the above situation, we need to ensure that every transaction is reliable. The ACID properties ensure this reliability for every transaction.

Atomicity ensures that either T1 can enter into the new consistent state S2 after successful completion of transaction or T1 can remain in the old consistent state S1 without affecting A and B.

Consistency ensures that the changes made are legal. That is, either S1 or S2, both should be valid states. It can be evaluated in many ways. For example, for our transaction T1, the sum of A and B, before and after the transaction will be same.
At S1 (Before transaction), A + B = 10000 + 10000 = 20000
At S2 (After transaction), A + B = 5000 + 15000 = 20000

Isolation: Assume that there is one more transaction T2 executed simultaneously along with T1 as given in Figure 3; also assume that the initial balance of account C is 5000.




Transaction T1
Transaction T2
1. Read (A)
2. A:=A-5000;
3. Write(A)
4. Read(B)
5. B:=B+5000;
6. Write(B)
1. Read (A)
2. A:=A-2000;
3. Write(A)
4. Read(C)
5. C:=C+2000;
6. Write(C)
  Figure 3 - Transaction T1 and T2 (Concurrent Execution)



T1 transfers 5000 from account A to B, and T2 transfers 2000 from account A to C. Instruction 3 in both transactions write new value of A into the database. If both transactions read A at the same time, then the Write which happened later would decide the new value of A. that is, T1 writes new value of A after T2 writes A, then the new value of A would be 5000. If T2 writes A after T1 has written, then the new value of A would be 8000. Both of these values are inconsistent. In a consistent transaction execution, at the end of the transactions, we need A with balance 3000, B with 15000, and C with 7000. Hence, these two transactions should be executed in isolated manner. Isolation property ensures the serial execution of transaction and leads to consistent databases.

Durability ensures that at the end of every successful transaction the data must be written permanently into the hard disk. That is, at the end the table should have new consistent values of A, B, and C in the hard disk.


************************


Go back to Transactions Management in DBMS home page 








SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...