What are dirty writes? What happens if they are not allowed?

Question

I read about database isolation levels and transactional phenomena. Dirty reads are obvious but I don't understand dirty writes.

All descriptions of dirty write say something like:

A dirty write is when a process save[s to] a file data that has already been changed on disk by another process. The last process will then overwrite the data of the first one. https://gerardnico.com/data/property/dirty_write

Some other descriptions use examples to demonstrate a dirty write but not what will happen to solve that. https://esb-dev.github.io/mat/IsoLevel.pdf

This is a dirty write from the example:

1. Saldo starts with 100
2. T2 begins: update Acct set Saldo = 200 where Acct = 1
3. T1 begins: update Acct set Saldo = 250 where Acct = 1
4. T1 commits => Saldo=250
5. T2 commits => Saldo=200

I don't know what will happen when the isolation level does not allow dirty writes.

• T1 fails at commit because the change of T2 isn't comitted at that time
• T1 commits successfully and T2 commit fails because T1 (not committed yet) overwrites that change
• T1 and T2 commit successfully but T1 wins silently (saldo 200 instead of 250)

I don't know what I should expect from transaction management. The example, modified:

1. Saldo starts with 100
2. T1 begins: update Acct set Saldo = 200 where Acct = 1
3. T2 begins: update Acct set Saldo = 250 where Acct = 1
4. T2 commits => Saldo=?
5. T1 commits => Saldo=?

Do we have a dirty write here? And what is the result if dirty write is allowed?

• Saldo = 250 because the last update wins
• Saldo = 200 because the last commit wins

I have an additional question about Java/Spring JPA/Hibernate: Are write statements not sent to the database unless hibernate does a commit? Some isolation levels and phenomena only make sense if all statements are always transmitted instantly to the database.

Pericynthion

Start by researching by reading an authoritative reviewed source--a DMBS manual or documentation or a textbook. Not web sites. Especially not web sites with bad grammar. Otherwise you are just asking us to (re)write yet another presentation. Dozens of published academic information modeling & DB design textbooks are online free in pdf. Ask just 1 question the 1st place you get stuck. If you are testing using a DBMS then tag with it, because SQL isolation levels are a specification that can be satisfied differently. See How to Ask, other help center links & the voting arrow mouseover texts. – Antinomian

You may want to lock into optimistic locking. Maybe the decision to change the saldo to either value 200 or 250 depends on the saldo being 100 currently. This can be modeled by adding a version number to the database row and letting the transaction fail if the version number is not what it was when reading the entity. That way only either T1 or T2 will succeed (which one is probaby undefined though, but at least you will know which one did win because the other one fails) – Messiaen

Answer 1

In general transactions prevent dirty writes by locking rows that will be written to until the end of the transaction. This means that neither transaction will fail, it's just that the second write will be delayed until the first transaction is finished. I don't think there are any isolation levels that don't do this.

So when you think about dirty writes you can imagine not having any isolation. In your first example I would expect that Saldo's final account to be 250 because the update to 200 was overwritten.

Note that it's not necessarily a problem for one transaction to overwrite the write of another transaction. Even if we imagine that the two transactions ran serially (i.e. everything in the first transaction completes before the second transaction starts) and therefore completely isolated we still expect one transaction to overwrite the other transaction.

I think a better example of a dirty write would be if transaction A and B both try to write to rows 1 and 2, but transaction A writes last to row 1 and transaction B writes last to row 2. Then you will see a result that doesn't match our expectation of isolation.

For a concrete example you can imagine you have two rows representing first and second place of a race. The first transaction set racer 1 in first and racer 2 in second place. Moments later a second transaction wants to update to put racer 2 in first place and racer 1 in second place. If dirty writes are possible then you could end up with racer 1 (or racer 2) in both first and second place slots.

As I mentioned already you can fix this problem by using transaction of any isolation level which will lock rows that are being written to from being written to by any other transactions until the changes are committed.

Ingenuity

Answer 2

Dirty writes ARE not allowed. The problem with your examples is that the 2nd transaction which tries to update a row commits first. But in reality this is not the case. The first update statement blocks the row till the end of the first transaction. And the 2nd transaction should wait.

Malebranche

Answer 3

• Dirty write is that a transaction updates or deletes (overwrites) the uncommitted data which other transactions insert, update or delete.

If dirty write is not allowed and T1(Transaction 1) fisrt updates a row, then the row is locked by T1 so T2(Transaction 2) needs to wait to update the same row until T1 commits(unlocks the same row). *T2 can still update other unlocked rows except the row locked by T1.

I could experiment not-allowd dirty write with MySQL and 2 command prompts as shown below. But, I couldn't experiment allowed dirty write with MySQL even though I set READ UNCOMMITTED which is the most loose transaction isolation level. *Basically, dirty write is not allowed with all isolation levels in many databases.

For "not-allowd dirty write" experiment, I set READ UNCOMMITTED isolation level:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

And, I created person table with id and name as shown below.

person table:

id	name
1	John
2	David

Now, I did these steps below with MySQL queries:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	UPDATE person SET name = 'Tom' WHERE id = 2;		T1 updates David to Tom so this row is locked by T1 until T1 commits.
Step 4		UPDATE person SET name = 'Lisa' WHERE id = 2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	T2 cannot update Tom to Lisa before T1 commits because this row is locked by T1 so error occurs. *Dirty write is not allowed.
Step 5	COMMIT;		T1 commits.
Step 6		UPDATE person SET name = 'Lisa' WHERE id = 2;	Now, T2 can update Tom to Lisa because T1 has already committed(unlocked this row).
Step 7		COMMIT;	T2 commits.

Finally, I'm not exaxtly sure about what happens if dirty write is allowed but I think if dirty write is allowed, the last updated data should be used whether it's committed first or last.

Implead