Transactions in DBMS

Transactions in SQL is a set of SQL statements that must be executed all at once or none of them must be executed. Transaction does not allow partial execution. The concept of transaction help in maintaining the integrity of the data in both the case even if there are multiple operations that are interrelated or there are multiple database users that update the database concurrently.

Content: Transactions in SQL

1. What is Transaction?
2. Need of Transaction
3. Implementation
4. Transaction States
5. ACID Test
6. Transaction Modes

What is Transaction in DBMS?

Transactions can be defined as a set of SQL commands that affects or change the data present in your database. It must be ensured that either all the commands of the transaction must be executed or none of them must be executed.

In case, if any one of the commands of the transaction fails to execute for any unknown reason then the data modified in the process of execution of the transaction is rolled back. The concept of transaction in DBMS maintains the integrity of data present in the database.

When any SQL query or an update statement is executed the transaction begins implicitly. Now the beginning of the transaction also needs an ending SQL statement. The ending SQL statement of a transaction must end either with the commit statement or with the rollback statement.

When any of the commands in the transaction fails to execute due to the occurrence of errors such as an error in one of the SQL statements of the transaction, power cut, system crash etc. the control directly goes to the rollback statement and all the modifications to the database are reverted back.

The most common example of the rollback would be, consider that you are editing a word document and you quit the document without saving it.

Once the commit statement of any transaction is executed the data affected by the transaction can never be rolled back. It is like saving the word document before quitting, in this acse the changes made to the document persist.

Need of Transaction

The transaction is useful when we require several SQL statements or queries to execute as a single unit. That is either all must be transaction statements must be successful be executed or none should be executed. We need transactions when we want that the database must be consistent even after the changes done by the SQL statements.

We know in SQL there may be many SQL statements executing concurrently so we must maintain isolation. To prevent the execution of one SQL statement, affect the execution of another SQL statement we use transactions.

The use of transactions also makes the database durable it ensures that the successful changes made to the database must persist and shall not be lost in case of any failure this increases the durability of the database.

Implementation of Transaction

To implement the transaction, the following are the steps that must be followed:

Begin Transaction;
SQL Commands;
Check for Errors
      If error occurs
         rollback transaction;
      Else
         commit transaction;

The transaction begins with the begin transaction command. And then the transaction read/write (SQL commands) command are executed. Once the commands are processed then we have to check for the errors.

If any kind of error has occurred then the transactions are rolled back. If no error has occurred then the transactions are committed.

Transaction States

As we know that the transaction is atomic that either it should execute entirely or none should execute. Partial execution will affect the consistency of the data. Now whenever a transaction fails, the data affected by the transaction has to be rolled back.

For the recovery purpose, the system must be aware that from where the transaction has started, where it has terminated and where it must be committed where it has been aborted. To keep track of the transaction the recovery manager must have to track the following transaction states.

• Active State: As soon as the transaction starts its execution it enters the active state. Being in the active state the transaction has to perform Read and Write operations.
• Partially Committed State: When the transaction finishes executing Read and Writes operation it enters the partially committed state. If there is no interrupt (failure) the transaction will enter a committed state.
  Committed State: The transaction enters the committed state if the transaction is executed successfully without any interruption.
• Failed State: The transaction enters the failed state either from the partially committed state i.e. if the failure occurs after the execution of the transaction but before it is committed or from the active state i.e. when the transaction is in execution.
• Terminated State: When the changes done by the transaction are recorded on the database, the transaction enters the terminated state.

With the states above the recovery manager of the database also have to track the transaction operations given below:

1. BEGIN_TRANSACTION: With this transaction state the transaction starts getting executed.
2. READ or WRITE: These states specify accessing or updating data items from the database.
3. END_TRANSACTION: This state marks the end of READ and WRITE operation. However, it must be checked whether the changes done by the READ and WRITE statement must be committed to the database or it must be rolled back.
4. COMMIT_TRANSACTION: This state specifies the successful end of the transaction and the changes done by the transaction cannot be rolled back now.
5. ROLLBACK_ABORT: This state specifies that the transaction has not been executed successfully and the data affected by the transaction must be rolled back.
   

ACID Test

Before considering a transaction as a successful transaction it must pass the ACID test.

1. Atomicity: The atomicity test specifies that either all the transactions are carried out in their entirety or no transactions at all. The partial transaction is not accepted. The transaction failure or the incomplete transaction may be the result of the following reasons.

• System Failure: This kind of failure may be a hardware error, software error or network error. The hardware error includes failure of main memory, the software error includes an error in coding or logic, the network error includes failure of connectivity etc.
• Transaction Error: There can occur an error while executing the transaction statement such as integer overflow or division by the zero, erroneous parameter value, programming error or it may happen that the user has interrupted the transaction execution.

2. Consistency: It is the user’s responsibility to maintain the consistency of the database. The user should code the transaction in such a way that it leaves the database in a consistent state after the transaction execution.

Consider that there are two accounts A and B. Now the transaction says that account A transfers 100 $ to account B. Then in this case the user must instruct the database to deduct the 100 $ from account a and also to add 100 $ to account B to maintain the consistency of the database.

3. Isolation: Some transactions execute simultaneously but they must not interfere with each other executing concurrently. The execution of one transaction must not affect the execution of another transaction.

4. Durability: The modifications done to the data present in the database by a committed transaction must persist and must not be lost due to any kind of failure.

Transaction Modes

There are three kinds of transaction modes let us discuss them:

a. Autocommit Transaction: In the Autocommit transaction mode we do not have to explicitly specify the commit command at the end of each SQL statement. The commit command is executed at the end of the execution of each SQL statement as it treats each SQL statement as a different transaction. Well, the SQL statement executed in Autocommit mode can never be rolled back.

b. Implicit Transaction: The Autocommit mode is the by default mode of SQL transaction. But if you feel that the changes to the database must not be committed unless it is specified then you can set the connection to implicit transaction mode.

With the implicit transaction mode, the transaction starts with the execution of the first SQL statement and remain in effect until you specify any commit or rollback statement.

c. Explicit Transaction: In the explicit transaction mode the user can control when the transaction should begin and when it must be committed or rolled back by specifying the command BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION.

So, in this context, we have studied the concept of a transaction. We had discussed its need, its implementation, the ACID test which every successful transaction must pass. We have also discussed the three kinds of transaction modes.