Search code examples
sqldatabasetransactionsdirty-writelost-update

Isolation level: Difference between dirty write and lost update

I'm reading A Critique of ANSI SQL Isolation Levels and get confused by the two definitions:

Dirty write

w1[x]...w2[x]...((c1 or a1) and (c2 or a2) in any order)

Lost update

r1[x]...w2[x]...w1[x]...c1

Doesn't the history definition of lost update include dirty write? (The w2[x]...w1[x] part). If yes then if we prevent dirty write we prevent lost update, right? I know this is clearly wrong but I don't understand why. Can anyone give some hints on what I misunderstood here? Many thanks!

I found the article explaining this example:

enter image description here

There is no Dirty Write since T2 commits before T1 writes x.

But the definition of lost update does not require the 2nd write to happen after the first transaction commits, right?

Solution

    • Dirty write is that a transaction updates or deletes (overwrites) the uncommitted data which other transactions insert, update or delete. *Basically, dirty write doesn't occur with all isolation levels in many databases.

    • Lost update is that two transactions read the same row to update it but the first committed update is overwritten by the second committed update. *Basically, lost update doesn't occur in SERIALIZABLE isolation level in many databases and lost update is prevented using SELECT FOR UPDATE in MySQL and PostgreSQL.

    The difference between dirty write and lost update is that uncommitted data is overwritten or committed data is overwritten.

    For example, there is product table with id, name and stock as shown below. *The stocks of the products decrease when customers buy the products.

    product table:

    id name stock
    1 Apple 10
    2 Orange 20

    These steps below shows dirty write:

    Flow Transaction 1 (T1) Transaction 2 (T2) Explanation
    Step 1 BEGIN; T1 starts.
    Step 2 BEGIN; T2 starts.
    Step 3 SELECT stock FROM product WHERE id = 2;

    20    		 T1 reads 20 which is updated later to 13 because a customer buys 7 oranges.
    Step 4 SELECT stock FROM product WHERE id = 2;

    20    		 T2 reads 20 which is updated later to 16 because a customer buys 4 oranges.
    Step 5 UPDATE product SET stock = '13' WHERE id = 2; T1 updates 20 to 13.
    Step 6 UPDATE product SET stock = '16' WHERE id = 2; T2 updates 13 to 16 before T1 commits.

    *Dirty write occurs.
    Step 7 COMMIT; T1 commits.
    Step 8 COMMIT; T2 commits.

    These steps below shows lost update:

    Flow Transaction 1 (T1) Transaction 2 (T2) Explanation
    Step 1 BEGIN; T1 starts.
    Step 2 BEGIN; T2 starts.
    Step 3 SELECT stock FROM product WHERE id = 2;

    20    		 T1 reads 20 which is updated later to 13 because a customer buys 7 oranges.
    Step 4 SELECT stock FROM product WHERE id = 2;

    20    		 T2 reads 20 which is updated later to 16 because a customer buys 4 oranges.
    Step 5 UPDATE product SET stock = '13' WHERE id = 2; T1 updates 20 to 13.
    Step 6 COMMIT; T1 commits.
    Step 7 UPDATE product SET stock = '16' WHERE id = 2; T2 updates 13 to 16 after T1 commits.
    Step 8 COMMIT; T2 commits.

    *Lost update occurs.