What Happens If a Bank Transfer Fails Halfway? Understanding ACID Properties in SQL

Bank transfers occasionally encounter failures when the process is interrupted. Imagine a scenario where ₹1,000 is deducted from Jeni's account but the transfer to Dravid's account is halted mid-operation. If the system crashes before the credit is applied, Jeni's balance remains at ₹9,000 while Dravid's stays at ₹5,000. This incomplete transaction represents a critical issue in banking systems.

To prevent such problems, databases utilize transactions and ACID properties. A transaction is a single logical unit of work that groups multiple database operations. In our example, two UPDATE statements would be part of a single transaction: one to deduct ₹1,000 from Jeni's account and another to add ₹1,000 to Dravid's balance.

The ACID properties ensure transactions behave reliably. Atomicity means all operations within a transaction are treated as one unit. Either all succeed or none take effect. For instance, if the transaction fails after deducting ₹1,000 from Jeni's balance but before crediting Dravid's, the transaction is rolled back, restoring both accounts to their original balances.

Consistency ensures transactions maintain data integrity by moving the database from a valid state to another while adhering to defined rules. In our example, the total balance remains unchanged at ₹15,000 before and after the transfer, respecting consistency rules.

Isolation prevents concurrent transactions from interfering with each other. Multiple users may perform operations simultaneously, so isolation controls how transactions interact with each other's changes. Durability guarantees that once a transaction is committed, its effects remain permanent, even in the event of a system failure.

By enforcing ACID properties, databases ensure reliable transactions, maintaining data integrity and preventing incomplete or inconsistent states when errors occur.