Post reply

Transactions and Hints

  • February 5, 2008 at 12:01 pm

    #119810

    Hi All,

    I have an read - only sql statment that I am developing that looks something like this:

    Begin Tran

    create #temp_table ...

    insert into #temp_table ... (

    Select ...

    From

    Table_A (NOLOCK),

    Table_B (NOLOCK)

    Where ...

    )

    select * from #temp_table

    delete #temp_table

    commit tran

    Now I understand that the 'Tran' statment defines the entier statment as a single step - thus when many users run the statment simutainisly, I won't get a clash saying that #temp_table already exists. However I do not want a lock on the tables I am reading from (ie Table_A and Table_B) for the duration of the execution of the statment. Do the hints (nolock) take precedence on the tran statment (ie, no lock will be put on the tables) or does the tran statment take precedence (ie they will be locked regardless)?

    Another question that comes to mind is - if two users run the sql, will I get a clash of type '#temp_table already exists' even if I don't use the tran statment ?

    Thanks,

    Yinnon

  • February 5, 2008 at 11:47 pm

    #775441

    Transactions by themselves don't cause locks or force locking modes. They just mean that a number of statements are treated like a single statement.

    Temp table names don't clash with other connections regardless of the existance of a transaction.

    Shared locks (from select) are released as soon as the read is complete on the default isolation level. Even inside a transaction. Only exclusive llocks are held until the transaction commits

    You can specify no lock if you wish no locks.

    May I suggest a couple changes to your statement? (in red)

    From Table_A WITH (NOLOCK) INNER JOIN Table_B WITH (NOLOCK) ON ...

    WHERE ...

    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

  • February 6, 2008 at 2:00 am

    #775470

    Thanks for a great and thorough answer Gila !!

    I will implement your suggestion (I actually already have joins on the tables).

    What does the WITH statement do?

  • February 6, 2008 at 2:07 am

    #775472

    Placing hints without using WITH is deprecated syntax. SQL 2000 does allow hints to be placed without using WITH. SQL 2005 does not

    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

  • February 6, 2008 at 2:16 am

    #775475

    thanks again Gail

Viewing 5 posts - 1 through 5 (of 5 total)

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