Transaction Management

A transaction is single unit of work. The transaction management is used to maintain database integrity by ensuring that batches of SQL operations execute completely or not at all. It is group of tasks that either executes fully or do not execute at all. A transaction typically consists of requests to access existing data, modify existing data, and add new data or any combination of these requests.

Let’s take an example where transaction is very useful, consider a transaction T1 that transfers Rs. 100 from account A to account B. Let the initial values of account A be Rs.3000 and account B be Rs.1500. The sum of the values of account A and B is Rs.4500 before the execution of transaction T1. Since T1 is a money transaction, the sum of the values of account A and B should be Rs.4500 even after its execution.

If transaction T1 is executed, either Rs.100 should be transferred from account A to B or neither of the accounts should be affected. If T1 fails after debiting Rs.100 from account A, but before crediting $100 to account B, the effects of this failed transaction on account A must be undone. This is the atomicity property of transaction T1. The execution of transaction T1 should also preserve the consistency of the database, that is, the sum of the values of account A and B should be Rs.4500 even after the execution of T1.

Suppose that during the execution of transaction T1 when Rs.100 is debited from account A and not yet credited to account B, another concurrently running transaction, say T2, reads the values of account A and B. Since T1 has not yet completed, T2 will read inconsistent values.

A transaction is used to satisfy some properties which helps ensure the integrity of data. These properties are ACID (Atomicity, Consistency, Isolation, and Durability):

  • Atomicity implies that either all of the operations that make up a transaction should execute or none of them should occur.
  • Consistency implies that if all the operations of a transaction are executed completely, the database is transformed from one consistent state to another.
  • Isolation implies that each transaction appears to run in isolation with other concurrently running transactions.
  • Durability (also known as permanence) implies that once a transaction is completed successfully, the changes made by the transaction persist in the database, even if the system fails.

Following are the commands used in transactions:

  • TRANSACTION
  • COMMIT
  • ROLLBACK

TRANSACTION

Each transaction is stated by using BEGIN TRANSACTION commands. For example, Consider the Student table.

Table: Student
IT_Officer_003_05

if we want to update the record of a Student in transaction, we can use the following command:

BEGIN TRANSACTION
                UPDATE Student SET Address = ‘MG Road, Shimla’ WHERE Student_Id = 2;

This starts a transaction and updates the Student table but the changes will not reflect until we COMMIT the changes.

COMMIT

Once a transaction is completed, a change cannot be implicitly written to database. We need to explicitly write the COMMIT statement to save changes. For example, in above example we need to commit the transaction to save the update of Student with id 2 in database as given below:

BEGIN TRANSACTION
                UPDATE Student SET Address = ‘MG Road, Shimla’ WHERE Student_Id = 2;
COMMIT TRANSACTION

ROLLBACK

The ROLLBACK command is used to undo the changes done by the transaction. You use ROLLBACK in case any error occurs in transaction. The usage of command is:

BEGIN TRANSACTION
                UPDATE Student SET Address = ‘MG Road, Shimla’ WHERE Student_Id = 2;
ROLLBACK

It will undo the update done by the above the command.

Download as PDF

Read next:  Data Communication ››

« Back to Course page