We have a Java application / hibernate connecting to SQL Server. It looks like implicit transaction is on by default. I am not sure if this is from hibernate or JDBC Driver . However this implicit transactions are causing alot of blocking. when I checked I always find that the head of the blocking is

set implicit_transactions off IF @@TRANCOUNT > 0 COMMIT TRAN 

My question is what can I perform from the database side to avoid this problem?

enter image description here

  • You're using implicit transactions. Turn them off in the connection string. – Erik Darling Oct 16 at 13:53
  • Can anyone explain how a test for an open transaction and a COMMIT statement may cause a lock? – Ant_222 Oct 16 at 15:07
  • @Ant_222 if you have a question, you should ask one. Asking in a comment isn't likely to get you an answer, and it's sort of hijacking someone else's question. – Erik Darling Oct 16 at 16:13
  • @ErikDarling My comment pertains to this question. I wonder how the line IF @@trancount > 0 COMMIT TRANSACTION can be the head of blocking. It is OK if I don't get an answer to this one, although I should like to. – Ant_222 Oct 16 at 16:32
  • @Ant_222 The test for an open transaction and the commit is not what is causing a lock. If that line is blocking anyone then the change they are trying to commit is what is doing that. This is why trying to analyze blocking by just looking at the last statement is not very useful - you need more context about the entire transaction. – Aaron Bertrand Oct 16 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.