sql / intermediate
Snippet
Ensuring Data Integrity with Atomicity Using Explicit Transactions
Transactions bundle multiple SQL data modification statements into a single, indivisible logical unit of work. By committing the transaction explicitly at the end, ANSI SQL guarantees that either all operations succeed completely or no changes persist, upholding database ACID properties.
snippet.sql
sql
1
2
3
4
5
6
7
8
9
UPDATE accountsSET balance = balance - 500WHERE account_id = 101;UPDATE accountsSET balance = balance + 500WHERE account_id = 202;COMMIT;
Breakdown
1
UPDATE accounts
Initiates the first balance modification within the implicit transaction block.
2
SET balance = balance - 500
Deducts funds from the source account balance.
3
WHERE account_id = 101;
Targets the specific sender account ID.
4
UPDATE accounts
Initiates the second modification statement as part of the same atomic unit.
5
SET balance = balance + 500
Credits funds to the destination account balance.
6
WHERE account_id = 202;
Targets the specific recipient account ID.
7
COMMIT;
Permanently applies all pending changes made within the current transaction to the database.