Hello SSC,
I am not a DBA,. I have some DBA knowledge, but my education is all programming. I had a production deadlock that took over 30 hours to rollback. I am concerned about data loss, but I have no idea how to troubleshoot this. I have a bunch of logs from the DBA's, so I know which tables deadlocked, but how do you check for data loss? 30 hours seems like a really long time for a rollback.
Any assistance would be greatly appreciated,
Dave
Everyone has a plan until they get punched in the mouth. --Mike Tyson
That is a very long time for a rollback.
So, you did experience data loss. The transaction that was rolled back, whatever it was doing that took 30 hours to undo (woof), that data is gone.
Now, would that have lead to data loss elsewhere? No. Almost 100%, without a doubt, no.
BUT!!!!
With a rollback going on that long, I'd be a little concerned that something else was up inside my system. First, make darned sure you have good, tested, backups (a tested backup is one that you've restored, only way to be 100% on backups is to do a restore from them). Next, run a consistency check on the database (DBCC CHECKDB). Be sure it's not corrupt in some way. Then, check your maintenance routines. Are statistics getting updated, stuff like that.
But, no, a rollback normally won't lead to any additional data loss beyond what is within the rollback itself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2024 at 1:32 pm
Thank you so much Grant! That's a relief.
I have enough information to present to the DBA group and I learned something.
Thank you again. Stay safe and be well!
Dave
Everyone has a plan until they get punched in the mouth. --Mike Tyson
April 29, 2024 at 5:36 pm
A 30 hour rollback kind of makes me think something was using explicit transactions and not committing them and held a connection for a long time. Had that happen on one of our systems ages back and if you left the application running for a long period of time, you'd eventually get a deadlock and have data rolled back. PLUS if you exited the application, since no commit happened, when the connection was closed, SQL rolled back the transaction. So the application would not write data to one of the tables. Thankfully, that table was not critical and the bug was found and corrected, but not a fun bug to find.
The other part of me wonders why SQL decided that was the "easier" one to roll back. How long would any of the other transactions taken to roll back? That is a scary hypothetical, eh? 30 hours is the "fast" rollback according to the SQL engine... yikes.
BUT just to reiterate what Grant said - anything that was in the transaction that was rolled back will be lost with no way to recover UNLESS the application that was putting the data in has a retry mechanism of some sort. The query that was rolled back, if it was a SELECT (unlikely since it took 30 hours to complete and it is rare to get a deadlock with a select in my experience), no data loss. If it was an update, insert, or delete, then any changes that transaction did are un-done with no way to recover them or see what was lost UNLESS you had some trace running (extended events session for example) that captured that data.
To add to Grant's checking backups and database consistency, which I do recommend to do on a regular basis, not just in times of troubleshooting/DR, I would review the query that was run (from reading the deadlock graph) to see what it was doing (insert, update, delete, select) and then review the impacted objects IF it makes sense. What I mean by "if it makes sense" is IF you understand what the data should look like AND you know what changes to that table would have taken place when the query ran. If you don't know the data (often the case for a DBA), reach out to someone who does to review the data and see if it looks right.
I would also look at the deadlock graph and try to see when the query started and when it was rolled back. If the duration is long (days for example), then I'd be concerned about something having explicit transactions without a commit which should be investigated. In my experience, a rollback USUALLY takes however long the query ran for approximately. So if a query runs for an hour and needs to be rolled back, it'll take an hour-ish (assuming no blocking occurred during the query execution and there was no idle time in the query). Not always the case as it depends (like all things in SQL), but for the majority of the cases that I see on my systems for rollbacks, the rollback takes just as long as the query ran minus some of the blocking time and some of the idle time. By idle time I mean if you have a WAITFOR in your query or if you use explicit transactions without a commit/rollback and the query hit the end and is waiting for the COMMIT/ROLLBACK TRANSACTION statement.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 29, 2024 at 9:51 pm
as already stated, its bit surprisng that the database engine thought a transaction large enough to warrant a 30 hour rollback was the least busy transaction. Was this transaction actually a deadlock victim or did this rollback for another reason maybe
What did the deadlock info show
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 10, 2024 at 9:39 pm
Rollbacks are single threaded so it can take much longer than than the parallel operation that got you there.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply