Transaction in DBMS

Transactions refer to a set of operations that are used for performing a set of logical work. Usually, a transaction means the data present in the DB has changed. Protecting the user data from system failures is one of the primary uses of DBMS.

In this article, we will take a look at Transaction in DBMS according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

• What is Transaction in DBMS?
• ACID Properties
  • Atomicity
  • Consistency
  • Durability
  • Isolation
• Serializability
  • Schedule
  • Serial Schedule
• Equivalence Schedules
  • Result Equivalence
  • View Equivalence
  • Conflict Equivalence
• States of Transactions
• Practice Problems on Conversion of Bases to Other Bases
• FAQs

What is Transaction in DBMS?

We can define a transaction as a group of tasks in DBMS. Here a single task refers to a minimum processing unit, and we cannot divide it further. Now let us take the example of a certain simple transaction. Suppose any worker transfers Rs 1000 from X’s account to Y’s account. This given small and simple transaction involves various low-level tasks.

X’s Account

Open_Account(X)

Old_Bank_Balance = X.balance

New_Bank_Balance = Old_Bank_Balance – 1000

A.balance = New_Bank_Balance

Close_Bank_Account(X)

Y’s Account

Open_Account(Y)

Old_Bank_Balance = Y.balance

New_Bank_Balance = Old_Bank_Balance + 1000

B.balance = New_Bank_Balance

Close_Bank_Account(Y)

ACID Properties

The transaction refers to a small unit of any given program that consists of various low-level tasks. Every transaction in DBMS must maintain ACID – A (Atomicity), C (Consistency), I (Isolation), D (Durability). One must maintain ACID so as to ensure completeness, accuracy, and integrity of data.

1. Atomicity

The property of atomicity states that we must treat any given transaction as an atomic unit. It means that either all or none of its operations need to be executed. One must ensure that there is no state in the database in which a transaction happens to be left partially completed. One must either define the states before or after the execution/failure/abortion of the transaction.

2. Consistency

The property of consistency states that the database must always remain in a consistent state after any transaction. Thus, a transaction must never have any damaging effect on the data and information that resides in the database. In case, before the execution of a transaction, the database happens to be in a consistent state, then it has to remain consistent even after the transaction gets executed.

3. Durability

The property of durability states that any given database must be durable enough to all of its latest updates, and it must happen even if the system suddenly restarts or fails. The database would hold the modified data in case a transaction updates and commits some chunk of information in the database. In case a transaction commits and yet the system fails before we write the data on the disk, then the information would be actually updated after the system springs back into action.

4. Isolation

The property of isolation states that when multiple transactions are being simultaneously executed and in parallel in a database system, then the carrying out and execution of the transaction would occur as if it is the only transaction that exists in the system. None of the transactions would affect any other transaction’s existence.

Serializability

Whenever the operating system executes multiple transactions in a multiprogramming environment, then there is always a possibility that instructions of one transaction interleave with some other transaction.

Schedule

A schedule refers to a chronological execution sequence of a given transaction. Any schedule can have multiple transactions in it, and each comprises a number of tasks/instructions.

Serial Schedule

It refers to that schedule in which the transactions are aligned in a way that just one of the transactions is executed first. Whenever the cycle completion of the first transaction occurs, the execution of the next transaction also occurs. Thus the transactions are ordered here one after another. Such a type of schedule is known as a serial schedule since the execution of the transactions occurs serially.

Serial schedules are like a benchmark in multi-transaction environments. We cannot change the execution sequence of instructions in a transaction, but we can execute the instructions of two transactions in a random fashion. Such an execution does not cause any harm if the two transactions are independent (mutually) and work on different data segments. But in case both of these transactions work on the same data segment, then its results may vary. These ever-varying results may bring the database to a state that is inconsistent.

If we want to resolve this issue, we allow execution of the transaction schedule parallelly in case the transactions are serializable or if we have an equivalence relation among them.

Equivalence Schedules

The equivalence schedules can be of the given types −

Result Equivalence

When the execution of two schedules produces the same result, they are considered to be equivalent. This type of equivalence is not considered to be very significant in general since they may yield a similar result for some of the values and different ones for others.

View Equivalence

Two given schedules are considered to be in view equivalence when the transactions present in both of these schedules perform a similar kind of action in a similar manner.

Now, for example,

• In case T reads the data initially in S1, then it will also read the data initially in S2.
• In case T reads the value that is written by J in S1, then it will also read the value that is written by J in S2.
• If the final write on a data value in S1 is performed by T, then it will also perform the final write on a data value in S2.

Conflict Equivalence

The two given schedules would be conflicting in case they have these given properties −

• They both access a similar data item.
• They both belong to different transactions.
• At least one of these is the “write” operation.

The two schedules that have multiple transactions and conflicting operations are conflict equivalent, if –

• Both of these schedules consist of a similar set of Transactions.
• We maintain the order of conflicting operation pairs in both schedules.

Note − The view equivalent schedules happen to be view serializable, while the conflict equivalent schedules happen to be conflict serializable. Also, all the conflict serializable schedules happen to be viewed as serializable as well.

States of Transactions

In a database, a transaction can be in one of these states given below −

• Active − This is the state in which a transaction is being executed. Thus, it is like the initial state of any given transaction.
• Partially Committed − A transaction is in its partially committed state whenever it executes the final operation.
• Failed − In case any check made by a database recovery system fails, then that transaction is in a failed state. Remember that a failed transaction can not proceed further.
• Aborted − In case any check fails, leading the transaction to a failed state, the recovery manager then rolls all its write operations back on the database so that it can bring the DB (database) back to the original state (the state where it actually was prior to the transaction execution). The transactions in this state are known to be aborted. A DB recovery module can actually select one of these two operations after the abortion of a transaction –
  • Re-start
  • Kill the transaction
• Committed − We can say that a transaction is committed in case it actually executes all of its operations successfully. In such a case, all of its effects are now established permanently on the DB system.

Practice Problems on Transaction in DBMS

1. A transaction that completes its execution is known to be:

a. Loaded

b. Saved

c. Committed

d. Rolled

Answer – (c) Committed

2. We can change the deadlock state back to a stable state using the _____________ statement.

a. Rollback

b. Commit

c. Deadlock

d. Savepoint

Answer – (a) Rollback

3. What are the four ACID properties of a transaction in DBMS?

a. Atomicity, Consistency, Inconsistent, Durability

b. Atomicity, Consistency, Isolation, Database

c. Automatically, Concurrency, Isolation, Durability

d. Atomicity, Consistency, Isolation, Durability

Answer – (d) Atomicity, Consistency, Isolation, Durability

Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.

Also Explore,

• Introduction to DBMS
• Lossy Decomposition in DBMS
• Multivalued Dependency in DBMS
• Decomposition in DBMS
• First Normal Form in DBMS
• Functional Dependency in DBMS
• Normal Forms in DBMS
• Partial Dependency in DBMS
• Relational Model in DBMS