sql / intermediate
Snippet
Explicit Transaction Isolation Level Configuration
Configuring transaction isolation levels controls how database engines manage concurrent access and prevent phenomena such as dirty reads, non-repeatable reads, and phantom reads. Setting the level to SERIALIZABLE ensures strict transaction execution order, making concurrent transactions behave as if executed sequentially.
snippet.sql
sql
1
2
3
4
5
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;START 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;
Sets the highest isolation level for the upcoming transaction to prevent dirty reads, non-repeatable reads, and phantom reads.
2
START TRANSACTION;
Marks the beginning of an explicit transaction block.
3
UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
Deducts 500 from the source account balance within the isolated transaction scope.
4
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;
Adds 500 to the destination account balance to maintain funds balance integrity.
5
COMMIT;
Persists all modified data changes permanently to the database disk storage.