|
|
Posted by Erland Sommarskog on 10/01/58 11:20
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/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|