Money Transfer with Error Handling and Balance Validation

advanced

Bank needs to transfer $500 from Account A to Account B. Requirements: (1) Debit and credit must both succeed (atomicity), (2) Source account balance must stay ≥ 0 (consistency), (3) Concurrent transfers must not interfere (isolation), (4) Committed transfer must survive crashes (durability). Traditional approach: Separate UPDATE statements without transaction. Problem: Debit succeeds but credit fails (money disappears), or concurrent transfers cause negative balance. Solution: Use transaction with error handling and balance validation.

-- PostgreSQL money transfer with error handling

CREATE OR REPLACE FUNCTION transfer_money(

from_account_id VARCHAR(10),

to_account_id VARCHAR(10),

transfer_amount DECIMAL(10,2)

) RETURNS TEXT AS $$

DECLARE

from_balance DECIMAL(10,2);

to_balance DECIMAL(10,2);

BEGIN

-- Start transaction (implicit in function)

-- Step 1: Lock source account and check balance

SELECT balance INTO from_balance

FROM accounts

WHERE account_id = from_account_id

FOR UPDATE; -- Lock row to prevent concurrent updates

IF NOT FOUND THEN

RAISE EXCEPTION 'Source account % not found', from_account_id;

END IF;

IF from_balance < transfer_amount THEN

RAISE EXCEPTION 'Insufficient funds: balance=%, amount=%', from_balance, transfer_amount;

END IF;

-- Step 2: Lock destination account

SELECT balance INTO to_balance

FROM accounts

WHERE account_id = to_account_id

FOR UPDATE; -- Lock row

IF NOT FOUND THEN

RAISE EXCEPTION 'Destination account % not found', to_account_id;

END IF;

-- Step 3: Debit source account

UPDATE accounts

SET balance = balance - transfer_amount,

updated_at = CURRENT_TIMESTAMP

WHERE account_id = from_account_id;

-- Step 4: Credit destination account

UPDATE accounts