Sql server ignore rowlock hint

Question

This is a general question about how to block a range of values ​​(and nothing more!) If they do not already exist in the table. The trigger for the question was that I want to do "insert if not exists", I do not want to use MERGEit because I need to support SQL Server 2005.

In the first compound I:

• begin transaction
• select data from table using (SERIALIZABLE, ROWLOCK)+ where to define range
• Wait...

In the second join, I insert data into the table with values ​​that do not match the where clause in the first join

I would expect the first connection to not affect the second connection, but it ends only after I complete (or rollback) the first connection transaction.

What am I missing?

Here is my test code:

First create this table:

CREATE TABLE test
(
    VALUE nvarchar(100)
)

Secondly, open a new sql server studio studio query window and do the following:

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

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

INSERT INTO test VALUES (N'b');

Please note that the second request does not end until the transaction is completed in the first window

Answer 1

You do not have an index on VALUE.

Without this, SQL Server will not be able to accept a key range lock and locks the entire table to lock the range.

, . RangeS-S , . .

, . a b (, aa), , b .

. : .