CA 36 – Isolation (ACID)

Source: Dev.to

Scenario

The experiment demonstrates how the Isolation property of ACID works when two sessions try to operate on the same account concurrently.

Steps

Session 1

BEGIN;
UPDATE accounts
-- (no COMMIT yet)

Session 2

SELECT * FROM accounts WHERE name = 'Alice';

Result: The balance still shows 1000, not the uncommitted value (e.g., 200).

UPDATE accounts
-- (this statement blocks, waiting for Session 1 to finish)

Commit in Session 1

COMMIT;

After the commit, Session 2’s blocked UPDATE proceeds, using the newly committed value. The final balance reflects the combined effect of both transactions.

Changing Isolation Levels

READ COMMITTED (default)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Behavior: Same as above—no dirty reads; Session 2 sees only committed data.

SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Behavior: If two transactions conflict, one will abort with an error instead of waiting silently, preventing potential anomalies.

Takeaway

Isolation ensures that concurrent transactions do not interfere with each other’s intermediate states. Even when multiple users attempt to modify the same account simultaneously, the database maintains consistency and prevents data corruption.