Transaction in DBMS: Ensuring Data Integrity and Consistency
A transaction in DBMS is a logical unit of work that consists of one or more database operations, executed as a single, indivisible sequence. Transactions are what make the ACID properties possible in practice.
Properties of a Transaction
Every transaction follows the ACID model: Atomicity, Consistency, Isolation, and Durability. Together, these ensure that a transaction either fully succeeds or has no effect at all on the database.
States of a Transaction
- Active — the transaction has started and is executing.
- Partially Committed — the final operation has executed, but changes aren't yet permanently saved.
- Committed — all changes are permanently saved to the database.
- Failed — an error occurred, and the transaction cannot proceed.
- Aborted — the transaction is rolled back, undoing all changes made so far.
Key Transaction Commands
| Command | Purpose |
|---|---|
| BEGIN / START TRANSACTION | Marks the start of a transaction |
| COMMIT | Saves all changes made during the transaction permanently |
| ROLLBACK | Undoes all changes made during the transaction |
| SAVEPOINT | Creates a point within a transaction that you can roll back to |
Example
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;
If any statement between BEGIN and COMMIT fails, a ROLLBACK ensures neither account is affected, preserving data integrity.
Transactions are the backbone of reliable multi-user database systems, ensuring that concurrent operations never leave the data in an inconsistent state.
Ready to master DBMS & SQL Training Course?
Join Uncodemy's hands-on training with mentor support and placement assistance.