Post reply

Deadlocking (chronological order access)

  • April 22, 2002 at 3:20 am

    #78604

    Hi all

    Here is a statement from a MS SQLServer whitepaper on deadlocking. My question is.. what in the blazes does it all mean , and if its to do with index sorting etc, Im still confused about the concept and how it works.

    "If applications accessing SQL Server are architected so that transactions access tables in the same chronological order across all user transactions, deadlocking will be avoided. It is worthwhile to clearly explain this concept of chronological table access to SQL application developers as early as possible during the application design process. It will help avoid deadlocking problems that will be more expensive to solve later on. "


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • April 22, 2002 at 4:41 am

    #427576

    quote:

    and if its to do with index sorting etc

    That answer is no. What it is referring to is when applications are built they should be built to access the data in the same order during a transaction.

    Example

    Insert Table1

    Update Table2

    Update Table3

    This give you a smaller lieklyhood of casuing a deadlock in the process. However if one process does that and then another at the same time does

    Insert Table1

    Update Table3

    Update Table2

    They can potentially lock resources the other is trying to get to since the processes order of execution is different. Thus you increase the chance of having a deadlock and spending a lot of resource time to investigate and correct the issue.

    Hope this helps.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • April 22, 2002 at 5:50 pm

    #427627

    Hi there, ok, I should have picked up on that one. I will have a another look at this at work, I can see how this may work, but will have to see about its validity in some of our real work senarios.


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

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

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