Transactions in DBMS: Fast Revision Notes for IBPS SO IT Officer

A transaction in DBMS is a sequence of one or more database operations executed as a single logical unit of work, which either completes entirely or leaves the database unchanged. IBPS SO IT Officer and similar exams test this topic through conceptual questions on transaction states, COMMIT, ROLLBACK, and SAVEPOINT — rarely through pure definitions — so understanding the state transitions matters more than memorizing terms.

What Is a Transaction?

A transaction is considered successful only when every operation inside it completes; if even one operation fails, the database rolls back to its previous consistent state.

A transaction typically groups operations like:

• INSERT
• UPDATE
• DELETE
• SELECT (in some cases, for read consistency)

Example: Fund Transfer

Rahul transfers ₹5,000 from Account A to Account B. The transaction has two operations:

1. Debit ₹5,000 from Account A (₹50,000 → ₹45,000)
2. Credit ₹5,000 to Account B (₹20,000 → ₹25,000)

Q: What happens if the debit succeeds but the credit operation fails? A: The DBMS rolls back the debit operation as well, since a transaction must be all-or-nothing — Account A's balance is restored to ₹50,000 so the database never ends up in an inconsistent, partially-updated state.

Why Transactions Are Needed

Without transactions, a partial update caused by any of the following could leave the database inconsistent:

Transaction Lifecycle & States

Every transaction moves through six possible states — Active, Partially Committed, Committed, Failed, Aborted, and Terminated — depending on whether it completes successfully or encounters an error.

Click to enlarge

Success path: Active → Partially Committed → Committed → Terminated Failure path: Active → Failed → Aborted → Terminated (or restarted)

Q: Which transaction state occurs immediately after the last SQL statement executes, before COMMIT? A: Partially Committed. The statement has run, but the DBMS hasn't yet guaranteed the changes are permanently written to disk — that guarantee only comes after COMMIT succeeds.

Q: What is the difference between the Failed and Aborted states? A: Failed is the state entered the moment an error occurs (e.g., a constraint violation or deadlock), before any recovery happens. Aborted is the state entered after the DBMS has rolled back all the transaction's changes in response to that failure.

Q: Can a transaction resume after entering the Aborted state? A: A transaction cannot resume itself, but the DBMS or application may choose to restart it as a brand-new transaction; the original aborted transaction still ends in the Terminated state.

Transaction Control Commands (TCL)

COMMIT, ROLLBACK, SAVEPOINT, and ROLLBACK TO SAVEPOINT are the four Transaction Control Language (TCL) commands used to manage transaction outcomes, with COMMIT and ROLLBACK the most frequently tested.

COMMIT

COMMIT permanently saves all changes made during the current transaction, after which the changes become visible to other users and can no longer be undone.

START TRANSACTION;

UPDATE Employee
SET Salary = Salary + 5000
WHERE EmpID = 101;

COMMIT;

ROLLBACK

ROLLBACK cancels a transaction and restores the database to its state before the transaction began, but only for changes that have not yet been committed.

START TRANSACTION;

DELETE FROM Employee
WHERE EmpID = 101;

ROLLBACK;

The deleted record is restored, because the DELETE was never committed.

SAVEPOINT and ROLLBACK TO SAVEPOINT

A SAVEPOINT marks an intermediate checkpoint inside a transaction, letting you undo only the operations after that checkpoint instead of the entire transaction.

START TRANSACTION;

UPDATE Employee
SET Salary = Salary + 5000
WHERE EmpID = 101;

SAVEPOINT S1;

DELETE FROM Employee
WHERE EmpID = 102;

ROLLBACK TO S1;

COMMIT;

Result: the salary update is preserved, the delete is undone, and the transaction is committed successfully — this partial-rollback behavior is exactly what SAVEPOINT is tested on.

TCL Commands Summary

Q: Can ROLLBACK undo a transaction that has already been committed? A: No. Once COMMIT has executed successfully, the changes are permanent and ROLLBACK has no effect on them — ROLLBACK can only undo uncommitted work.

Autocommit Mode

Autocommit is a DBMS setting that, when ON, treats every individual SQL statement as its own transaction and commits it automatically right after execution.

Transactions vs ACID Properties

Transactions define how database operations are grouped and executed, while ACID properties — Atomicity, Consistency, Isolation, and Durability — define the rules that make those transactions reliable.

👉 Continue your revision with ACID Properties in DBMS — the two topics are frequently tested together in IBPS SO IT Officer.

One-Minute Revision

What to Study Next

Complete your DBMS revision with these related articles.