DBMS Notes

Transaction Properties

  • The transaction has the four properties which ensure that the database remains consistent, accuracy, completeness, and data integrity, before and after the transaction execution.
  • These properties are called as ACID Properties of a transaction.

ACID Properties

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability

Above Mention properties are commonly known as ACID properties. Let explain all terms one by one

1. Atomicity:

  • This property states that either all of its operations are executed or none.
  • Execution of a transaction should be start from first-step (fetch) and end with last-step (Commit). There should be no abortion or failure in-between the execution of any atomicity transaction.
  • If there is any failure or abortion even at point 99.9 out of 100 then there must be Rollback.
  • Rollback, eliminate all previous execution and transfer the control from start of execution where it restart the transaction

Let explain with example

If we want to transfer the money from one account (Account “A”) to other account (Account “B”) then a transaction will be

Transaction = (Debit account “A”) + (Credit Account “B”)

In the following diagram, we can be seen that

  • If there is no atomicity then debiting the account “A” with Rs.1000 does not credit the Account “B”.
  • If there is an atomicity then debiting the account “A” with Rs.1000 will credit the Account “B” with Rs.1000.

2. Consistency:

The database will be in consistent state if

“Sum of balance, before and after the transaction execution, is same”.   

Let explain with example

Suppose SUM1 is balance of two accounts before to transaction and SUM2 is balance after the transaction.

The condition for consistency is SUM1 Must be equal to SUM2

Balance before transaction

  • Sum1 = Account A + Account B

Balance after the transaction

  • Sum2 = Account A + Account B

Now, suppose Account A has 1500 and transfer 1000 to Account B which already contain 3000, let execute

Balance before transaction

  • Sum1 = 1500 + 3000 =4500

Balance after the transaction

  • Sum2 = 500 + 4000 = 4500

Hence it proved that SUM1=SUM2

Note: if account debited and money not received then it should be in consistently. To resolve this, again Rollback is used

3. Isolation:

The term ‘isolation’ means separation. In Isolation, the data of one transaction should not affect by other transaction.

Conversion of parallel schedule to serial schedule is called isolation. Serial schedule is always consistent.

Parallel Vs. Serial Schedules

Parallel Schedule: it means performing of more than one transaction at a time

Serial Schedule: it means performing only one transaction at a time. After completion of one transaction than other should be performed.

Example: In the following diagram, Account “A” is making two transactions T1 and T2 to account B and C. These both transactions T1 and T2 are executing independently without affecting each other. It is an isolation example.

IMPORTANT NOTE:

If two operations are running parallel on two different accounts or databases, then the value of both accounts should not get affected by each other.

If two operations are running parallel on same accounts, then the value of both accounts can get affected by each other. To resolve this problem make transaction serial.

4. Durability:

Durability ensures the permanency of something.

The database should be durable. It means, when the database is updated after transaction then it should holds the permanent changes in database. Permanent changes means, these modified information’s does not change automatically after sometime until unless other transaction has to perform some actions.

Note: Commit command is use to store or update the data permanently.

Help Other’s By Sharing…

Contact Us

Burewala, Vehari, Punjab, Pakistan

cstaleem1@gmail.com

Website: CStaleem.com

Pin It on Pinterest