Sql Server Ignore rowlock hint

Question

This is a general question about how to lock range of values (and nothing else!) when they are not exists in table yet. The trigger for the question was that I want to do "insert if not exists", I don't want to use MERGE because I need to support SQL Server 2005.

In the first connection I:

1. begin transaction
2. select data from a table using (SERIALIZABLE, ROWLOCK) + where clause to respecify range
3. wait...

In the second connection, I insert data to the table with values that do not match the where clause in the first connection

I would expect that the second connection won't be affected by the first one, but it finishes only after I commit (or rollback) the first connection's transaction.

What am I missing?

Here is my test code:

First create this table:

CREATE TABLE test
(
    VALUE nvarchar(100)
)

Second, open new query window sql server managements studio and execute the following:

BEGIN TRANSACTION;
SELECT *
FROM  test WITH (SERIALIZABLE,ROWLOCK)
WHERE value = N'a';

Third, open another new query window and execute the following:

INSERT INTO test VALUES (N'b');

Notice that the second query doesn't ends until the transaction in the first window ends

Critter

Answer 1

You are missing an index on VALUE.

Without that SQL Server has nothing to take a key range lock on and will lock the whole table in order to lock the range.

Even when the index is added however you will still encounter blocking with the scenario in your question. The RangeS-S lock doesn't lock the specific range given in your query. Instead it locks the range between the keys either side of the selected range.

When there are no such keys either side the range lock extends to infinity. You would need to add a value between a and b (for example aa) to prevent this happening in your test and the insert of b being blocked.

See Bonus Appendix: Range Locks in this article for more about this.

Adorl

(: Thank you Martin, But it didn't help. I've tried adding index (tried both CLUSTERED and NONCLUSTERED): "CREATE clustered INDEX ix_test_value ON test (value)" and it is still lock the selection of value='a' still locks the insertion of value 'b'. Maybe I'm missing another thing? 10x – Critter

@Critter - The range lock locks the range between the keys either side of the selected range so when you do this against an empty table the range locked is still the whole index. Try adding a couple of rows such as 1 and AA to your test table and trying again. – Adorl

Thank you, you are right. After adding few rows it seems to work. – Critter