Transaction Management

In database, transactions are very important to maintain the consistent state of the database. We need to consider the users who operate and access database, and alter the state. This is an important aspect of database management which you will study in this article.

What is Transaction?

Database Transaction is a logical unit of processing in a DBMS that consists of one or more database access operations. In a nutshell, database transactions show real-world events of any enterprise.

All varieties of database access operations that are held among the beginning and conclude transaction statements are considered as a single logical transaction in DBMS.

The database is uncertain transactions during the transaction. Only once the database is dedicated the state is changed from one consistent state to another.

• A transaction is a program unit whose execution might also add changes or won’t alternate to the contents of a database.
• The transaction concept in DBMS is accomplished as a single unit.
• If the database operations do not update the database but only retrieve data, this type of transaction is called a read-only transaction.
• A successful transaction can change the database from one certain State to another.
• DBMS transactions must be atomic, consistent, steady, and durable
• If the database were in an incompatible state before a transaction, it would remain in the inconsistent state at end of the transaction.

Why do you want concurrency in Transactions?

A database is a shared resource accessed. It is used by many customers and processes concurrently. For example, the banking device, railway reservations, air reservations systems, stock market monitoring, supermarket inventory, checkouts, etc.

Not managing concurrent access may create issues, for example:

• Hardware failure and additionally system crashes

States of Transactions

Let’s study, how a transaction moves between these various states are indexed below:

1. Once a transaction state performing, it becomes active. It can issue READ or WRITE operations.
• Once the READ and WRITE operations are complete, the transactions turn out to be partially committed state.
• After it, some recovery protocols need to ensure that a system failure will not result in an inability to record changes in the transaction permanently. If this check is a success, the transaction commits and enters the committed state.
• If the check becomes fail, the transaction goes to the Failed state.
• If the transaction is terminated while it’s in the active state, it goes to the failed state. The transaction should be rolled back to undo the effect of its write operations on the database.
• The terminated state refers to the transaction leaving the system.

How do we define ACID Properties?

ACID Properties are used for maintaining the integrity of the database during transaction processing. In DBMS “ACID” consists of Atomicity, Consistency, Isolation, and Durability.

• Atomicity: A transaction follows a single unit of operation. You either execute it completely or no longer execute it at all. There cannot be unequal execution.
• Consistency: Once the transaction is executed, it should move from one consistent state to another.
• Isolation: Transaction should be executed in isolation from other transactions (no Locks). During concurrent transaction execution, ongoing transaction results from simultaneously executed transactions should not be made available to each other. (Level 0,1,2,3)
• Durability: · After successful completion of a transaction, the changes within the database have to persist. Even in the case of system failures.

ACID Property in DBMS:

Below is an example of an ACID property in DBMS:

Transaction 1: Begin X=X+50, Y = Y-50 END

Transaction 2: Begin X=1.1*X, Y=1.1*Y END

Transaction 1 is transferring \$50 from account X to account Y

Transaction 2 is adding each account with a 10% interest payment

If both of these transactions are submitted together, there is no guarantee that Transaction 1 will execute before Transaction 2 or vice versa. Despite any order, the result must be as if the transactions take place serially one after the other.

Consider the following transaction T consisting of T1 and T2: Transfer of 100 from account X to account Y.

If the transaction fails after completion of T1 but before completion of T2.( say, after write(X) but before write(Y)), then the amount has been deducted from X but not added to Y.

This results in an inconsistent database state. Therefore, the transaction must be executed totally to check the accuracy of the database state.

Consistency: This means that integrity constraints must be maintained so that the database is uncertain before and after the transaction. It refers to the accuracy of a database.

Referring to the example above, the total amount before and after the transaction must be maintained.

Total before when T ( before T1 and T2) occurs = 500 + 200 = 700

Total after T (After T1 and T2) occurs = 400 + 300 = 700

Therefore, it results in the database being consistent. Inconsistency occurs in case T1 completes but T2 fails. As a result, T becomes incomplete.

Isolation: This property checks further that multiple transactions can occur concurrently without leading to the inconsistency of the database state. Transactions occur independently without interference.

Changes occurring in a particular transaction will not be visible to any other transaction until that change in that transaction is written to memory or has been fixed.

The ACID properties, provide a mechanism to ensure the correctness and consistency of a database in a way such that each transaction is a bunch of operations that act as one unit, produce consistent results, act in isolation from other operations, and update that it makes are durably stored.

Types Of DBMS Schedules

Types of schedules in the DBMS Schedule are a process of lining the transactions and executing them one by one. When multiple transactions are running concurrently and the order of operation is needed to be set so that the operations do not overlap each other, Scheduling is brought into play and the transactions are timed accordingly.

• Serial Schedules:  Schedules in which the transactions are executed non-interleaved, i.e., a serial schedule is one in which no transaction starts until a running transaction has ended are called serial schedules. i.e., In the Serial schedule, a transaction is executed completely before starting the execution of another transaction.

In other words, you can say that in a serial schedule, a transaction does not start execution until the currently running transaction finishes execution. This type of execution of the transaction is also known as non-interleaved execution. The example we have seen above is the serial schedule.

• Non-Serial Schedule: This is a type of Scheduling where the operations of multiple transactions are provided. This would possibly cause a rise in the concurrency problem.

The transactions are executed irregularly, keeping the result correct and the same as the serial schedule. Unlike the serial schedule where one transaction must wait for another to complete all its operations, in the non-serial schedule, the other transaction proceeds without waiting for the previous transaction to complete.

This sort of schedule does not provide any benefit for the concurrent transaction. It parted into two types namely, Serializable and Non-Serializable Schedules. The Non-Serial Schedule can be further split into Serializable and Non-Serializable.

Serializable:

This is used to maintain the regularity of the database. It is mainly used in non-Serial scheduling to check whether the scheduling will lead to any inconsistency or not. On the other hand, a serial schedule does not need serializability because it follows a transaction only when the previous transaction is complete.

The non-serial schedule is said to be in a serializable schedule only when it is equivalent to the serial schedules, for an n number of transactions. As a result, concurrency is allowed in this case thus, multiple transactions can execute concurrently.

These are of two types:

1. Conflict Serializable: A schedule is called conflict serializable if it can be converted into a serial schedule by swapping non-conflicting operations.
2. View Serializable: A Schedule is called view serializable if it is view equal to a serial schedule (no overlapping transactions). A conflict schedule is a view serializable

Non-Serializable:

The non-serializable schedule is divided into two types, Recoverable and Non-recoverable Schedules. More on this later.

Summary

You learned that the consistency of database is important as transactions are executed. The nature of transactions are expected to be consistent with ACID properties. Though it appears that the transactions are executed simultaneously, it is serialized mostly, we will explore on this topic in future articles.

In the next article, you will learn how database management maintain the concurrency.