DBMS Transaction

DBMS Transaction

As a group of tasks, a transaction can be described. The minimum processing unit which can not be further divided is a single task.

Let's take an example of a transaction that is simple. Suppose Rs 500 is transferred from A's account to B's account by a bank employee. Several low-level tasks include this very simple and small transaction.

A's Account

Open_Account(A)
Old_Balance = A.balance
New_Balance = Old_Balance - 500
A.balance = New_Balance
Close_Account(A)

B's Account

Open_Account(B)
Old_Balance = B.balance
New_Balance = Old_Balance + 500
B.balance = New_Balance
Close_Account(B)

ACID Properties

A transaction is a very small program unit and can involve several low-level tasks. To ensure accuracy, completeness, and data integrity, a transaction in a database system must preserve atomicity, consistency, isolation, and durability, commonly known as ACID properties.

• Atomicity-This property states that an atomic unit must be viewed as a transaction, that is, either all or none of its operations are performed. In a database where a transaction is left partially completed, there must be no state. States should be specified either before the transaction is completed, or after the transaction is executed / aborted / failed.
• Consistency −After every transaction, the database must stay in a consistent state. No transaction should adversely affect the data residing in the database. If the database was in a stable state prior to a transaction being executed, it must still remain consistent after the transaction has been executed.
• Durability-Even if the system fails or restarts, the database should be durable enough to retain all the most recent updates. If a transaction updates and commits a chunk of data to a database, the modified data will be retained in the database. If a transaction is committed, but the system fails before the data can be written to the disk, until the system springs back into action, the data will be modified.
• Isolation-In a database system where more than one transaction is executed simultaneously and simultaneously, the isolation property states that all transactions would be executed and executed as if they were the only transaction in the system. The presence of any other transaction would not be affected by any transaction.

Serializability

When several transactions are executed in a multiprogramming environment by the operating system, there are possibilities for one transaction 's instructions to be interleaved with any other transaction.

• Schedule −A schedule is considered a chronological execution sequence of a transaction. There can be several transactions in a schedule, each comprising a set of instructions / tasks.
• Serial Schedule-It is a schedule in which transactions are aligned in a way that first executes one transaction. Then the next transaction is executed when the first transaction completes its cycle. One after the other, transactions are ordered. This type of schedule, as transactions are executed in a serial manner, is called a serial schedule.

Serial schedules are regarded as a benchmark in a multi-transaction environment. It is not possible to change the execution sequence of an instruction in a transaction, but two transactions may have their instructions executed at random. If two transactions are mutually independent and work on separate data segments, this execution does no harm; but if those two transactions operate on the same data, then the results can vary. The database can be taken to an inconsistent state by this ever-varying result.

We allow the parallel execution of a transaction schedule to solve this problem, if the transactions are either serializable or have some relationship of equivalence between them.

Equivalence Schedules

An equivalence schedule can be of the following types-

Result Equivalence

If two schedules, after execution, produce the same result, they are said to be equal to the result. For a certain value and different results for another set of values, they will yield the same result. That's why this equivalence is usually not considered significant.

View Equivalence

If transactions in both schedules perform similar actions in a similar way , two schedules will see equivalence.

For instance −

• If T reads the initial data in S1, then the initial data in S2 is read as well.
• If the value written by J in S1 is read by T, then the value written by J in S2 is also read.
• If T writes the final value of the data in S1, then the final value of the data in S2 is also written.

Conflict Equivalence

If they have the following properties, two schedules will be conflicting,

• Separate transactions belong to both.
• The same data object accesses both.
• At least one of these operations is "write".

Two schedules that have multiple transactions with conflicting activities are said to be equivalent to conflicts if and only if

• The same collection of transactions is found in both schedules.
• In both schedules, the order of conflicting operating pairs is preserved.

Note that view equivalent schedules are serializable for views and conflict equivalent schedules are serializable for conflicts. Both serializable conflict schedules are also serializable view schedules.

States of Transactions

A transaction in a database can be in one of the following states −

• Active-The transaction is being executed in this state. This is any transaction 's initial condition.
• Partially Committed-It is assumed to be in a partially committed state when a transaction performs its final operation.
• Failed- if any of the checks made by the database recovery system fails, a transaction is said to be in a failed state. A transaction that has failed will no longer go further.
• Aborted-If all of the checks fail and the transaction has entered a failed state, the recovery manager returns all of the written operations to the database to get the database back to its original state where it was before the transaction was performed. Transactions are referred to as aborted in that state. After a transaction aborts, the database recovery module will select one of the two operations-
  • Re-start the transaction
  • Kill the transaction
• Committed-If a transaction successfully executes all its operations, it is said to be committed. All of its impact on the database system are now permanently known.