sql / intermediate
Snippet
Guaranteeing Data Consistency with Transactional Rollbacks
Transactions bundle multiple database commands into a single atomic operation. If an error occurs prior to completion, changes can be safely discarded using ROLLBACK, preserving data consistency.
snippet.sql
sql
1
2
3
4
START TRANSACTION;UPDATE accounts SET balance = balance - 500.00 WHERE account_id = 101;UPDATE accounts SET balance = balance + 500.00 WHERE account_id = 102;COMMIT;
Breakdown
1
START TRANSACTION;
Initiates an explicit transaction block, turning off implicit auto-commit mode.
2
UPDATE accounts SET balance = balance - 500.00 WHERE account_id = 101;
Deducts the specified funds from the sender's account balance within the isolated state.
3
UPDATE accounts SET balance = balance + 500.00 WHERE account_id = 102;
Credits the identical amount to the receiver's account balance.
4
COMMIT;
Permanently persists all balance modifications performed during the transaction.