Help with Locking Tables for Stored Proc

  • Instead of placing the database into single user mode, while we load new data, I am trying to figure out the SQL command: SET TRANSACTION ISOLATION LEVEL, but there is not a lot of information on the MS page. (only one example)

    What I would like to do is put the locks in place (so we can ovoid the deadlocks we are getting with users during the load process, since it always kills the stored proc).

    From what I am reading.. the SQL would look something like..

    SET TRANSACTION ISOLATION LEVEL TABLOCK HOLDLOCK

    GO

    BEGIN TRANSACTION

    (CURRENT SQL CODE)

    END TRANSACTION

    Is that correct?? Wouldn't I also need to change the transaction isolation back to normal?

  • SET TRANSACTION ISOLATION LEVEL is used to set one of the 5 isolation levels:

    READ UNCOMMITTED

    READ COMMITTED

    REPEATABLE READ

    SERIALIZABLE

    SNAPSHOT

    The syntax is described at http://msdn.microsoft.com/en-us/library/ms173763.aspx

    What you listed there are locking hints which go into a query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for clearing that up Gail... That leave the following questions...

    1) the example shows a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    . What does it mean by session, when the stored proc ends (in this case) or when all the steps in the Job complete?

    2) If you use a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    (http://msdn.microsoft.com/en-us/library/aa213026(v=sql.80).aspx)

    then use a hint to create table locks.. those locks would remain until the commit. Correct?

    3) Can you use hints when you create a table... so users can not access it until the data load and indexing has taken place? Or must it be in the Insert Into statement only. (Given the page it looks like you can not. So my guess is that you would have to write a bogus line, and delete, to get table lock placed)

  • dwilliscp (6/14/2013)


    1) the example shows a set... begin.. comit. So once the commit is done the set command ends?

    No, it's a session-level setting. It persists til you set otherwise or close the connection

    2) If you use a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    (http://msdn.microsoft.com/en-us/library/aa213026(v=sql.80).aspx)

    then use a hit to create table locks.. those locks would remain until the commit. Correct?

    Hit?

    3) Can you use hits when you create a table... so users can not access it until the data load and indexing has taken place? Or must it be in the Insert Into statement only. (Given the page it looks like you can not. So my guess is that you would have to write a bogus line, and delete, to get table lock placed)

    Hits?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail sorry found the section at the bottom, and changed my last post... but you had already read what I posted before. Opps.. hit should have been hint. I cleaned up my earlier post.

  • If you are running under serialisable isolation, all locks are held until the end of the transaction (and there will be a lot of locks). Hints go on queries, not on tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail, I was afraid of that.

    One more question... (SET TRANSACTION ISOLATION LEVEL SERIALIZABLE)

    In my case what does it mean by session, when the stored proc ends or when all the steps in the Job complete?

  • When the connection that ran the SET closes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply