However, I would still be interested in knowing if there is a general way to recover from deadlocks on a read. That way the locks on the rows can be removed right away. I could store the ids returned in a List and loop through that. This code has been working fine for a while, but we are running quite a bit of code for each record, so I can imagine one of those processes is creating a lock on the initial table being read through.Īfter some thought, Scottie's suggestion to use a data structure to store the results would likely fix this situation. (No, there is no way to perform the update in the initial query, many other things occur for each record that is returned). Then I iterate through that list of ids using the Read function and run an update process on that record, which will eventually update a value for that record in the database. This case is not easy to reproduce so I'd like to be sure about it before I modify any of my code.Īs requested, some more info about my situation: In one case, I have a process that performs a query that loads a list of record ids that need to be updated. Will retrying Read after a deadlock skip any rows? Also, should I call Thread.Sleep between retries to give the database time to get out of the deadlock state, or is this enough. Is this the right approach? I want to be sure that records aren't skipped in the reader. ![]() If (ex.ErrorCode != 1205 || -DeadLockRetry = 0) Like this: public class MyDataReader : SqlDataReader ![]() I could create my own class that inherits from SqlDataReader which simply overrides the Read function with retry code. I was thinking about using a similar retry process. ![]() This worked very well for the case where the deadlock happened during the initial query execute, but now we are getting deadlocks while iterating through the results using the Read() function on the returned SqlDataReader.Īgain, I'm not concerned about optimizing the query, but merely trying to recover in the rare case that a deadlock occurs. If (err.Number != 1205 || -DeadLockRetry = 0) throw an exception if the error is not a deadlock or the deadlock still exists after 5 tries To fix this we've added retry code to simply run the query again, like this: //try to run the query five times if a deadlock happends In the past, we've had some deadlocks that occur while calling the ExecuteReader function on our SqlCommand instance. This is usually very rare as we have already optimized our queries to avoid deadlocks, but they sometimes still occur. NET application show that it occasionally deadlocks while reading from Sql Server. '\.\system_health*.The event logs for my. Timestamp_utc, datediff(s,timestamp_utc,GETUTCDATE()) sec_agoĬast(SERVERPROPERTY('ErrorLogFileName') as varchar(max)) + You can see this by comparing the deadlock message in the errorlog with the deadlock xml in the system_health XEvents session, which you can query like this: SELECTĬONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport, ![]() More CPUs, and is automatically enabled and cannot be disabled. This feature is only available for systems with 16 or Sophisticated managed code can be written to notice deadlocks, and attempt one-by-one back-off of the locks that it has accumulated. Locking performance by splitting a single lock resource into multiple Since all managed code is implicitly wrapped in a transaction, this works very well in conjunction with SQL Server's ordinary data-based deadlock detection. For large computer systems, locks on frequently referenced objects canīecome a performance bottleneck as acquiring and releasing locks placeĬontention on internal locking resources.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |