Microsoft SQL Server forum - Serializable transactions and insert.

Author

Serializable transactions and insert.

Magnus Byne

2005-06-30, 8:23 pm

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.

Gang He [MSFT]

2005-06-30, 8:23 pm

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.
>

Magnus Byne

2005-07-04, 1:23 pm

Hi,

Thanks for your help. The situation I have is where a number of threads
are adding things to a table. Inside a transaction they first do a
select to make sure someone has not already inserted the data - so
typically the first select returns an empty rowset. Selecting an empty
rowset (e.g. key does not exist) always seems to create a Key Range
lock regardless of the indexes on the table (or a table lock if there
are no indexes). Is there anyway to around this?

Many thanks,
Magnus.

Erland Sommarskog

2005-07-04, 8:23 pm

Magnus Byne (MagnusByne@gmail.com) writes:
> Thanks for your help. The situation I have is where a number of threads
> are adding things to a table. Inside a transaction they first do a
> select to make sure someone has not already inserted the data - so
> typically the first select returns an empty rowset. Selecting an empty
> rowset (e.g. key does not exist) always seems to create a Key Range
> lock regardless of the indexes on the table (or a table lock if there
> are no indexes). Is there anyway to around this?

I played around a little, and for once it seems that Gang was wrong.

CREATE TABLE testie (a int NOT NULL PRIMARY KEY,
somedata varchar(500) NOT NULL)
go
INSERT testie (a, somedata)
VALUES (1, replicate('x', 243))
INSERT testie (a, somedata)
VALUES (3, replicate('x', 243))
INSERT testie (a, somedata)
VALUES (5, replicate('x', 243))
INSERT testie (a, somedata)
VALUES (10, replicate('x', 243))
INSERT testie (a, somedata)
VALUES (19, replicate('x', 243))

Then I ran in one query window:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
IF ENOT XISTS (SELECT * FROM testie WHERE a = 9)
INSERT testie (a, somedata) VALUES (9, replicate('l', 23))

Note: no COMMIT or ROLLBACK!

Then in a second window, I did:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM testie WHERE a = 8)
INSERT testie (a, somedata) VALUES (8, replicate('l', 23))

This blocked. However, if I changed 8 to 4, the second query did not
block. So it appears that even if the key is unique, there is a
range lock. And when you think of it, there is not much to do. If
there is no row, what should SQL Server lock on? Thin air?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links