Understanding locking behavior in SQL Server

Question

I tried to reproduce the situation of question [1].

On table, taken and filled with data from wiki's "Isolation (database systems)" [2],
in SQL Server 2008 R2 SSMS, I executed:

1) first in first tab (window) of SSMS

-- transaction isolation level in first window does not influence results (?)
-- initially I thought that second transaction in 2) runs at the level set in first window

begin transaction 
INSERT INTO users VALUES ( 3, 'Bob', 27 )
waitfor delay '00:00:22'
rollback

2) immediately after, in second window

-- this is what I commented/uncommented

-- set transaction isolation level SERIALIZABLE
-- set transaction isolation level READ REPEATABLE
-- set transaction isolation level READ COMMITTED
-- set transaction isolation level READ UNCOMMITTED

SELECT * FROM users --WITH(NOLOCK)

---

Update:
Sorry, results were corrected.

My results, depending on isolation level set in 2), are that SELECT returns:

• immediately (reading uncommitted inserted row)

  • for all cases of SELECT with NOLOCK
  • for READ UNCOMMITTED (SELECT either with or without NOLOCK)

• is waiting the completion of transaction 1) (ONLY IF SELECT is without NOLOCK) and

  • in READ COMMITTED and higher (REPEATABLE READ, SERIALIZABLE) transaction isolation level

These results contradict to situation described in question (and explained in answers?) [1]
(for example, that SELECT with NOCHECK is waiting completion of 1)), etc.

How can my results and [1] be explained?

---

Update2:
This question is really subquestion of my questions [3] (or the result of them not being answered).

Cited:
[1]
Explain locking behavior in SQL Server
Explain locking behavior in SQL Server
[2]
"Isolation (database systems)"
Plz add trailing ) to link. I cannot manage to preserve it here in the link! http://en.wikipedia.org/wiki/Isolation_(database_systems)
[3]
Is NOLOCK the default for SELECT statements in SQL Server 2005?
Is NOLOCK the default for SELECT statements in SQL Server 2005?

Answer 1

There is a useful MSDN link her talk about locking hints in SQL 2008. Maybe in your example its a case of SQL Server 2008 disfavoring your tables locks?

(The following snippet from the link below talks about locks potentially being ingored by SQL Server 2008)

As shown in the following example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

CopyUSE AdventureWorks2008R2;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT Title
    FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by 
-- the transaction.
SELECT  
        resource_type, 
        resource_subtype, 
        request_mode
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@spid;

-- End the transaction.
ROLLBACK;
GO

The only lock taken that references HumanResources.Employee is a schema stability (Sch-S) lock. In this case, serializability is no longer guaranteed.

In SQL Server 2008, the LOCK_ESCALATION option of A LTER TABLE can disfavor table locks, and enable HoBT locks on partitioned tables. This option is not a locking hint, but can but used to reduce lock escalation. For more information, see ALTER TABLE (Transact-SQL).

Answer 2

The hint in the second query overrides transaction isolation level.
SELECT ... WITH (NOLOCK) is basically identical to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT ....

With any other isolation level the locks are honored, so the second transaction waits until the locks are released by the first one.