Understanding Lock Behavior in SQL Server

Question

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

In the table taken and populated with data from the Isolation (database systems) wiki [2],
in SQL Server 2008 R2 SSMS I performed:

1) first in the first tab (window) 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 the 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, the results have been fixed.

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

• immediately (reading an unfixed inserted row)

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

• waiting for transaction 1) to complete ( ONLY IF SELECT without NOLOCK) and

  • READ COMMITTED (REPEATABLE READ, SERIALIZABLE)

, ( ?) [1]
(, SELECT NOCHECK 1)) ..

[1]?

---

Update2:
[3] ( ).

:
[1]
SQL Server
SQL Server
[2]
" ( )"
Plz ) . , ! http://en.wikipedia.org/wiki/Isolation_(database_systems)
[3]
NOLOCK SELECT SQL Server 2005?
NOLOCK SELECT SQL Server 2005?

Answer 1

MSDN SQL 2008. , SQL Server 2008 ?

( , SQL Server 2008)

, SERIALIZABLE, NOLOCK SELECT, , , .

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

, HumanResources.Employee, (Sch-S) .. .

SQL Server 2008 LOCK_ESCALATION A LTER TABLE HoBT . , . . ALTER TABLE (Transact-SQL).

Answer 2

.
SELECT ... WITH (NOLOCK) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT ....

, , .