louis nguyen (lo************@hotmail.com) writes:
In the example you posted, I typically use a "set transaction" option.
My understanding is that this would prevent all shared locks. What
is your opinion (pros/cons) of this? Thanks, Louis.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl
INSERT tbl (id, ...) VALUES (@id, ...)
COMMIT TRANSACTION
This is very likely to cause deadlocks. The isolation level does not
affect the ability to get shared locks. It only affects what you can
see if you issue the same statement later in the query.
Try this:
CREATE TABLE tbl (id int NOT NULL)
go
DECLARE @id int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl
WAITFOR DELAY '00:00:10'
INSERT tbl (id) VALUES (@id)
COMMIT TRANSACTION
First create the table, then run the batch from two windows. You will
get a deadlock. Add "WITH (UPDLOCK)" after the table, and both
batches will succeed.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
