Níveis de isolamento

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