SQL Server - confusion about serializable isolation level

Question

I have read the article (https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/) and I have a question according to:

"SERIALIZABLE: A query in the current transaction cannot read data modified by another transaction that has not yet committed. No other transaction can modify data being read by the current transaction until it completes, and no other transaction can insert new rows that would match the search condition in the current transaction until it completes. As a result, the Serializable isolation level prevents dirty reads, nonrepeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels."

I am confused about inserting new rows which don't fulfill search condition from 1 session/query. Example below:

Let's suppose I have table

EmpID   FirstName
1       john
2       new employee
3       A new employee

And queries in separate tabs:

--session 1----------------------------------
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

    SELECT FirstName FROM EmployeeInfo
    WHERE FirstName like 'new empl%'

    WAITFOR DELAY '00:00:10'  

    SELECT FirstName FROM EmployeeInfo
    WHERE FirstName like 'new empl%'

ROLLBACK TRANSACTION;

---------session 2---------------------------
begin transaction;

    UPDATE EmployeeInfo
    SET FirstName = 'frank'
    WHERE EmpID = 1;

commit transaction;

-----session 3----
insert into EmployeeInfo values('A new employe 2')

I execute queries one after the other: session 1, session 2, session 3. I expected that session 1 won't stop session 2 and session 3 from being executed because updates and inserts from this session don't fulfill search condition which is used in the first query. However, in results, i can see that session 1 has to be finished (rollback) before session 2 and session 3 is executed.

However, while I use other search condition in session 1 like below:

--session 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT EmpID, FirstName FROM EmployeeInfo
WHERE EmpID = 2

WAITFOR DELAY '00:00:10'  

SELECT FirstName FROM EmployeeInfo

ROLLBACK TRANSACTION;

then, session 2 and session 3 are completed independently of session 1. Why is that? Why like condition block inserting while "=" don't?

Edited: 1. There is only a primary key on EmpID .

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2017-05-20T12:16:55

This is because "no other transaction can insert new rows that would match the search condition in the current transaction until it completes". And SQL Server enforces this by taking range locks that prevent a conflicting insert.

If you have an index on EmployeeInfo.FirstName, SQL may be able to take a narrow lock to enforce this. But without an index, SQL takes a lock that prevents any inserts. Also if the SELECT query predicate was not supportable with a index, it would block all inserts.

You can examine the current state of the locks with:

select @@spid this_session, *
from sys.dm_tran_locks

. And note that this behavior, makes SERIALIZABLE a not-very-useful isolation level. And you should really only ever use READ COMMITTED and SNAPSHOT, possibly adding locking hints for specific transactions.

SQL Server - confusion about serializable isolation level

1 Answers