4

I have multiple database sessions from which one has been blocking others because of a frozen application. There are transactions running on multiple sessions. I need to kill the head blocker because of Lock request timeouts on other sessions. After killing that session other user have reported data loss. I am pretty sure they have committed their transactions but they still seem to have been rolled back. They can even proof it because some documents have been printed which are missing data afterwards.

Is it possible that committed transactions are being rolled back because of a rollback on another session? I would think not, but after reading the COMMIT documentation I have doubts. It says:

If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

Reading the TRANCOUNT documentation it says:

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

So is the current connection the same thing as a session or can multiple sessions share the same connection (maybe by pooling)? And if they can share, how does this relate to transactions? Can it be that these active transactions are being rolled back later on and even after they have been committed?

[edit] To be more clear, some users didn't get a Lock timeout because their data didn't conflict with the head blocker. They committed transactions but those transactions seemed to have been rolled back after killing the head blocker.

[Update 2018-03-29] After a new incident I had the opportunity to investigate the problem while it happened. The transaction logs concluded that the problem was a nested transaction that never committed. The application that caused the problem wasn't frozen so the user never noticed it until he closed the application. At that point he lost his data because the transaction was rolled back. I accepted the best answer possible and that was the one telling me it wasn't possible to roll back a committed transaction. I guess it really isn't possible, it's just hard to find the real problem.

Improve this question

2 Answers 2

Reset to default
13

Generally, a transaction is exactly one of

  • committed
  • rolled back

Committed transactions are never rolled back.
It's how all RDBMS operate, on ACID principles

Now, there are some different cases where it may look like this rule has been broken. But it hasn't.

Before we look at these cases though, different user sessions do not share a connection. Each user/client has one connection to the SQL and all are isolated from each other. Connection pooling does not affect this.

Savepoints

You can SAVE a transaction and rollback to this savepoin,

That is, you can partially commit/rollback if you use savepoints but I've never seen anyone do this in real life code. I won't expand more.

Nested transactions

You can nest transactions but they don't really mean anything
Simply put, SQL Server does not really have nested transations even if @@TRANCOUNT can be higher than one.

  • A begin increments @@TRANCOUNT by one
  • A rollback will set @@TRANCOUNT to zero
  • A commit decrements @@TRANCOUNT by one

A longer explanation is in this SO answer

Improve this answer
2
  • Nested transactions were the problem. I should have read the manual about transaction names and how they do not apply to nested transactions.
    – Martin
    Commented Mar 29, 2018 at 13:59
  • @Martin I should have mentioned this too sqlskills.com/blogs/paul/…
    – gbn
    Commented Mar 29, 2018 at 14:14
1

1) On the topic of rolling back committed transactions. The Documentation you referred to 'If @@TRANCOUNT is greater than 1,' would happen if in a single session you nest transactions. https://technet.microsoft.com/en-us/library/ms189336%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

In that case the final transaction would commit or roll back it's work and any nested transactions.

2) What you described is multiple sessions wanting to start multiple transactions but being blocked. You are observing 'data loss' because a compatible lock was not available to the requesting transaction and the application could not save its data. I used the term 'data loss' because it really is an application issue. The application should be coded to realize a transaction failed and give the transaction another try or warn the end user so there are no surprises.

The data base engine did just what it was designed to do and prevented an violation of the ACID principle.

Improve this answer
1
  • The data loss is actually data loss because some sessions seem to have started and committed transactions during the locked session. They probably didn't need the locked data and so they didn't receive a Lock timeout. But still their transaction seem to have been rolled back.
    – Martin
    Commented Oct 17, 2017 at 13:22

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.