Search code examples
sql-servermultithreadingdeadlockisolation

SQL Server isolation level, deadlocks across Load Ballancing

I know what is DeadLock and how database generated. My goal is to clarify this issue thoroughly to record.

1- How can i handle deaed locks in sql server? Is there any library or tool to handle theme?

2- In a real time scenario how can i force while my database takes much requests in a short time. For example: i have 8 dedicated server and they are taking 20 000-40 000 per request in one second.

a-> Which is the best Load Balancing tool especially if i want to escape the dead-locks

b-> Which layer is perfect for load balancing is it should be on physical-layer or software-layer or both. Of course is both but how is in your experiences? For example how is netscaler in physically? I want to know their advantages and disadvantages?

Solution

  • This is a long formatted comment. In some of my ColdFusion applications, I have taken the following approach to handling deadlocks.

    • put the cfquery tag into a function that returns a ColdFusion query object.
    • Attempt to call that function using try/catch a maximum of 3 times
    • Throw an error if still unsucessful

    This approach is not limited to ColdFusion. It can be applied to .net, php, java, and even tsql.

    Also, it will not eliminate the deadlock problem. It will however, reduce the amount of times it happens.