Transactions in DBMS

⏱ Estimated reading time: 3 min

A Transaction in DBMS is a sequence of one or more SQL operations that are executed as a single logical unit of work. A transaction must either be fully completed or not executed at all. It ensures that the database remains consistent even in the presence of errors, system failures, or concurrent access.

Typical operations inside a transaction include:

• INSERT

• UPDATE

• DELETE

For example, transferring money between two bank accounts involves:

1. Deducting amount from Account A

2. Adding amount to Account B
   These steps must be treated as a single transaction.

Properties of a Transaction — ACID Properties

The four ACID properties guarantee reliability and consistency.

1️⃣ Atomicity

• All or nothing property

• Ensures entire transaction is completed.

• If any part fails, the whole transaction is rolled back.

Example:
If money is deducted from Account A but not added to B, entire transaction must be cancelled.

2️⃣ Consistency

• Transaction must take the database from one valid state to another valid state.

• Ensures rules, constraints, and triggers are not violated.

3️⃣ Isolation

• Multiple transactions can occur at the same time without interfering with each other.

• Intermediate results of one transaction should be hidden from others.

Example:
Two users withdrawing money simultaneously should not see inconsistent balance.

4️⃣ Durability

• Once a transaction is committed, its changes are permanent—even if system crashes.

• DBMS stores changes to non-volatile memory.

Transaction States

A transaction passes through several states:

1. Active – Execution in progress

2. Partially Committed – Final statement executed

3. Committed – Successful completion, changes saved

4. Failed – Error or system failure

5. Aborted – Transaction rolled back

6. Terminated – Transaction completed (either committed or aborted)

 Control Commands Used in Transactions

1️⃣ COMMIT

Saves all changes permanently.

COMMIT;

2️⃣ ROLLBACK

Undo changes made in the current transaction.

ROLLBACK;

3️⃣ SAVEPOINT

Creates a checkpoint inside a transaction.

SAVEPOINT sp1;
ROLLBACK TO sp1;

Problems Prevented by Transactions

Transactions help avoid anomalies in concurrent environments:

✔ Dirty Read

Reading uncommitted data from another transaction.

✔ Lost Update

Two transactions overwrite each other's data.

✔ Non-repeatable Read

Data changes during a transaction when read multiple times.

✔ Phantom Read

New rows appear in the result of a repeated query.

Schedules in Transactions

✔ Serial Schedule

Transactions execute one after another.
Safe but slow.

✔ Non-Serial Schedule

Transactions interleave.
Efficient but require checks for consistency.

Why Transactions Are Important?

• Maintain data consistency

• Provide fault tolerance

• Handle system crashes safely

• Ensure reliable multi-user access

• Prevent data corruption

Conclusion

Transactions are a fundamental concept in DBMS to ensure data reliability and integrity. Using ACID properties, transactions make sure that even in the presence of system failures, power outages, or concurrent users, the database remains stable, consistent, and correct. They are essential for applications such as banking, e-commerce, reservation systems, and any real-time multi-user environment.