-- Application-level advisory lock (prevents race conditions)
SELECT PG_ADVISORY_LOCK(12345); -- Session-level lock

  -- Critical section
  UPDATE account SET balance = balance - 100 WHERE id = 1;
  UPDATE account SET balance = balance + 100 WHERE id = 2;

SELECT PG_ADVISORY_UNLOCK(12345);

-- Transaction-level advisory lock (auto-released on commit/rollback)
BEGIN;
  SELECT PG_ADVISORY_XACT_LOCK(account_id) FROM accounts WHERE id IN (1,2) ORDER BY id;
  -- Always lock in same order to prevent deadlocks!
COMMIT;