Module lessons (4/4)
Isolation levels
Beyond explicit FOR UPDATE, transactions also have innate mathematical rules baked deep into the engine that govern how much energy to spend isolating each individual interaction by default. This is called the Isolation Level. In the SQL world we know 4 standard ones, in order of power and safety but obviously the inverse in terms of performance.
READ UNCOMMITTED:Anarchic madness, your transaction will see every change made by others including temporary data from people halfway through their own scripts. Nobody uses it on large DBs, because of constant Dirty Read errors.READ COMMITTED:(Default for Postgres). You'll instantly see new data as soon as a third party issues a COMMIT, but you can fall victim to phantom reads (seeing new records midway through your long workflows) or a non-linear consistent view.REPEATABLE READ:Heavier. For the duration of your big time-window session I "see" the database frozen at the moment my query started, totally ignoring and disregarding even the official commits completed by other people! It protects against records magically appearing, guaranteeing stability.SERIALIZABLE:God tier (Super slow). Treats every overlapping transaction by blocking them and executing them serially in line in a single lane, ruthlessly aborting those that collide, eliminating at the root even the scientific possibility of the slightest collision, sacrificing a solid 90% of high throughput.
How do you set it?
Instead of the normal BEGIN; you type:
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- and you proceed normally with your hermetically sealed isolation
UPDATE products SET stock = 10;
COMMIT;Or in some variants and older engines you assign it after starting:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;Try it yourself
Start a transaction formally setting the global level for the lifetime of the query to REPEATABLE READ to create a frozen, protected exchange.\nRemember that at the query level it serves no purpose unless followed up by something else, so close it empty with 'COMMIT'.
Show hint
Open with BEGIN ISOLATION LEVEL REPEATABLE READ; and close with COMMIT;
Solution available after 3 attempts
Let's try the crazy serializable level and insert some fictitious data.\nOpen a 'SERIALIZABLE' block.\nUpdate the name of category Id=2 to 'Nuovo Nome Segreto'.\nRun the final atomic, settling commit.
Show hint
BEGIN ISOLATION LEVEL SERIALIZABLE;\nUPDATE categories SET name = 'Nuovo Nome Segreto' WHERE id = 2;\nCOMMIT;
Solution available after 3 attempts