sql / intermediate
Snippet
Ensuring Atomicity with Explicit Transaction Boundaries
Transactions bundle multiple SQL commands into a single logical unit of work adhering to ACID principles. Placing state-changing commands between explicit transaction boundary statements ensures that either all modifications persist together or none do if an error occurs.
snippet.sql
sql
1
2
3
4
BEGIN TRANSACTION;UPDATE accounts SET balance = balance - 150.00 WHERE account_id = 101;UPDATE accounts SET balance = balance + 150.00 WHERE account_id = 202;COMMIT;
Breakdown
1
BEGIN TRANSACTION;
Explicitly starts a new transaction context for isolated data manipulation.
2
UPDATE accounts SET balance = balance - 150.00 WHERE account_id = 101;
Deducts 150.00 from the source account balance within the transaction.
3
UPDATE accounts SET balance = balance + 150.00 WHERE account_id = 202;
Adds 150.00 to the destination account balance within the transaction.
4
COMMIT;
Permanently saves all pending modifications from the transaction to the database.