SERIALIZABLE Isolation Deep Dive
SERIALIZABLE Isolation Deep Dive is a SQL statement in the Transactions category. Highest isolation level: prevents dirty reads, non-repeatable reads, AND phantom reads. Least concurrent. The syntax is SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;. It returns serializable transaction. A typical example: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; SELECT SUM(amount) FROM orders WHERE status = 'pending'; -- No other transaction can INSERT/UPDATE/DELETE pending orders -- Because of range locks -- SELECT ... WHERE status = 'pending' locks the range of rows matching this predicate -- New rows with status='pending' cannot be inserted (phantom protection) -- Use when absolute consistency is required: -- Financial calculations, inventory allocation A close relative is BEGIN / START TRANSACTION, which begins a new transaction. Changes made within are invisible to others until COMMIT. A close relative is COMMIT, which saves all changes made in the current transaction permanently. A close relative is ROLLBACK, which undoes all changes made in the current transaction (or to a savepoint).