sql / intermediate
Snippet
Controlling Transaction Concurrency with SQL Isolation Levels
Setting isolation levels controls how changes made by concurrent transactions are visible to one another. REPEATABLE READ prevents non-repeatable reads during atomic state updates.
snippet.sql
sql
1
2
3
4
5
6
7
8
9
10
11
12
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION;UPDATE accountsSET balance = balance - 250.00WHERE account_id = 101;UPDATE accountsSET balance = balance + 250.00WHERE account_id = 202;COMMIT;
Breakdown
1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Configures the transaction isolation level to ensure consistent reads.
2
START TRANSACTION;
Initiates an explicit transaction boundary for a series of statements.
3
UPDATE accounts SET balance = balance - 250.00 WHERE account_id = 101;
Deducts funds from the sender account as an atomic step.
4
UPDATE accounts SET balance = balance + 250.00 WHERE account_id = 202;
Credits funds to the recipient account within the same transaction scope.
5
COMMIT;
Permanently saves all updates committed during the transaction.