![]() ![]() My guess, you're seeing Sch-M/Sch-S deadlock. Then, the TRUNCATE tries to individually grab X locks which deadlock with S locks on a row-by-row basis? The problem is that WITH (NOLOCK) should prevent the S locks from being created, hence no deadlock, right? The closest thing I can think of is that the TRUNCATE is placing an IX lock on the table, and a SELECT is getting a request granted for an IS lock. If SQL Server somehow chooses to wrap it in a transaction, like the above, the WITH (NOLOCK) query hint should mean that Process B never tries to lock anything. ![]() I do not see how Process B should ever be creating a transaction.įurther, let's suppose Process B actually were What I do not understand is how a combination of:Ĭan ever lead Process B to terminate with error " Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Given my understanding of how it's supposed to work, I'm getting behavior that is impossible, so I'm trying to figure out what I've misunderstood. I thought I understood concurrency control, at least on a theoretical level (I've read through the key papers on the subject). I'm trying to better learn and understand locking in SQL Server. Having said that, I think I might have been unclear though in what I'm asking. I've set the flags so that the next time this error comes up (it comes up every 3 days due to the scheduled process), I'll catch it, if it will help to illustrate the problem. I didn't really mean to ask you guys to debug my code though (I can rewrite it in this instance, and I'm trying to learn how to do it myself more generally), and these query hints are more designed to tell SQL Server which queries must lock data and which can't (thinking about failure modes) than they are to optimize performance. Would somebody please help me understand what is happening here and why there is (seemingly?) contention between a SELECT WITH (NOLOCK) and these other queries? I thought the whole point of reading uncommitted data was that in return for accepting I might not get the "right" answer, I would avoid contention and would always be able to return *some* answer. The timeouts all happen after the transaction deadlock errors, and last until the whole TRUNCATE/INSERT/UPDATE process finishes. The timeout period elapsed prior to completion of the operation or the server is not responding. Rerun the transaction.Ģ) Timeout expired. Instead, I end up with an ASP.NET page crash, running through the following progression:ġ) Transaction (Process ID ) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Occasionally, a scheduled process TRUNCATES that table, then re-fills it using INSERT WITH (ROWLOCK) and runs a couple of UPDATE WITH (ROWLOCK) queries on the table.Īs I understand things, the SELECT WITH (NOLOCK) should, during this process, sometimes return no rows, sometimes return uncommitted rows, and sometimes return committed rows. ![]() I have a commonly-accessed ASP.NET page running a SELECT query WITH (NOLOCK) from some table. I'm having a problem I can't quite understand along the lines of the following: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |