Transaction Concepts – Commit, Rollback, Savepoint

  • A transaction is a group of tasks that must be done together.
    Example: Transferring money from one account to another (debit from one, credit to another).

2. Transaction Properties (ACID)

  1. Atomicity: Do all or none. If one step fails, undo everything.
    Example: Debit but no credit? Undo the debit.
  2. Consistency: The database should remain correct before and after the transaction.
    Example: Total money stays the same.
  3. Isolation: Each transaction works as if it’s the only one happening.
    Example: No mixing of two transactions at the same time.
  4. Durability: Once committed, changes stay even if there’s a crash.
    Example: Saved data doesn’t disappear.

3. Commands for Transactions

  1. COMMIT
    • Saves all changes made in the transaction permanently.
      Example: Bank transfer is complete when you COMMIT.
    • Keyword: Save changes.
  2. ROLLBACK
    • Undo all changes since the transaction started (like CTRL+Z).
      Example: Debit is done, but credit fails → ROLLBACK to undo.
    • Keyword: Undo changes.
  3. SAVEPOINT
    • Create a checkpoint to go back to if needed.
      Example: SAVEPOINT before debit → rollback only to this point if credit fails.
    • Keyword: Checkpoint.

4. How They Work Together

  1. Start a Transaction:
    • Example: Debit $100 from Account A.
  2. SAVEPOINT:
    • Save a checkpoint after debit.
  3. Problem?:
    • Use ROLLBACK TO SAVEPOINT: Undo just the debit.
  4. Success?:
    • Use COMMIT: Save everything.

5. Quick Summary

  • COMMIT = Save changes permanently.
  • ROLLBACK = Undo everything.
  • SAVEPOINT = Undo specific parts by marking checkpoints.

6. Simple Example

Scenario: Transfer $100 from Account A to Account B.
Commands:

  1. Start transaction: Begin work.
  2. Debit Account A: Mark a SAVEPOINT.
  3. Credit Account B.
  4. Problem? ROLLBACK TO SAVEPOINT (undo debit only).
  5. Success? COMMIT (save everything).

MCQ

Basic Concepts of Transactions

  1. Which of the following is NOT a property of a transaction?
    • A) Atomicity
    • B) Consistency
    • C) Durability
    • D) Scalability
      Answer: D) Scalability
  2. 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.
  3. Which transaction property ensures changes made by a transaction are saved permanently?
    • A) Atomicity
    • B) Durability
    • C) Isolation
    • D) Consistency
      Answer: B) Durability

Commit

  1. 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.
  2. 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

  1. 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.
  2. 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

  1. 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.
  2. 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

  1. 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.
  2. Which of the following ensures that transactions are executed independently of each other?
    • A) Durability
    • B) Isolation
    • C) Consistency
    • D) Atomicity
      Answer: B) Isolation
  3. 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

  1. 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
  2. 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
  3. 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

  1. 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.
  2. 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.