470,624 Members | 2,148 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,624 developers. It's quick & easy.

Serializable transactions and insert.

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.

Jul 23 '05 #1
3 5612
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" <Ma********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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.

Jul 23 '05 #2
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.

Jul 23 '05 #3
Magnus Byne (Ma********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Fabrice FRASSAINT | last post: by
2 posts views Thread by Adnan | last post: by
1 post views Thread by John Wells | last post: by
reply views Thread by Ben Dewey | last post: by
12 posts views Thread by Rami | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.