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.