Read Committed vs Read Uncommited if both transaction do not rollback

Question

I am trying to understand read committed and read uncommitted isolation levels. I know that theoreticay read uncommitted allows dirty reads and read committed doesn't, but I still can't really understand.

Considering Figure above, if none of the transactions were aborted then the final result is the same for both read committed and read uncommitted?

Answer 1

Your example has nothing to do with Isolation Levels. This is because they affect readers behaviour, not writers, and in your example there are only writers.

You should refer this BOL article: Understanding Isolation Levels that says

Choosing a transaction isolation level doesn't affect the locks that are acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

In your example none of transactions read, they both modify. The first transaction will acquire X on interested RID or key (depends on table structure, if it is heap or clustered table) -- I'll call it res_1 in future -- for inserting and will hold it for all the duration of the transaction (it will also have IX on corresponding page and object), and the same is for the first statement of the second transaction: it will acquire X on res_2 when inserting.

On the DELETE attempt the second transaction will be blocked as it cannot obtain X (or U in case there is no index on where condition), this is because there is already X on the same resource (res_1) hold by the first transaction. And there will be no second INSERT in the second transaction because previous DELETE is blocked.

Finally, when the first transaction attempts its DELETE, it needs X or U (depending on index existence) on res_2, but it's already blocked with X by tran2, so it's also blocked and there is no exit from this situation, every session waits for another session to finish and no session can complete, at this point a deadlock occur and server will resolve it by rolling back one of the transactions.

Answer 2

READ UNCOMMITTED allows you to read the dirty data that has not been committed by other transactions. SQL Server engine ignores any lock under the table being read and reads the data directly from memory.

READ COMMITTED will read the data that has already been COMMITTED but will wait if the data is being affected by other transaction.

So, in the example provided the system is not only reading but also trying to DELETE a row that has still not been COMMITTED, so, both will wait until the other transaction finishes so, the example is a typical example for DEADLOCK.

To ilustrate the differences between COMMITTED vs UNCOMMITTED I will show you a simple and clear example that we will run twice, in the two modes.

-- Query Window 1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- Prepare for first Run
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Prepare for second Run

BEGIN TRANSACTION                                   -- Step 1
INSERT INTO Audit (ProductID, PrevValue, NewValue, ChangedBy)   
    VALUES (1, 'AAA', 'aaa', SYSTEM_USER);          -- Step 3
COMMIT TRANSACTION                                  -- Step 5

-- Query Window 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- Prepare for first Run
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Prepare for second Run

BEGIN TRANSACTION                           -- Step 2
SELECT * FROM Audit WHERE PrevValue = 'AAA' -- Step 4
COMMIT TRANSACTION                          -- Step 6

We have to run first the line for UNCOMMITTED LEVEL in both queries and then, go to first one, run Step 1, go to the second, step 2 and so on. In the UNCOMMITTED when we run Step 4 we will see the results inmediately as we are doing a dirty read (from memory). For second run, we will remove first the line test with:

DELETE FROM Audit WHERE PrevValue LIKE 'AAA';   

Then, will run the line for COMMITTED LEVEL in both query windows and will run the same sequence. We will observe that now, when we run Step 4 the system remains with no response. Just in the moment that we run Step 5 to commit the insert the window will show the results.

I hope that the question now is clearer.