MS SQL Server - Frequent Deadlock Issues

Question

MS SQL Server - Frequent Deadlock Issues

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

To start with, the deadlocks are on row-level locks, not table level, so lock escalation will have no effect here. Please turn it back on.

The key, I suspect, is this:

transactionname="implicit_transaction"

That means that a transaction gets automatically started when a statement runs, and only gets committed when an explicit COMMIT TRANSACTION is run.
This is not the default in SQL Server, so someone would have had to either set this in the application (session-level setting), or by SET IMPLICIT TRANSACTIONS ON

I suspect that transactions are getting started, and not committed, and since locks taken from data modifications are held until the end of the transaction, you're having long-running transactions holding locks longer than expected, and hence causing deadlocks.
I would recommend removing the implicit transaction settings and explicitly creating and committing transactions where necessary, and allowing autocommit where they're not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sandeep.shah SSC Veteran Points: 201 More actions · Answer 2

GilaMonster - Wednesday, November 15, 2017 1:12 AM
To start with, the deadlocks are on row-level locks, not table level, so lock escalation will have no effect here. Please turn it back on.
The key, I suspect, is this:
transactionname="implicit_transaction"
That means that a transaction gets automatically started when a statement runs, and only gets committed when an explicit COMMIT TRANSACTION is run.
This is not the default in SQL Server, so someone would have had to either set this in the application (session-level setting), or by SET IMPLICIT TRANSACTIONS ON
I suspect that transactions are getting started, and not committed, and since locks taken from data modifications are held until the end of the transaction, you're having long-running transactions holding locks longer than expected, and hence causing deadlocks.
I would recommend removing the implicit transaction settings and explicitly creating and committing transactions where necessary, and allowing autocommit where they're not.

Thanks GilaMonster for detailed analysis.

Actually, we are using jTDS JDBC driver via Java Application to execute this queries. I have verified the code that if autocommit is set to false on JDBC connection, jTDS driver in turn fires SET IMPLICIT TRANSACTIONS ON command to the database. Implicit transaction starts on database for any sql statement fired afterwards. This behavior of jTDS driver is similar to Microsoft SQL Server JDBC driver.
Transaction boundaries are small and should not take more than 1 second between Service-DB layer.
One more point to note that we have tested same functionality on Oracle with similar load (50 concurrent threads) and it works well without any deadlocks.