SQL Server Performance Forum – Threads Archive

Setting the ISOLATION level !

The following isolation level(which is inside the SP and also necessary) is causing the deadlocks. Once this transaction is committed, should I set the transaction isolation level to default(Read Committed) at the end of SP? Any hints?
USE DatabaseName
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION Update TableA
SET Value1 = Value2 SELECT Value1 FROM TableA COMMIT TRANSACTION
GO
DBCC USEROPTIONS
GO Is it necessary to do this?
SET TRANSACTION ISOLATION LEVEL READ COMMITTED Thanks, Name
———
Dilli Grg (1 row(s) affected)

No…if you runt the code in query window you have to…
If you are setting ISOLATION level in the procedure, it is for that procedure only… even if you don’t set it back to read committed it should be fine… Read BOL topic "SET TRANSACTION ISOLATION LEVEL (Transact-SQL) "
SERIALIZABLE
Specifies the following: Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes. MohammedU.
Moderator
SQL-Server-Performance.com

Thanks Mohammed for your response. So you are saying that the sql server automatically sets the transaction level to default as soon as the transaction commits from the stored procedure. This is the sample SP that is causing deadlocks. Does anyone see any issues with this stored procedure?
CREATE PROCEDURE dbo.usp_PopEvents AS DECLARE @lockId uniqueidentifier SET @lockId = newid() SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION UPDATE dbo.tbl_One
SET LockId = @lockId
WHERE pk_OneID IN (
SELECT TOP 100 pk_OneID
FROM dbo.tbl_One
WHERE LockId IS NULL
ORDER BY pk_OneID ASC ) SELECT Event
FROM dbo.tbl_One
WHERE LockId = @lockId DELETE
FROM dbo.tbl_One
WHERE LockId = @lockId COMMIT
GO
Thanks, Name
———
Dilli Grg (1 row(s) affected)

If you are trying to update the same data using the same procedure or different procedure you may get the blocks and dead locks too… Because you are using "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction. Why do you want to use SERIALIZABLE? Try the following… CREATE PROCEDURE dbo.usp_PopEvents
AS
DECLARE @lockId uniqueidentifier SET @lockId = newid()
declare @tbl table (pk_OneID int)
insert into @tbl (pk_OneID)
SELECT TOP 100 pk_OneID
FROM dbo.tbl_One
WHERE LockId IS NULL
ORDER BY pk_OneID ASC BEGIN TRANSACTION
UPDATE O SET LockId = @lockId
FROM dbo.tbl_One O
JOIN @tbl t ON T.pk_OneID = O.pk_OneID DELETE FROM dbo.tbl_One WHERE LockId = @lockId
IF @@ERROR = 0 COMMIT TRAN ELSE ROLLBACK TRAN SELECT Event FROM dbo.tbl_One
WHERE LockId = @lockId
MohammedU.
Moderator
SQL-Server-Performance.com

This proc is called from 3 different applications (might be at the same time) and data integrity is very important in this case so I am assuming this (SERIALIZABLE) is a MUST for this SP. I think your approach might help in this update scenario, I will evaluate this and go from there. Thanks for your help. BTW Mohammed, where are you in CA? I was in southern cal for last 2 yrs. Thanks, Name
———
Dilli Grg (1 row(s) affected)

the reason this query deadlocks is that 2 overlapping calls will hit the same rows,
the later one not getting the rows, and becoming a deadlock victim whoever wrote this did not understand the purpose of SERIALIZABLE if you left this in the default READ COMMITTED levle
with the BEGIN TRAN
& the first UPDATE
those rows are locked, PERIOD no other operation can get to those rows,
but at SERIALIZABLE, the second call is a deadlock victim this proc should be something like: SELECT TOP 100 pk_OneID
FROM dbo.tbl_One (XLOCK,READPAST)
WHERE LockId IS NULL
ORDER BY pk_OneID ASC DELETE dbo.tbl_WHERE pk_OneID IN (
SELECT TOP 100 pk_OneID
FROM dbo.tbl_One
WHERE LockId IS NULL
ORDER BY pk_OneID ASC ) all at READ COMMITTED

Joe, thanks for the reply. I know this was existing proc which was causing deadlocks so just trying to find the way to optimize without affecting the output( or logic) for this proc. I am not expert on the locks but what does XLOCK with READPAST do in this case?

quote:Originally posted by joechang the reason this query deadlocks is that 2 overlapping calls will hit the same rows,
the later one not getting the rows, and becoming a deadlock victim whoever wrote this did not understand the purpose of SERIALIZABLE if you left this in the default READ COMMITTED levle
with the BEGIN TRAN
& the first UPDATE
those rows are locked, PERIOD no other operation can get to those rows,
but at SERIALIZABLE, the second call is a deadlock victim this proc should be something like: SELECT TOP 100 pk_OneID
FROM dbo.tbl_One (XLOCK,READPAST)
WHERE LockId IS NULL
ORDER BY pk_OneID ASC DELETE dbo.tbl_WHERE pk_OneID IN (
SELECT TOP 100 pk_OneID
FROM dbo.tbl_One
WHERE LockId IS NULL
ORDER BY pk_OneID ASC ) all at READ COMMITTED

Thanks, Name
———
Dilli Grg (1 row(s) affected)

XLOCK Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. READPAST Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement. MohammedU.
Moderator
SQL-Server-Performance.com

Thanks everyone for your suggestions. Based on Joechang and Mohammed’s inputs, I have modified the first proc to reflect to the changes. Unfortunately, we don’t have Dev and QA environment to test this proc from the application, so just wanted make sure that the this updated SP does basically the same thing as the original one in terms of results. Thanks again for your time. http://support.microsoft.com/kb/297466/en-us
http://www.eggheadcafe.com/software/aspnet/29397697/fifo-type-queue-in-sql-ta.aspx CREATE PROCEDURE dbo.usp_PopEvents
AS
DECLARE @lockId uniqueidentifier
SET @lockId = newid() DECLARE @tmpData TABLE (pk_OneID int) BEGIN TRANSACTION
INSERT INTO @tmpData (pk_OneID)
SELECT TOP 100 pk_OneID
FROM dbo.tbl_One (XLOCK, READPAST)
WHERE LockId IS NULL
ORDER BY pk_OneID ASC UPDATE O
SET LockId = @lockId
FROM dbo.tbl_One O
JOIN @tmpData t
ON T.pk_OneID = O.pk_OneID DELETE FROM dbo.tbl_One
WHERE LockId = @lockId
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN SELECT Event
FROM dbo.tbl_One
WHERE LockId = @lockId
GO
Thanks, Name
———
Dilli Grg (1 row(s) affected)

Guess not, since the delete happens within the transaction. So, the select after delete won’t return anything because the records are already deleted. Thanks, Name
———
Dilli Grg (1 row(s) affected)

That is really funny…. I think you copied my code…I messed it <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> MohammedU. Moderator SQL-Server-Performance.com

That is really funny…. [?][?][?] I didn’t want to say that your approach was wrong so you know what you’ve suggested![<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by MohammedU That is really funny…. I think you copied my code…I messed it <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> MohammedU. Moderator SQL-Server-Performance.com <hr height="1" noshade id="quote"></blockquote id="quote"> <pre id="code"> Thanks, Name ——— Dilli Grg (1 row(s) affected) </pre id="code">

What is Funny???
You didn’t check the login before putting into production….
It was my cut and past issue…. MohammedU.
Moderator
SQL-Server-Performance.com

]]>