sql / expert
Snippet
Transaction Isolation Levels and Atomicity
This snippet demonstrates the use of high-level transaction isolation to ensure data integrity during a fund transfer. By setting the isolation level to SERIALIZABLE, the database prevents phantom reads and write skew, ensuring that the two UPDATE operations are treated as a single atomic unit of work.
snippet.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN;UPDATE accountsSET balance = balance - 500WHERE account_id = 'A' AND balance >= 500;UPDATE accountsSET balance = balance + 500WHERE account_id = 'B';COMMIT;
Breakdown
1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Sets the highest isolation level to prevent concurrent transactions from interfering with data consistency.
2
BEGIN;
Starts the explicit transaction block.
3
UPDATE accounts ... WHERE balance >= 500;
Subtracts the amount only if the balance satisfies the business rule constraint.
4
COMMIT;
Permanently saves all changes made during the transaction to the database.