2

I have a multi-thread application that can opened more than one database connection to perform database operation. These connections are using the same credential.

Here is a problem, the application has a thread A opened an transaction, and start deleting items from a table, but the same thread A trigger another thread which open a new connection to read same table and to look for more thing to delete. As you can imagine, the deleting table is locked, and you can't do select on it until its transaction is completed. As a result, the application is blocking itself.

This application logic need to be fix. However, here are my questions:

  1. Is there a way to have SQLServer detect this application deadlock and kill one of the open selection, similar to a deadlock victim exception, or some kind of timeout setting?

  2. Is is possible to write a SQL Agent, that detect this situation and kill one of the session?

  3. Why would SQL Server throw a deadlock victim exception if it is caused by different application, and not for the same application?

Thanks

Improve this question

1 Answer 1

Reset to default
3

Deadlocks and blocking locks are two different concepts that you need to understand.

A deadlock is a situation where process/action 1 is waiting for process/action 2 to finish and at the same time process/action 2 is waiting for process/action 1 to finish. In other words. They would wait forever since they are waiting on each other.

In your scenario, something else is happening:

Process 1 is doing an action and has taken a lock on a resource to complete that action, Process 2 now wants to start a action that requires a lock on the same resource. Process 2 now has to wait for process 1 to complete and the lock is released. The key here is that at any given moment, none of the processes are waiting for each other (at the same time). One process is just waiting for the other process to finish an action on the same resource. They are not waiting for each other.

I hope that's clear.

On to how we fix your issue:

Can you post the Table definition, the indexes on the table and the delete select statement. We could have a look to see if there are ways to make the likelyhood of blocking locks less.

Improve this answer
4
  • Thanks for your comment. Just want to point out that this is still a deadlock situation because the application (1 process only) has opened up 2 database connections. The application basically started an transaction, locked the table, and it tries to read the table from another connection. To the database, it is a block, because if the delete sql session is committed, the select sql can continue. To the application however, it is a deadlock, it has multiple threads with its own database connections trying to access the same resource, while one thread was waiting for the other thread.
    – dsum
    Commented Sep 3, 2012 at 17:12
  • 3
    @Dsum. Key is what you say: "To the database, it is a block, because if the delete sql session is committed, the select sql can continue." As long as committing or roling back a transaction allows the other process to continue. SQL will not consider it a deadlock.. Because from a SQL perpective, it isn't. That doesn't make it less frustrating for you. Sure it's possible to create a job that runs every minute to find blocking processes and then kills them. But.....That's more like a temp fix. Please post your table structure and query definition. We can see how to properly address this problem
    – Edward Dortland
    Commented Sep 3, 2012 at 17:20
  • Thanks for the feedback. It make sense that SQL only treat it as a block and not a deadlock. Sorry I will not going to post any table structure because I know the reason for the application deadlock. The code need to be fix, and need to be deploy to customer site, and you are right that I was looking for a temporary solution.
    – dsum
    Commented Sep 3, 2012 at 23:36
  • [Update] - Here is the link to the script that we use to find the blocked SQL(s): blog.sqlauthority.com/2010/10/06/…
    – dsum
    Commented Sep 4, 2012 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.