SQL Server Performance

Setting the ISOLATION level !

Discussion in 'SQL Server 2005 General Developer Questions' started by DilliGrg, Mar 2, 2007.

  1. DilliGrg Member

    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)

  2. MohammedU New Member

    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
  3. DilliGrg Member

    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)

  4. MohammedU New Member

    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
  5. DilliGrg Member

    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)

  6. joechang New Member

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

    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)

  8. MohammedU New Member

    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
  9. DilliGrg Member

    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)

  10. DilliGrg Member

    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)

  11. MohammedU New Member

    That is really funny....<br />I think you copied my code...I messed it <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com
  12. DilliGrg Member

    <i>That is really funny....</i><br />[?][?][?]<br /><br />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=':)' />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by MohammedU</i><br /><br />That is really funny....<br />I think you copied my code...I messed it <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Thanks,<br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
  13. MohammedU New Member

    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

Share This Page