Posted by Gang He [MSFT] on 06/30/05 23:08

What you saw is probably the behavior by design. Looks like the index is
built on column id but not unique. The range lock taken under serializable
isolation level is acquired on the next key value outside the range.
Suppose the rows in the table look like (1, 'one'), (3, 'three'), (4,
'four'). For this select query the range lock is obtained on row (3,
'three') even if only the row (1, 'one') qualifies for the query. This is
to prevent new inserts (qualifying the predicate) after the last row
returned for the query. Now, if the index is unique on the column id, then
the select query will not obtain a range lock and you won't have this
problem.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Magnus Byne" <MagnusByne@gmail.com> wrote in message
news:1120159130.147995.169930@f14g2000cwb.googlegroups.com...
> Hi,
>
> I have a problem using serializable transactions. In one transaction I
> select a single specific row from a table using a where clause (this
> causes it to acquire and hold a range lock). In another transaction I
> attempt to insert a new row into the table (which does not match the
> first transactions where clause), but it is blocked by the first
> transaction. The reading I have done on SQL Server suggests that I
> should be able to insert rows, as long as the new rows do not match the
> where clause in the other transactions select.
>
> Here is what I do:
>
> Transaction 1
> SET TRANSACTION ISOLATION LEVEL Serializable
> BEGIN TRANSACTION
> select * from test_table where id=1;
>
> and then on another session I run
> Transaction 2
>
> SET TRANSACTION ISOLATION LEVEL Serializable
> BEGIN TRANSACTION
> insert into test_table values (2, 'two');
>
> Transaction 2 cannot complete until transaction 1 has finished. I have
> tried using WITH (ROWLOCK) hints but to no avail. Am I missing
> something important? Is this true of other DBs?
>
> (I am able to update rows that are not in transaction 1's where clause)
>
> I am using SQL Server version 8.00.760(SP3).
>
> Thanks for your help,
>
> Magnus.
>

[Back to original message]