Why insert TSQL statement block when transaction isolation level for another transaction is serializable with non-conflicting filter?

Question

Serializable transaction isolation levels avoids the problem of phantom reads by blocking any inserts to a table in a transaction which are conflicting with any select statements in other transactions. I am trying to understand it with an example, but it blocks insert even if when the filter in the select statement is not conflicting. I would appreciate any explanation on why it behaves in that way.

Table Script

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

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

SERIALIZABLE Specifies the following:

Statements cannot read data that has been modified but not yet committed by other transactions.

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

As I understand this, your insert will be blocked since the transaction under which your SELECT is running has not completed.

Answer 2

Disclaimer: I should say at the outset that this is not an area that I claim particular expertise in so parts of this answer may be downright wrong though I think the gist is correct!

I think the main issue in your test scenario is that the table is a heap. In order to guarantee serializability SQL Server needs to get a RangeS-S lock on the range indicated in the WHERE clause. If that is not possible it needs to lock the whole table.

If you try adding a clustered index on the table on the first name column

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

you should find that the insert succeeds with no problem. I then tried dropping this index, adding a new id column and making it the clustered index and recreating the firstname index as nonclustered

DROP INDEX [IX_FirstName] ON [dbo].[dummy] 
ALTER TABLE dbo.[dummy] ADD ID int NOT NULL IDENTITY (1, 1)
ALTER TABLE dbo.[dummy] ADD CONSTRAINT  PK_dummy PRIMARY KEY CLUSTERED (ID)
CREATE NONCLUSTERED INDEX [IX_FirstName] ON [dbo].[dummy] ([firstname] ASC)

Upon re-running the test I found that the blocking had reappeared. This was expected as I had read the following in the linked article

Before key-range locking can occur, the following conditions must be satisfied:

• The transaction-isolation level must be set to SERIALIZABLE.

• The query processor must use an index to implement the range filter predicate. For example, the WHERE clause in a SELECT statement could establish a range condition with this predicate: ColumnX BETWEEN N'AAA' AND N'CZZ'. A key-range lock can only be acquired if ColumnX is covered by an index key.

In order to satisfy the second condition I used the Index Hint below

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
select * from dummy 
WITH (INDEX(IX_FirstName))
where firstname = 'abc'

And then found that the insert succeeded without issue.

Just to add that whether the index is unique makes a difference as well.