Transactions and ACID Properties
Subject: Computer Science Fundamentals · Language: DBMS · Level: beginner · 10 min read
Understand transactions in DBMS, ACID properties, transaction states, COMMIT, ROLLBACK, and why databases protect complete operations.
A transaction is a group of database operations that should be treated as one complete unit of work. If all operations are successful, the transaction is saved. If any important operation fails, the transaction should be cancelled so the database does not remain in a half-completed state. This idea is important because many real-life database tasks need more than one operation. A money transfer, ticket booking, order placement, exam result update, and inventory purchase may all require multiple changes in different tables. The DBMS must make sure these changes happen correctly.
A beginner should understand transactions as a promise: either the complete work happens properly, or the database returns to a safe previous state. This keeps data reliable even when errors, power failures, or system crashes happen.
Simple Transaction Example
Consider a bank transfer where Rahul sends 500 rupees to Priya. The database must reduce Rahul's balance and increase Priya's balance.
```text
Transfer 500 from Rahul to Priya
Step 1: Deduct 500 from Rahul's account
Step 2: Add 500 to Priya's account
Step 3: Save the transaction permanently
``` If Step 1 happens but Step 2 fails, money disappears from the system. If Step 2 happens without Step 1, extra money is created. Both situations are incorrect. A transaction prevents this by treating all steps as one unit.
In a correct transaction, both account updates should be saved together. If the second update fails, the first update should be undone.
Why Transactions Are Needed
Transactions protect the database from incomplete work. Without transactions, a failure in the middle of an operation can leave data inconsistent. They also help when many users work on the same database at the same time. For example, two users may try to book the last available seat, or two payments may update the same wallet balance. Transaction rules help the DBMS handle such situations safely.
Transactions are used heavily in banking, railway booking, shopping carts, payment systems, hospital systems, college portals, and inventory management. Any place where data accuracy matters needs transaction control.
SQL Transaction Commands
The most common transaction control commands are listed below.
1. `COMMIT`: Saves all changes of the transaction permanently.
2. `ROLLBACK`: Cancels the transaction and undoes uncommitted changes.
3. `SAVEPOINT`: Creates a temporary point inside a transaction.
4. `ROLLBACK TO SAVEPOINT`: Goes back to a selected savepoint.
5. `SET TRANSACTION`: Sets transaction properties such as isolation level.
For beginners, `COMMIT` and `ROLLBACK` are the most important commands. `COMMIT` means the work is accepted, while `ROLLBACK` means the work is cancelled.
COMMIT
`COMMIT` is used when all operations in a transaction are successful and the changes should become permanent.
```sql
UPDATE Accounts
SET balance = balance - 500
WHERE account_no = 101;
UPDATE Accounts
SET balance = balance + 500
WHERE account_no = 202; COMMIT;
``` In this example, the debit and credit operations are completed before `COMMIT`. After commit, the updated balances become permanent in the database. Once a transaction is committed, normal rollback cannot undo it. That is why applications should commit only after checking that all required operations have completed correctly.
ROLLBACK
`ROLLBACK` is used when something goes wrong and the transaction should be cancelled.
```sql
UPDATE Accounts
SET balance = balance - 500
WHERE account_no = 101;
-- If the credit operation fails, cancel the transaction
ROLLBACK;
``` After rollback, the database returns to the state it had before the transaction began. Rahul's balance will not remain wrongly deducted. Rollback is useful when there is an error, validation failure, insufficient balance, duplicate booking, failed payment confirmation, or any condition where saving partial changes would be unsafe.
SAVEPOINT
A savepoint is a named point inside a transaction. It allows part of a transaction to be rolled back without cancelling the entire transaction.
```sql
SAVEPOINT before_discount;
UPDATE Orders
SET discount = 100
WHERE order_id = 501; ROLLBACK TO SAVEPOINT before_discount;
``` In this example, the transaction can return to the point before discount was applied. Savepoints are useful in larger transactions where some steps may be optional or correctable. A beginner does not need to use savepoints in every query, but knowing the idea helps understand advanced transaction control.
ACID Properties
ACID properties are the four main rules that make transactions reliable. The word ACID is formed from Atomicity, Consistency, Isolation, and Durability.
1. Atomicity: A transaction is completed fully or not done at all.
2. Consistency: A transaction keeps the database in a valid state.
3. Isolation: Transactions should not disturb each other while running.
4. Durability: Once committed, changes should remain saved even after failure.
These properties are the heart of transaction management. They explain how a DBMS protects data from incomplete updates, invalid states, concurrent access problems, and system failures.
ACID Summary
This table gives a short revision view of ACID properties.
| Property | Meaning | Simple Example |
| --- | --- | --- |
| Atomicity | All steps happen or none happen | Debit and credit both succeed in money transfer |
| Consistency | Database rules remain valid | Balance should not become invalid after transfer |
| Isolation | Transactions do not interfere incorrectly | Two bookings should not get the same seat |
| Durability | Committed data remains saved | Payment record remains after system restart |
The table is useful for revision, but each property should be understood through behavior, not only through definition.
Atomicity
Atomicity means a transaction is indivisible. The DBMS should not save only half of the transaction. Either every required step succeeds, or the complete transaction is undone. In the bank transfer example, deducting money from one account and adding money to another account must be treated as one unit. If credit fails after debit, atomicity says the debit should be rolled back.
Atomicity protects the database from partial updates. It is one of the first properties students should connect with `ROLLBACK`.
Consistency
Consistency means a transaction should move the database from one valid state to another valid state. It should not break rules, constraints, or relationships. For example, if a database rule says account balance cannot be negative, a transaction should not commit a negative balance. If a foreign key requires a valid department ID, a transaction should not insert a student with an invalid department ID.
Consistency depends on both DBMS rules and application logic. The DBMS enforces constraints, while the application should check business conditions such as sufficient balance or valid order quantity.
Isolation
Isolation means transactions should run as if they are separated from each other. Even when many users use the database at the same time, one transaction should not see unsafe intermediate changes of another transaction. For example, if two users try to book the last seat at the same time, isolation helps prevent both users from successfully booking the same seat. Isolation is closely connected with concurrency control. Locks, timestamps, and isolation levels are used by the DBMS to manage multiple transactions safely.
Durability
Durability means once a transaction is committed, its changes should remain saved even if the system crashes later. For example, after a payment transaction is committed, the payment record should not disappear because of a power failure. The DBMS uses logs, recovery techniques, and stable storage ideas to protect committed data. Durability gives confidence that committed work is permanent. This is very important in payment systems, bank systems, and any application where losing saved data is unacceptable.
Transaction States
A transaction passes through different states during its life cycle. These states help explain what is happening internally while the DBMS processes a transaction.
1. Active: The transaction is running and executing operations.
2. Partially committed: The final operation has executed, but changes are not fully permanent yet.
3. Committed: The transaction completed successfully and changes are permanent.
4. Failed: The transaction cannot continue because of an error or failure.
5. Aborted: The transaction has been rolled back and changes are undone.
6. Terminated: The transaction has finished and left the system.
These states are useful for exams because they explain how a transaction moves from execution to success or failure.
Transaction State Diagram
The following text diagram shows the basic flow of transaction states.
```text
Active
|
v
Partially Committed ----> Committed ----> Terminated
|
v
Failed ----> Aborted ----> Terminated
```
A transaction usually moves from active to partially committed, then committed, and finally terminated. If something goes wrong, it moves to failed and then aborted.
Common Transaction Failures
Transactions may fail for many reasons. A system crash can stop execution before commit. A deadlock can force the DBMS to cancel one transaction. A constraint violation can reject invalid data. A network failure can interrupt communication between application and database.
The DBMS must handle these failures carefully. If the transaction was not committed, changes may be rolled back. If the transaction was committed, recovery should preserve the committed changes. This is why logs and recovery mechanisms are important parts of DBMS transaction management.
Important Points to Remember
A transaction is a complete unit of database work. It should be committed only when all important steps succeed. `COMMIT` saves changes permanently, while `ROLLBACK` cancels uncommitted changes. `SAVEPOINT` allows partial rollback inside a transaction.
ACID properties make transactions reliable. Atomicity prevents half-completed work, consistency protects valid rules, isolation manages simultaneous transactions, and durability protects committed data.