sql server - 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

The first issue in your test scenario is that the table has no useful index on firstname. The second is that the table is empty.

From Key-Range Locking in BOL

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.

There is no suitable index to take RangeS-S locks on so to guarantee serializable semantics SQL Server needs to lock the whole table.

If you try adding a clustered index on the table on the first name column as below and repeat the experiment ...

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

... you will find that you are still blocked!

Despite the fact that a suitable index now exists and the execution plan shows that it is seeked into to satisfy the query.

You can see why by running the following

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 

Returns

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

SQL Server does not just take out a range lock on exactly the range you specify in your query.

For an equality predicate on a unique index if there is a matching key it will just take a regular lock rather than any type of range lock at all.

For a non unique seek predicate it takes out locks on all matching keys within the range plus the "next" one at the end of the range (or on ffffffffffff to represent infinity if no "next" key exists). Even deleted "ghost" records can be used in this range key locking.

As described here for an equality predicate on either a unique or non unique index

If the key does not exist, then the ‘range’ lock is taken on the ‘next’ key both for unique and non-unique index. If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value.

So with an empty table the SELECT still ends up locking the entire index. You would need to also have previously inserted a row between abc and lmn and then your insert would succeed.

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