10

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?

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
user441660
  • 545
  • 7
  • 17

3 Answers3

9

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.

Stephen
  • 8,508
  • 12
  • 56
  • 96
usr
  • 168,620
  • 35
  • 240
  • 369
  • SQL Server might allow lost writes, right? Unless we use a serializable transaction isolation level, there can be no guarantee that one of two concurrent txns will not overwrite the other's data. In which case one of those could be a lost write. – CppNoob Oct 03 '18 at 04:43
  • @CppNoob there are no lost writes in SQL Server ever because written rows are always X-locked till the end of the transaction. Lost writes would allow for internal structures to become inconsistent (such as indexes and FKs) so the product makes 100% sure this does not happen. – usr Oct 03 '18 at 09:05
  • Which transaction isolation level are you speaking of? Say you have a table with a record, whose column `name` has value `A`. Now two transactions (at snapshot isolation / repeatable read isolation level) each read this record, set the `name` column to `A,X` and `A,Y`. At the end of the two transactions, the value is one of `A,X` and `A,Y`, but not `A,X,Y`. Would you consider that as a lost update? – CppNoob Oct 04 '18 at 04:46
  • 1
    @CppNoob one of those transactions will block until the earlier one commits (or abort due to an SI conflict). After blocking is over it can then of course overwrite the older value. Of course you always have the ability to overwrite anything you want by simply issuing another update. But that is not considered a lost write. I'm not sure how a lost write is defined but it's not simply the ability to overwrite anything you want. – usr Oct 04 '18 at 08:59
  • I was under the impression that txns block only in case of serializable isolation level. – CppNoob Oct 04 '18 at 16:04
  • @CppNoob no, any write X-locks and will block against any other write to the same row. No exceptions to my knowledge and I have investigated this issue before. Logically it must be this way to make the product work. – usr Oct 04 '18 at 16:12
  • This behavior is what is called pessimistic locking where transactions could block. There are cases where transactions can proceed with optimistic locking. Concurrent transactions write separate copies of the same data (multi-version concurrency control) and latter transactions actually check if any of the data they've read / written to has been committed by an earlier transaction. If so, they abort. Tends to perform better. – CppNoob Oct 04 '18 at 16:15
  • 1
    @CppNoob that is true, I had not thought about that. Hekaton does that (as you surely know). But Hekaton also contains a little bit of blocking. It's not per row but transactions must wait for earlier transactions to find out if the earlier one aborts or commits. Hekaton is generally seen as lock free but it's not quite. – usr Oct 04 '18 at 16:40
3

No, you can't unless you update in the same transaction. Setting the Isolation Level to Read Uncommitted will only work to read the data from the table even if it has not been committed but you can't update it.

The Read Uncommitted Isolation Level and the nolock table hint will be ignored for update or delete statements and it will wait until the transaction is committed.

Adolfo Diaz
  • 66
  • 3
0

Dirty write didn't occur in SQL Server according to my experiment with READ UNCOMMITTED which is the most loose isolation level. *Basically, dirty write is not allowed with all isolation levels in many databases.

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

I experimented dirty write with MSSQL(SQL Server) and 2 command prompts.

First, I set READ UNCOMMITTED isolation level:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Then, 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 MSSQL queries. *I used 2 command prompts:

Flow Transaction 1 (T1) Transaction 2 (T2) Explanation
Step 1 BEGIN TRAN;
GO;		 T1 starts.
Step 2 BEGIN TRAN;
GO;		 T2 starts.
Step 3 UPDATE person SET name = 'Tom' WHERE id = 2;
GO;		 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;
GO;		 T2 cannot update Tom to Lisa before T1 commits because this row is locked by T1 so to update this row, T2 needs to wait for T1 to unlock this row by commit.

*Dirty write is not allowed.
Step 5 COMMIT;
GO;		 Waiting... T1 commits.
Step 6 UPDATE person SET name = 'Lisa' WHERE id = 2;
GO;		 Now, T2 can update Tom to Lisa because T1 has already committed(unlocked this row).
Step 7 COMMIT;
GO;		 T2 commits.
Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129