SQL
Ensure Data Consistency with SQL Transactions
Understand how to group multiple SQL statements into an atomic unit using transactions, guaranteeing data integrity even if errors occur during execution.
START TRANSACTION; -- Or BEGIN TRANSACTION / BEGIN;
UPDATE accounts
SET balance = balance - 100.00
WHERE account_id = 101;
UPDATE accounts
SET balance = balance + 100.00
WHERE account_id = 102;
-- Optional: Simulate an error to test ROLLBACK
-- INSERT INTO non_existent_table (col) VALUES (1);
COMMIT; -- Or ROLLBACK; if an error occurs or transaction needs to be undone.
How it works: SQL transactions are crucial for maintaining data integrity in web applications, especially for operations that involve multiple interdependent changes. This snippet shows a simple fund transfer between two accounts. By wrapping the `UPDATE` statements within `START TRANSACTION` (or `BEGIN`) and `COMMIT`, either all changes are successfully applied to the database, or if any statement fails (or `ROLLBACK` is explicitly called), all changes within the transaction are undone, ensuring the database remains in a consistent state.