🏦 Transaction Concepts

1. What is a Transaction?

👉 A transaction = a group of tasks that must succeed together or fail together.
Example: Money transfer (Debit from A + Credit to B).

2. ACID Properties

A – Atomicity → All or nothing. (If one step fails, undo everything).
C – Consistency → Database must remain valid before & after.
I – Isolation → Each transaction works independently.
D – Durability → Once saved (committed), changes stay forever.

3. Transaction Commands

COMMIT ✅
- Saves all changes permanently.
- Example: “Bank transfer complete.”
ROLLBACK ❌
- Undoes all changes since the transaction started.
- Example: Debit done, credit failed → Undo debit.
SAVEPOINT 📍
- Marks a checkpoint in a transaction.
- Allows rollback to that point (not the whole transaction).

4. How They Work Together

Start transaction.
Do task (e.g., debit).
Set SAVEPOINT (checkpoint).
Do next task (e.g., credit).
If problem → ROLLBACK TO SAVEPOINT (undo part only).
If all good → COMMIT (save permanently).

5. Quick Summary

COMMIT → Save changes forever.
ROLLBACK → Undo everything.
SAVEPOINT → Undo partially (from a checkpoint).

6. Example: Transfer $100 from A → B

BEGIN;  
UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 'A';  
SAVEPOINT after_debit;  
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 'B';  

-- If error:  
ROLLBACK TO after_debit;  

-- If success:  
COMMIT;

MCQ

Basic Concepts of Transactions

Which of the following is NOT a property of a transaction?
- A) Atomicity
- B) Consistency
- C) Durability
- D) Scalability
  Answer: D) Scalability
What does the term “Atomicity” refer to in transactions?
- A) The database remains consistent after a transaction.
- B) A transaction is treated as a single unit: all or none.
- C) The transaction is durable even after system failure.
- D) Multiple transactions run without interfering.
  Answer: B) A transaction is treated as a single unit: all or none.
Which transaction property ensures changes made by a transaction are saved permanently?
- A) Atomicity
- B) Durability
- C) Isolation
- D) Consistency
  Answer: B) Durability

Commit

What is the main purpose of the COMMIT command?
- A) To undo all changes made by the transaction.
- B) To save all changes made by the transaction permanently.
- C) To create a checkpoint in the transaction.
- D) To terminate a transaction without saving changes.
  Answer: B) To save all changes made by the transaction permanently.
After issuing a COMMIT, which of the following is true?
- A) The transaction can be rolled back.
- B) The changes are lost if the system crashes.
- C) The transaction is saved permanently.
- D) A new SAVEPOINT is created automatically.
  Answer: C) The transaction is saved permanently.

Rollback

The ROLLBACK command is used to:
- A) Save changes made by the transaction.
- B) Undo all changes made during the transaction.
- C) Create a checkpoint in the transaction.
- D) Combine multiple transactions into one.
  Answer: B) Undo all changes made during the transaction.
If a failure occurs during a transaction, which command is most appropriate to maintain database consistency?
- A) COMMIT
- B) SAVEPOINT
- C) ROLLBACK
- D) ABORT
  Answer: C) ROLLBACK

Savepoint

What does the SAVEPOINT command do in a transaction?
- A) Permanently saves all changes made so far.
- B) Creates a checkpoint to which the transaction can roll back.
- C) Terminates a transaction and rolls back all changes.
- D) Makes the transaction restart from the beginning.
  Answer: B) Creates a checkpoint to which the transaction can roll back.
Which command is used to undo changes made after a specific SAVEPOINT?
- A) ROLLBACK
- B) ROLLBACK TO SAVEPOINT
- C) COMMIT TO SAVEPOINT
- D) DELETE SAVEPOINT
  Answer: B) ROLLBACK TO SAVEPOINT

General Concepts

In which situation should a transaction be committed?
- A) When partial data changes are made.
- B) When the transaction completes successfully.
- C) When a system failure occurs.
- D) When there is a checkpoint created.
  Answer: B) When the transaction completes successfully.
Which of the following ensures that transactions are executed independently of each other?
- A) Durability
- B) Isolation
- C) Consistency
- D) Atomicity
  Answer: B) Isolation
What happens if a ROLLBACK is issued after a COMMIT?
- A) The changes made by the transaction are undone.
- B) The changes remain because COMMIT is irreversible.
- C) The database goes back to its initial state.
- D) It depends on the SAVEPOINT created earlier.
  Answer: B) The changes remain because COMMIT is irreversible.

Scenario-Based Questions

A transaction debits $100 from Account A and credits $100 to Account B. Which command ensures the changes are permanent?
- A) SAVEPOINT
- B) COMMIT
- C) ROLLBACK
- D) ABORT
  Answer: B) COMMIT
A transaction includes three steps: Debit, Credit, and Generate Receipt. If the receipt generation fails, which command can undo the debit and credit?
- A) COMMIT
- B) SAVEPOINT
- C) ROLLBACK
- D) ROLLBACK TO SAVEPOINT
  Answer: C) ROLLBACK
Which of the following commands allows you to undo specific parts of a transaction?
- A) SAVEPOINT
- B) COMMIT
- C) ROLLBACK
- D) ROLLBACK TO SAVEPOINT
  Answer: D) ROLLBACK TO SAVEPOINT

Advanced Concepts

When is a SAVEPOINT most useful?
- A) To mark the beginning of a transaction.
- B) To create multiple checkpoints in a long transaction.
- C) To combine two transactions.
- D) To prevent a COMMIT from being executed.
  Answer: B) To create multiple checkpoints in a long transaction.
Which of the following statements is FALSE?
- A) COMMIT makes changes permanent.
- B) ROLLBACK undoes all changes since the last COMMIT.
- C) SAVEPOINT allows partial rollback.
- D) Changes after COMMIT can be undone using ROLLBACK.
  Answer: D) Changes after COMMIT can be undone using ROLLBACK.