-- 12_pessimistic_locking_transfer.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Pessimistic locking for safe fund transfers.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.

-- Pessimistic locking for transfers
BEGIN;
SELECT * FROM bank.accounts WHERE account_id = 1 FOR UPDATE;
SELECT * FROM bank.accounts WHERE account_id = 2 FOR UPDATE;
UPDATE bank.accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE bank.accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;
