SQL Server - Is there any such thing called 'dirty write'?

sql-server transactions locking isolation-level
user441660 picture user441660 · Dec 4, 2014 · Viewed 7.3k times · Source

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?

Answer

usr picture usr · Dec 4, 2014

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.

Related questions

Correct use of transactions in SQL Server

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly. What's the problem with the following script? BEGIN TRANSACTION […

Read More
SQL Server - transactions roll back on error?

We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; It …

Read More
SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file

I'm having a similar issue to The current transaction cannot be committed and cannot support operations that write to the log file, but I have a follow-up question. The answer there references Using TRY...CATCH in Transact-SQL, which I'll come …

Read More