Database Management System (DBMS) / Acid Properties and transactions in DBMS

What are Transactions in DBMS

🔹 What are Transactions in DBMS?

A transaction in DBMS is a sequence of one or more SQL operations (like INSERT, UPDATE, DELETE, SELECT) that are executed as a single unit of work.

A transaction is either fully completed (committed) or completely rolled back (undone) — never left halfway.

🔹 Key Properties: ACID

Every transaction must satisfy the ACID properties:

Property

Meaning

A – Atomicity

All operations in a transaction are done or none. No partial updates.

C – Consistency

The database moves from one valid state to another valid state.

I – Isolation

Transactions don’t interfere with each other when running simultaneously.

D – Durability

Once a transaction is committed, its changes are permanent — even in case of crash.

🔹 Example Banking System:

Transferring ₹100 from Account A to Account B:

-- Start Transaction
BEGIN;

-- Step 1: Deduct from A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

-- Step 2: Add to B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- Commit the changes
COMMIT;

If anything fails in between (e.g. second update), you can do:

ROLLBACK;

This undoes all changes made so far in the transaction.

🔹 Transaction States:

State

Description

Active

The transaction has started and is performing operations.

Partially Committed

All operations are done, waiting for commit.

Committed

All changes are saved permanently.

Failed

Some issue occurred (like power failure or error).

Aborted

Changes are undone; the transaction is rolled back.

🔹 SQL Keywords Involved:

  • BEGIN / START TRANSACTION

  • COMMIT – Save changes

  • ROLLBACK – Undo changes

  • SAVEPOINT – Create rollback checkpoints

  • SET TRANSACTION – Set isolation level

✅ Summary:

A transaction is a logical unit of work that must either be fully completed or fully undone, ensuring data integrity, consistency, and reliability.