Banking Transaction Scenario

Hook: Interviewers love this question because a bank transfer is the cleanest way to test whether you truly understand the phrase all or nothing.

Question: In a banking transaction scenario, how do you move money from one account to another safely in SQL?

Answer: Put the debit and credit inside one explicit transaction. First subtract from the source account, then add to the destination account, and commit only when both steps succeed. If anything fails, roll back so the database never shows money leaving one account without arriving in the other.

Interview-Ready Answer: I would wrap the transfer in a single SQL transaction so it is atomic, which means either the whole transfer happens or none of it does. I would also guard against overdrafts by checking the balance before debiting, or by enforcing a CHECK constraint so the database protects the rule for me. That way I avoid half-finished transfers, inconsistent balances, and race conditions when two sessions try to update the same account at once.

What this really means

A bank transfer is a classic transaction, which is a group of SQL changes treated as one unit. The key idea is ACID: Atomicity means all or nothing, Consistency means rules stay valid, Isolation means other sessions do not see a half-done transfer, and Durability means a committed transfer survives a crash.

How it works under the hood

1. The database starts a transaction and assigns it a transaction ID. From this point, changes are not final yet.
2. The source row is read and locked. A lock is like a temporary sign that says, “another writer must wait.” If you need to read first, many systems use SELECT ... FOR UPDATE to lock before editing.
3. The debit happens only if the account has enough money. This is where you prevent negative balances.
4. The credit happens next. Both changes belong to the same unit of work, so they must succeed together.
5. The engine writes the change to a transaction log, often called WAL or redo log. The log is the safety net that makes recovery possible after a crash.
6. COMMIT makes the transfer durable. If the app crashes before commit, the database can roll back the incomplete work from the log.

Autocommit vs explicit transaction

When and why to use it

Use a transaction any time several changes must stay in sync: money movement, seat booking, inventory checkout, loyalty points, or ledger posting. In banking, the rule is simple: if one side fails, the whole story fails.

Performance and real numbers

With an index on account_id, each row lookup is usually around O(log N). The real cost is not the math; it is the lock and the log flush. For OLTP systems, keep transactions short, often in the low milliseconds to tens of milliseconds, because long transactions increase waiting and deadlock risk. Also, never store money in floating-point types such as FLOAT; use NUMERIC or DECIMAL so you do not get rounding bugs like 0.3000000004.

Important edge cases

• Overdrafts: stop them with a balance check or a constraint.
• Deadlocks: if two transfers lock accounts in opposite order, both can wait forever until the database kills one. A common fix is to lock rows in a consistent order, such as ascending account ID.
• Crash before commit: the transaction must disappear completely, not partially.
• Isolation level: READ COMMITTED is common, but stronger levels like SERIALIZABLE reduce anomalies at the cost of more retries.

Memory idea: think of a transfer like moving a sealed envelope from one safe to another. You do not announce it done until the envelope is in the second safe and the door is locked.

Real-world story

Imagine a mobile banking app with a core ledger service. A customer sends $200 from checking to savings. The service first debits checking, then credits savings, and both actions must be part of the same SQL transaction.

What goes wrong when developers misunderstand this? Suppose the debit is committed first, then the app times out before the credit happens. The user sees missing money, support sees angry tickets, and the reconciliation job later reports that the ledger no longer balances. Logs may show one successful UPDATE followed by a timeout, restart, or deadlock error. In production, that kind of bug creates both financial risk and trust damage, which is why interviewers care so much about transaction handling.

Follow-up & Tricky Questions:

• What happens if the credit step fails after the debit step? In the same transaction, the database aborts the transaction and rolls back both changes, so the source account is not left short.
• Why would you use SELECT ... FOR UPDATE? It locks the rows you plan to change, which prevents another session from reading and updating them at the same time and causing a lost update.
• Which isolation level would you pick for transfers? READ COMMITTED is common with proper locking, but SERIALIZABLE gives the strongest safety when you can tolerate retries under heavy contention.
• How do you avoid deadlocks? Lock accounts in a consistent order, such as always by smaller account_id first, so two sessions do not wait on each other in opposite directions.
• What is a savepoint? It is a partial rollback point inside a transaction, useful when one sub-step is risky but you do not want to discard the whole transaction unless necessary.

• Tricky: Is one SQL statement always one transaction? No. In autocommit mode each statement commits by itself, but multi-step business actions need an explicit transaction.
• Tricky: Can you safely transfer money with two separate UPDATE statements and no transaction? No, because a crash between them can leave the database inconsistent.
• Tricky: Does COMMIT just mean “send the data to the client”? No. It means the database has made the change durable and valid according to its transaction rules.

Common Mistakes:

• Using autocommit for a transfer: fix this by wrapping debit and credit in one explicit transaction.
• Using FLOAT for money: fix this by using NUMERIC or DECIMAL.
• Forgetting a balance guard: fix this with WHERE balance >= amount or a CHECK constraint.
• Ignoring lock order: fix this by locking rows in the same order every time to reduce deadlocks.

Memory Hook: “Debit, credit, commit — or admit defeat and roll back.” If both doors in the bank vault cannot close, nothing is complete.

Cheat Sheet:

• Transactions make multi-step work atomic.
• Use BEGIN, then debit, then credit, then COMMIT.
• Use ROLLBACK if anything fails.
• Protect balances with NUMERIC and constraints.
• Keep transactions short and lock rows consistently.
• Think about isolation, not just syntax.

Practice Tasks:

• Create two accounts and write a transfer that moves $50 safely.
• Add a rule that rejects overdrafts and test the failure path.
• Simulate two concurrent transfers on the same account and reason about where a deadlock could happen.