Conclusion:ĭo the things suggested in the bullets above to fix Deadlocks. In my case, I think only one table alone was enough to cause the deadlock ( intra-query parallelism) but I could be wrong.Īs soon as indexes were added on the column referenced in the WHERE clause, the problem went away! As a nice side-effect, the job also runs faster now. This increased time increases the possibility of deadlocks. The other related tables being updated also lacked an index on the column referenced in the WHERE clause. Because of the lack of indexes, it has to do a full-scan to do the UPDATE in all 10 sessions which increases the amount of time spent in a transaction (although not explicit). To cut to the chase, the problem was because the tables being updated were lacking indexes on the column used in the WHERE clause and had several million rows. Since this piece of code could be running simultaneously in 10 other sessions (updating using different key ID’s that are close to each other since they come from a sequence), we have a deadlock situation. If I had been running the code serially, I am sure there might not have been a problem as there would not have been a deadlock situation. There are two other related tables around this update that were also being updated. ArgumentList $RepositoryDatabaseType, $Repositor圜onnectionString, $sql, 'DataTable', $rowItemProbeRunCOnfig.QueryTimeout, $RepositoryDotNetProviderType Invoke-Command -ScriptBlock $sbTrackingEvent ` WHERE ProbeTargetRunId = " -f $setItem.Key StatusMessage = 'Probe succeeded but returned no output!', If (($resultSetCount -eq 0) -or ($totalRowCount -eq 0)) I have copied over the PowerShell snippet where the failure was happening. In this case, I knew the exact UPDATE statements where the error was happening. My Deadlock case:īeing someone who logs all code obsessively can be a helpful trait at times (here is nice TSQL Logger if you need one). E.g., Always Customers first, Orders second, OrderDetails third.Ĥ) Examine the indexes on the affected tables to eliminate full-table scansĥ) Reduce the amount of time spent in a transactionĦ) Update in chunks especially if updating/deleting across sessionsħ) Avoid RBAR (Row By Agonizing Row) CRUD operations! Do statement based mass changes. I am not going to rehash what has already been said although these high level points are worth noting to resolve them:ġ) Examine known Parallelism (where you have parallelized jobs)Ģ) Examine unknown Parallelism (unknown jobs or users interfere with your jobs in parallel)ģ) Arrange order of tables doing DML to be the same across all code. If you need a refresher, I recommend this good article. We know what deadlocks are and some of the common reasons they happen. Unfortunately, I don’t have the deadlock graph to show. The difference from what I understand in this StackExchange question is that the former is caused by intra-query parallelism. Notice that this is slightly different from a similar error belowĮrror 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Transaction (Process ID) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Recently, the job kept failing with the below error: As it runs, it maintains the fetch status/result of every operation in database tables which can be consulted if something were to go wrong. It is actually a PowerShell job that runs every night in parallel to collect the data from hundreds of instances (both SQL Server and Oracle). I have a nightly SQL Agent job that aggregates backup information from system views to power a Power BI dashboard that the DBA’s review periodically for database backup failures.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |