Isolation level: Difference between dirty write and lost update

user_8159477 0
Viewed 84

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?

lost-update dirty-write transactions sql database
1 Answers

  • Dirty write is that a transaction updates or deletes 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 and update the same row but the first committed updated row is overwritten by the last committed updated row. *Basically, lost update occurs with some isolation levels in many databases.

For example, there is "product" table with "id", "name" and "stock" as shown below:

id name stock
1 Apple 5
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".
Step 4 SELECT stock FROM product WHERE id = 2;

20		 T2 reads "20".
Step 5 UPDATE product SET stock = '19' WHERE id = 2; T1 updates "20" to "19".
Step 6 UPDATE product SET stock = '17' WHERE id = 2; T1 updates "19" to "17".

*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".
Step 4 SELECT stock FROM product WHERE id = 2;

20		 T2 reads "20".
Step 5 UPDATE product SET stock = '19' WHERE id = 2; T1 updates "20" to "19".
Step 6 COMMIT; T1 commits.
Step 7 UPDATE product SET stock = '17' WHERE id = 2; T1 updates "19" to "17".

*Lost update occurs.
Step 8 COMMIT; T2 commits.
user_81724390
Related