Menu
Does SQL Server allow a transaction to modify the data that is currently being modified by another transaction but hasn't yet been committed? Is this possible under any of the isolation levels, let's say READ UNCOMMITTED since that is the least restrictive? Or does it completely prevent that from happening? Would you call that a 'dirty write' if it is possible?
234
A Dirty Write occurs when one transaction overwrites a value that has previously been written by another still in-flight transaction. One reason why Dirty Writes are bad is that they can violate database consistency.
Dirty Reads A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 updates a row. Transaction 2 reads the updated row before transaction 1 commits the update.
Dirty write is that a transaction updates or deletes (overwrites) the uncommitted data which other transactions insert, update or delete.
To prevent dirty reads, the database engine must hide uncommitted changes from all other concurrent transactions. Each transaction is allowed to see its own changes because otherwise the read-your-own-writes consistency guarantee is compromised.
Any RDBMS providing transactions and atomicity of transactions cannot allow dirty writes.
SQL Server must ensure that all writes can be rolled back. This goes even for a single statement because even a single statement can cause many writes and run for hours.
Imagine a row was written but needed to be rolled back. But meanwhile another write happened to that row that is already committed. Now we cannot roll back because that would violate the durability guarantee provided to the other transaction: the write would be lost. (It would possibly also violate the atomicity guarantee provided to that other transaction, if the row to be rolled back was one of several of its written rows).
The only solution is to always stabilize written but uncommitted data using X-locks.
SQL Server never allows dirty writes or lost writes.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
