Level 3 - Question 6: Transaction Management with COMMIT and ROLLBACK

Solution:

-- Start transaction (implicit in Oracle)
BEGIN
    -- Deduct from Account A
    UPDATE accounts
    SET balance = balance - 500
    WHERE account_id = 'A001';
    
    -- Add to Account B
    UPDATE accounts
    SET balance = balance + 500
    WHERE account_id = 'A002';
    
    -- If both succeed, commit the transaction
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- If any error occurs, rollback
        ROLLBACK;
        RAISE;
END;
/

This PL/SQL block ensures atomicity: either both updates happen or neither does. COMMIT makes changes permanent, while ROLLBACK undoes all changes in the transaction if an error occurs.

Key Concepts: ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable transaction processing.