A banking system transection processing using PL SQL

A banking system performs fund transfers in two separate steps:

It deducts the transfer amount from the sender’s account in the Accounts table.

It adds the same amount to the recipient’s account in the Accounts table.

Now assume that the amount is successfully deducted from the sender’s account, but before the system can credit the recipient’s account, the system crashes.

Question 1:
Which ACID property is violated in this situation?
Explain why this failure results in inconsistent account balances.

Question 2:
Write a PL/SQL block (or SQL transaction) that ensures the fund transfer is handled atomically. Your solution must include COMMIT and ROLLBACK statements to maintain consistency.

Question 3:
Explain how your solution guarantees that either both the debit and credit operations are completed successfully, or neither operation is applied.

Answer:

1. Violated ACID Property

Violated Property: Atomicity

Atomicity means a transaction must be completed fully or not executed at all (all-or-nothing principle). In this scenario, the system successfully deducts money from the sender’s account, but crashes before crediting the recipient’s account.

As a result:

• Money is deducted from the sender.
• The recipient does not receive the money.
• Total system balance becomes inconsistent.

Therefore, the Atomicity property is violated because the transaction was only partially completed.

---

2. PL/SQL Block for Atomic Fund Transfer

DECLARE
    v_sender_id      Accounts.AccountID%TYPE := 101;
    v_receiver_id    Accounts.AccountID%TYPE := 202;
    v_amount         NUMBER := 5000;
    v_balance        NUMBER;
BEGIN
    -- Check sender balance
    SELECT Balance INTO v_balance
    FROM Accounts
    WHERE AccountID = v_sender_id
    FOR UPDATE;

    IF v_balance < v_amount THEN
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient Balance');
    END IF;

    -- Step 1: Debit sender
    UPDATE Accounts
    SET Balance = Balance - v_amount
    WHERE AccountID = v_sender_id;

    -- Step 2: Credit receiver
    UPDATE Accounts
    SET Balance = Balance + v_amount
    WHERE AccountID = v_receiver_id;

    -- If both operations succeed
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        -- If any error occurs
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Transaction Failed: ' || SQLERRM);
END;
/

---

3. How This Ensures Atomicity

• Both debit and credit operations are executed within a single transaction block.
• COMMIT executes only if both updates succeed.
• If any error occurs, control moves to the EXCEPTION block.
• ROLLBACK reverses all changes made during the transaction.

This guarantees that either both debit and credit are successfully completed, or neither operation is applied, thus maintaining database consistency.

---

Conclusion

The violated ACID property is Atomicity. Using transaction control statements (COMMIT and ROLLBACK) ensures that fund transfers are executed in an all-or-nothing manner, preventing inconsistent account balances.