5

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?
Anand Patel
  • 6,031
  • 11
  • 48
  • 67

2 Answers2

11

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')
Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Funny, one should hack to extract this info or it is written anywhere in msdn? – Gennady Vanin Геннадий Ванин Oct 18 '10 at 11:48
  • @MartinSmith I assumed that without a covering index that serializable transaction would result in a table lock but have not found this information elsewhere and am not sophisticated enough to test my assumption. I trust you are correct here ... but wanted to ask if you can share a source for your knowledge (documentation or verified personal testing). Thanks a lot! – TCC Oct 22 '14 at 20:32
  • @MartinSmith Regarding my previous comment, I'm sure the methods in this question could be used to verify the "Table lock" fallback... I'm just not sure i would interpret them right since i am not used to looking at lock descriptions (but I'm learning thanks to great answers like this one). I added a link to this question from the MSDN docs because it really is an outstanding exercise to explain the edge cases around range locking. Thanks for your efforts in the community. – TCC Oct 22 '14 at 20:45
0

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.

jl.
  • 401
  • 6
  • 13