Saturday, 2 April 2016

Consistency property of ACID properties

ACID Properties, Properties of Database Transactions, Atomicity Explained, Example for Atomic transactions

Consistency Property - ACID Properties

Let us consider a transaction T1 through which you want to transfer 2000 from your account ‘A’ (source account) to your friend’s account ‘B’ (destination account). Assume that the account balance of A is 10000 and B is 14000. This transaction can be represented as follows;

Instructions in Transaction T1
Step 0: Begin_Transaction
The starting point of the current transaction.
T1 started. At this stage A = 10000 and B = 14000.
Old consistent state
Step 1: Read (A);
Read the current balance of A from database.
A’s current balance is 10000.
Step 2: A := A – 2000;
Deduce the amount to be transferred from the source account.
A := 10000 – 2000;
Step 3: Write (A);
Write the new value of A into database.
A’s new balance is 8000.
Step 4: Read (B);
Read the current balance of B from database.
B’s current balance is 14000.
Step 5: B := B + 2000;
Add the transferred amount (amount deduced from A) to destination account B.
B := 14000 + 2000;
Step 6: Write (B);
Write the new value of B into database
B’s new balance is 16000.
Step 7: Commit;
Permanently write values of A and B into database.
At the end of the transaction,
A = 8000 and B = 16000.
New consistent state
Table: Transaction T1

From the above example, we would know that if the transaction executed the instructions in Step 0 to Step 7, then we would say that this transaction T1 is successfully completed. All these steps have to be executed successfully, safely and without any other external intervention. To ensure this we use to check the transaction properties.

Consistency – This property ensures that whether the old values of all the data items of a transaction is successfully transformed into new values in the exact manner as it is specified in the instructions of that transaction.

In other words, a transaction should transform a database from one consistent state to a new one. This would happen only if all the instructions are executed without any interference from other transactions.

For example, the old values of A and B are 10000 and 14000 respectively. The instructions or calculations that change A and B values are A := A-2000 and B := B+2000. If these statements are executed successfully and there was no other transactions that change A or B during the execution of T1, then we would see the new values of A and B as 8000 and 16000.


Go back to ACID properties home page

Go to Atomicity

Go to Isolation

Go to Durability


No comments:

Post a Comment

SQL exercises for beginners one

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