Secure Funds Transfer Using Transactional Updates

Transaction for Fund Transfer between Accounts

Write a PostgreSQL query to transfer funds between two accounts using a transaction.

Solution:

-- Begin the transaction.
BEGIN;

-- Subtract $100 from the sender's account.
UPDATE Accounts
SET balance = balance - 100
WHERE account_id = 1;

-- Add $100 to the receiver's account.
UPDATE Accounts
SET balance = balance + 100
WHERE account_id = 2;

-- Commit the transaction to complete the transfer.
COMMIT;

Explanation:

• Purpose of the Query:
• To ensure that the debit and credit operations occur together atomically.
• Prevents scenarios where one update occurs without the other.
• Key Components:
• Two UPDATE statements: one for debiting and one for crediting.
• BEGIN and COMMIT to ensure atomicity.
• Real-World Application:
• Essential in financial systems where accurate fund transfers are critical.

Notes:

• In the event of an error, the transaction would need to be rolled back to avoid inconsistent balances.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to transfer funds between two accounts ensuring the sender's balance is sufficient, then commit the transaction.
• Write a PostgreSQL query to perform a fund transfer between two accounts and insert a record into a Transfers log table within a transaction.
• Write a PostgreSQL query to lock the sender's and receiver's account rows with SELECT FOR UPDATE, then update their balances as part of a fund transfer transaction.
• Write a PostgreSQL query to transfer funds between two accounts using SAVEPOINT to allow partial rollback if a condition is not met.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Multi-Operation Transaction with COMMIT.