Understand SQL Read commit and read uncommitted

Question

I am using SQL Server Express 2008 with AdventureWorksLT2008 DB to understand the different values ​​of Read Read and Read uncommitted.

According to Wikipedia: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

READ THE COMMITTEE

Recording data obtained using a query does not interfere with some other transactions.

Suppose that there is a table called SalesLT.Address and an AddressLine2 column for which all rows have an empty value

Then I run this query:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION update SalesLT.Address set AddressLine2 = 'new value' BEGIN TRANSACTION select AddressLine2 from SalesLT.Address --Break Here /* COMMIT TRANSACTION COMMIT TRANSACTION */ 

So, you can see that the first transaction has not yet been completed, and the second begins to request data.

As a result:

So, why can the second transaction be extracted from phantom data, even the first transaction has not yet been completed?

Answer 1

When data is read inside a transaction, any changes made by this transaction are visible - only within the scope of the transaction (although READ UNCOMMITTED changes this). So, despite the fact that you started the second, nested transaction, you are still in the area of ​​the first transaction and thus you can read the changed data and get the “changed values”.

Another transaction, for example, on a separate SPID, is blocked if it uses READ COMMITTED and tries to read this data.