Trust Coding : Programming: DBMS Transaction ACID Violation and SQL Solution Example

DBMS Transaction Properties and Solution for Banking Transfer Failure

📌 Question

Suppose a banking database contains two accounts: Account A with balance BDT 500 and Account B with balance BDT 300. A transaction is initiated to transfer BDT 200 from Account A to Account B. During the process:

The debit operation on Account A is successful (new balance = BDT 300).
Before the credit operation on Account B is applied, the system crashes.

Tasks:

Write down the four properties of a DBMS transaction.
Identify which property is violated in this scenario.
Justify your answer.
Suggest a solution using SQL to prevent this type of violation.

✅ Answer

1. Four Properties of a DBMS Transaction (ACID)

Atomicity: The transaction is all-or-nothing. Either all operations succeed, or none do.
Consistency: A transaction transforms the database from one consistent state to another.
Isolation: Transactions execute independently without interference.
Durability: Once a transaction commits, its changes are permanent even in case of a crash.

2. Property Violated

Atomicity is violated in this scenario.

3. Justification

The debit from Account A succeeded, but the credit to Account B did not occur due to system crash.
This results in a partially completed transaction, leaving the database in an inconsistent state.
Atomicity ensures that either both debit and credit happen or none happen, which was not enforced here.

4. SQL Solution to Prevent Violation

Use an SQL transaction block with COMMIT and ROLLBACK to ensure atomicity:

-- Start transaction
BEGIN
   -- Deduct 200 from Account A
   UPDATE Accounts
   SET balance = balance - 200
   WHERE account_id = 'A';

   -- Add 200 to Account B
   UPDATE Accounts
   SET balance = balance + 200
   WHERE account_id = 'B';

   -- Commit if both updates succeed
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      -- Rollback if any error occurs
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('Transaction failed and rolled back.');
END;
/

📌 Summary

The four ACID properties are Atomicity, Consistency, Isolation, and Durability.
Atomicity is violated because only the debit was applied and the credit failed.
Using COMMIT and ROLLBACK ensures either the full transaction succeeds or no changes are applied.
This prevents inconsistent account balances in real-world banking operations.

Trust Coding : Programming

DBMS Transaction ACID Violation and SQL Solution Example