sql / expert
Snippet
Transaction Isolation Levels and Integrity
Isolation levels define how transaction integrity is visible to other users and systems. SERIALIZABLE is the highest level, preventing dirty reads, non-repeatable reads, and phantom reads by simulating sequential execution.
snippet.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 = 102;COMMIT;
Breakdown
1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Ensures the highest level of data consistency for the upcoming transaction.
2
START TRANSACTION
Begins a logical unit of work that must succeed or fail as a whole.
3
COMMIT
Permanently saves all changes made during the transaction to the database.