Why insert a TSQL statement block when a transaction isolation level for another transaction is serialized using a non-conflict filter?

Question

Serializable transaction isolation levels eliminate the problem of reading phantom by blocking any inserts into the table in a transaction that conflict with any selection statements in other transactions. I try to figure this out with an example, but it blocks the insertion even if the filter in the select statement does not conflict. I would appreciate any explanation of why he behaves this way.

Script Table

CREATE TABLE [dbo].[dummy](
    [firstname] [char](20) NULL,
    [lastname] [char](20) NULL
) ON [PRIMARY]

GO

Session - 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select * from dummy where firstname = 'abc'

Session - 2

insert into dummy values('lmn', 'lmn') -- Why this blocks?

Answer 1

, firstname. -, .

BOL

, : :

• SERIALIZABLE.

• . , WHERE SELECT : ColumnX BETWEEN N'AAA' AND N'CZZ'. ColumnX .

RangeS-S, . SQL Server .

, , ...

CREATE CLUSTERED INDEX [IX_FirstName] ON [dbo].[dummy] ([firstname] ASC)

... , !

, , , .

, ,

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT *
FROM   dummy
WHERE  firstname = 'abc'

SELECT resource_type,
       resource_description, 
       request_mode
FROM   sys.dm_tran_locks
WHERE  request_session_id = @@SPID

COMMIT 

+---------------+----------------------+--------------+
| resource_type | resource_description | request_mode |
+---------------+----------------------+--------------+
| DATABASE      |                      | S            |
| OBJECT        |                      | IS           |
| PAGE          | 1:198                | IS           |
| KEY           | (ffffffffffff)       | RangeS-S     |
+---------------+----------------------+--------------+

SQL Server , .

, , , .

"" ( ffffffffffff , "" ). "" .

, ' , . , "".

, SELECT - . abc lmn, .

insert into dummy values('def', 'def')

Answer 2

http://msdn.microsoft.com/en-us/library/ms173763.aspx

SERIALIZABLE :

, , .

, , .

, , , SELECT, .