none
Data Committed instead of Rollback after Deadlock Error in SQL Server 2008 R2 (SP2)

    Question

  • We're having a strange issue which is occurring only with one Customer having SQL Server 2008 R2 (SP2).

    Basically we have multiple threads uploading data and when an error occurs (like deadlock or any other error). The deadlock victim (process/transaction) is rolledback (from .NET). However the rollback command is not reaching SQL Server as it doesn't show in the trace (through SQL Profiler).

    To make things worse, not only the transaction is not being rolled back but the INSERTs executed before the error are being somehow committed, leaving the database in an inconsistent state.

    This is only produced in one environment.

    Any idea what the issue could be?

    Wednesday, August 20, 2014 9:47 AM

Answers

  • All statements are executed with in a Transaction.

    Or so you think. It is clear from the symptoms you describe that this is not the case.

    Under the same scenario this code works perfectly fine for 1000s of customers. Only one customer has this issue.

    I can only work from what you told me, and from that description it seems to me that there is a flaw in your code. The fact that it only exhibits at one customer may be due to that this customer is running a different version of your application. It can also be that the flaw only exhibits under certain conditions.

    What I also can say is that there is no magic setting in SQL Server that you can flip and your application will start working.

    You will have to start debugging. And scrutinise your code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 20, 2014 12:33 PM

All replies

  • This may be due to IMPLICIT_TRANSACTIONS property. Please go through teh following link to check.

    http://msdn.microsoft.com/en-IN/library/ms187807(v=sql.105).aspx

    Wednesday, August 20, 2014 10:20 AM
  • It seems that you have a problem with the scope of your transactions. Or to be blunt: you have a bug in your code. Apparently the INSERT before the statement that deadlocked is not part of the same transaction where the deadlock appeard. But from what you say, maybe it should be in the same transaction.

    No rollback is needed as such after a deadlock, as a deadlock aborts the current batch and rolls back any open transaction.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 20, 2014 10:43 AM
  • But we are explicitly starting a transaction before any INSERTs therefore even if IMPLICIT_TRANSACTIONS is SET ON it doesn't matter here.
    Wednesday, August 20, 2014 10:45 AM
  • All statements are executed with in a Transaction. Under the same scenario this code works perfectly fine for 1000s of customers. Only one customer has this issue.
    Wednesday, August 20, 2014 10:49 AM
  • All statements are executed with in a Transaction. Under the same scenario this code works perfectly fine for 1000s of customers. Only one customer has this issue.
    You need to capture profiler to check transaction scope.

    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, August 20, 2014 11:58 AM
    Moderator
  • All statements are executed with in a Transaction.

    Or so you think. It is clear from the symptoms you describe that this is not the case.

    Under the same scenario this code works perfectly fine for 1000s of customers. Only one customer has this issue.

    I can only work from what you told me, and from that description it seems to me that there is a flaw in your code. The fact that it only exhibits at one customer may be due to that this customer is running a different version of your application. It can also be that the flaw only exhibits under certain conditions.

    What I also can say is that there is no magic setting in SQL Server that you can flip and your application will start working.

    You will have to start debugging. And scrutinise your code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 20, 2014 12:33 PM