Isolation level: Difference between dirty write and lost update

Question

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:

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?

Krein

Answer 1

• 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.

Twelfth

This answer is partially true,in your example in order for dirty read problem to occur transaction 2 must read uncommitted data from transaction 1 or vice versa. So you must switch Step 4 with Step 5 and finally transaction 1 must be failed. – Glaze

If T2 runs after T1 committed, we cannot say T1's update is lost. Why ? why must be in concurrent context ? – Climacteric