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