sql / intermediate
Snippet
Setting Explicit Isolation Levels in Transactions
Transaction isolation levels control how concurrent database transactions interact with each other. The SERIALIZABLE isolation level provides the highest degree of data safety by preventing dirty reads, non-repeatable reads, and phantom reads.
snippet.sql
sql
1
2
3
4
5
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTION;UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;COMMIT;
Breakdown
1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Defines the isolation level for the upcoming transaction to prevent concurrency anomalies.
2
BEGIN TRANSACTION;
Marks the explicit starting point of an atomic database transaction.
3
UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
Deducts funds from the source account inside the transaction block.
4
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;
Adds funds to the target account to maintain financial consistency.
5
COMMIT;
Permanently applies all modifications made during the transaction to the database.