Transaction Properties

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

ACID Properties

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

The above-mentioned properties are commonly known as ACID properties. Let’s explain all the terms one by one

1. Atomicity:

  • This property states that either all of its operations are executed or none.
  • Execution of a transaction should start from the first step (fetch) and end with the 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 a Rollback.
  • Rollback, eliminate all previous execution and transfer the control from the start of execution, where it restarts the transaction.

Let’s explain with an example.

If we want to transfer the money from one account (Account “A”) to another 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 account “A” with Rs.1000 does not credit Account “B”.
  • If there is an atomicity, debiting the account “A” with Rs.1000 will credit the Account “B” with Rs.1000.

2. Consistency:

The database will be in a consistent state if

The sum of the balance, before and after the transaction execution, is the same.   

Let’s explain with an example

Suppose SUM1 is the balance of two accounts before the transaction and SUM2 is the 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 transfers 1000 to Account B, which already contains 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 the account is debited and money is 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 be affected by other transactions.

The conversion of a parallel schedule to a serial schedule is called isolation. The serial schedule is always consistent.

Parallel Vs. Serial Schedules

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

Example: In the following diagram, Account “A” is making two transactions, T1 and T2, to account B and C. These 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 be affected by each other.
  • If two operations are running parallel on the same accounts, then the value of both accounts can be affected by each other. To resolve this problem, make a transaction serial.

4. Durability:

Durability ensures the permanency of something.

The database should be durable. It means when the database is updated after a transaction, it should hold the permanent changes in the database. Permanent changes mean the modified information does not change automatically after some time unless another transaction has to perform some actions.

Note: The commit command is used to store or update the data permanently.