Transaction in DBMS (original) (raw)

Last Updated : 23 Sep, 2025

A transaction refers to a sequence of one or more operations (such as read, write, update, or delete) performed on the database as a single logical unit of work.

transaction_1

Transaction

All types of database access operation which are held between the beginning and end transaction statements are considered as a single logical transaction. During the transaction the database is inconsistent. Only once the database is committed the state is changed from one consistent state to another.

Example: Let’s consider an online banking application:

**Transaction: When a user performs a **money transfer, several operations occur, such as:

Facts about Database Transactions

Operations of Transaction

A user can make different types of requests to access and modify the contents of a database. So, we have different types of operations relating to a transaction. They are discussed as follows:

1) Read(X)

A read operation is used to read the value of a particular database element **X and stores it in a temporary buffer in the main memory for further actions such as displaying that value.

**Example: For a banking system, when a user checks their balance, a Read operation is performed on their **account balance:

SELECT balance FROM accounts WHERE account_id = 'A123';

This updates the balance of the user's account after withdrawal.

2) Write(X)

A write operation stores updated data from main memory back to the database. It usually follows a read, where data is fetched, modified (e.g., arithmetic changes) and then written back to save the updated value.

**Example: For the banking system, if a user withdraws money, a **Write operation is performed after the balance is updated:

UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A123';

This updates the balance of the user’s account after withdrawal.

3) Commit

This operation in transactions is used to maintain integrity in the database. Due to some failure of power, hardware, or software, etc., a transaction might get interrupted before all its operations are completed. This may cause ambiguity in the database, i.e. it might get inconsistent before and after the transaction.

**Example: After a successful money transfer in a banking system, a **Commit operation finalizes the transaction:

COMMIT;

Once the transaction is committed, the changes to the database are permanent and the transaction is considered **successful.

4) Rollback

A rollback undoes all changes made by a transaction if an error occurs, restoring the database to its last consistent state. It helps prevent data inconsistency and ensures safety.

**Example: Suppose during the money transfer process, the system encounters an issue, like insufficient funds in the sender’s account. In that case, the transaction is rolled back:

ROLLBACK;

This will undo all the operations performed so far and ensure that the database remains consistent.

ACID Properties of Transaction

Transactions in DBMS must ensure data is accurate and reliable. They follow four key ACID properties:

  1. **Atomicity: A transaction is all or nothing. If any part fails, the entire transaction is rolled back. Example: While transferring money, both debit and credit must succeed. If one fails, nothing should change.
  2. **Consistency: A transaction must keep the database in a valid state, moving it from one consistent state to another. Example: If balance is ₹1000 and ₹200 is withdrawn, the new balance should be ₹800.
  3. **Isolation: Transactions run independently. One transaction’s operations should not affect another’s intermediate steps. Example: Two users withdrawing from the same account must not interfere with each other’s balance updates.
  4. **Durability: Once a transaction is committed, its changes stay even if the system crashes. Example: After a successful transfer, the updated balance remains safe despite a power failure.

Read more about ACID Properties

Transaction Schedules

When multiple transaction requests are made at the same time, we need to decide their order of execution. Thus, a transaction schedule can be defined as a chronological order of execution of multiple transactions. Example: After a successful transfer, the updated balance remains safe despite a power failure.

There are broadly **two types of transaction schedules discussed as follows:

i) Serial Schedule

In a serial schedule, transactions execute one at a time, ensuring database consistency but increasing waiting time and reducing system throughput. To improve throughput while maintaining consistency, concurrent schedules with strict rules are used, allowing safe simultaneous execution of transactions.

ii) Non-Serial Schedule

Non-serial schedule is a type of transaction schedule where multiple transactions are executed concurrently, interleaving their operations, instead of running one after another. It improves system efficiency but requires concurrency control to maintain database consistency.

Read more about Types of Schedules

Transaction Recovery Techniques in DBMS

When a system failure occurs during a transaction (such as power failure, hardware malfunction, software error, or deadlock), the database can become inconsistent or partially updated. Recovery techniques are designed to restore the database to a consistent state, ensuring the ACID properties are maintained.

Checkpointing

Undo and Redo Operations

**Undo: Revert the effects of incomplete or uncommitted transactions.
**Redo: Reapply committed transactions that might not have been written to disk at the time of failure.

Deferred Update (No-Undo/Redo Approach)

Immediate Update (Undo/Redo Approach)

Shadow Paging