Post reply

Query optimization avoiding deadlocks

  • March 5, 2008 at 4:09 am

    #128194

    Hi Folks,

    Can anyone answer me the following questions with regard to querys. there is a deadlock being thrown in one of our applications and I'd like to find out the following:

    When inserting records into a table by selecting from one table and carrying out some processing on the values - does the engine grab a lock on the inserting table from the point it begins the transaction or does it do the processing first and then request the lock right before it inserts the data?

    Would it help to use a table variable to insert all the data into and carrying out the processing or is the lock just aquired just before the insert?

    Do output variables impact on the locks? i.e. is this an uneccessary waste of resources, holding the lock until it writes back out to a table variable?

    Any answers to these questions is greatly appreciated.

    Thanks folks,

    Martin

  • March 5, 2008 at 5:54 am

    #785425

    use profiler in a test environment and look at the lock events. you will see when locks on objects are aquired and released. that way you will probably answer all your own questions.

  • March 5, 2008 at 6:21 am

    #785430

    Refer the following link to understand locks in detail:

    http://support.microsoft.com/kb/822101/en-us

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • March 5, 2008 at 7:13 am

    #785470

    THanks for the post. I'll read up on that now.

    cheers

Viewing 4 posts - 1 through 3 (of 3 total)

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