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:
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?
Is is possible to write a SQL Agent, that detect this situation and kill one of the session?
Why would SQL Server throw a deadlock victim exception if it is caused by different application, and not for the same application?