If that doesn't reveal anything, you can analyze deadlocks using the SQL Server Profiler: You should analyze your process and see if there are scenarios where locks could be acquired in a different order. It applies here because DELETE and UPDATE statements implicitely acquire locks on the rows or index range or table (depending on what the engine deems appropriate). How would acquiring locks in the same order apply here? Have you got any advice on how he would change his SQL to do that?ĭeadlocks are always the same, no matter what environment: two processes (say A & B) acquire multiple locks (say X & Y) in a different order so that A is waiting for Y and B is waiting for X while A is holding X and B is holding Y. Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.Use a row versioning-based isolation level.Keep transactions short and in one batch.Avoid user interaction in transactions.There's a good reason it is listed first. You can find the same advice in this technical article on regarding Minimizing Deadlocks. acquire locks in the same order, for different processes. The general advice regarding deadlocks: make sure you do everything in the same order, i.e. xdl, I'm a bit weary of attaching the whole thing. I'm not sure if these are the same as the deadlocks that occur without any table hints as I wasn't able to reproduce any of those.Īsk if there's anything else needed from the. UPDATE: These are the two arrangements of deadlocks that occur with ROWLOCK, they both refer only to the delete statement and the non-clustered index it uses. I'll try to get some deadlock logs up if I can't get this solved today (the correct trace flags weren't enabled before). UPDATE (additional information): All deadlocks so far have happened in the update and delete phase, none in the select. Or should I just accept that deadlocks will happen and retry the query in my application? Would it be safe to use NOLOCK on my select in this case, given there is no row overlap between chunks? Then TABLOCKX, HOLDLOCK would only block the update and delete, correct? Then I tried TABLOCKX, HOLDLOCK on the update, but that means I can't perform my select in parallel so I'm losing the advantages of parallelism.Īny idea how I can avoid deadlocks but still process multiple parallel chunks? I tried using ROWLOCK for the update and delete but that caused even more deadlocks than before for some reason, even though there are no overlaps between chunks. I am getting deadlocks when running this with multiple parallel chunks. Where NON_CLUSTERED_ID = FOO where NON_CLUSTERED_ID in. These also use non-clustered index seeks. Then, we process these rows in c# and write changes as a single update and multiple deletes, this happens many times per chunk. This query uses a non-clustered seek (TIME column) merged with a clustered lookup. There are no other connections to this database while the application is running.įirst, select a chunk of rows within a specific time span. I have an application connected to a SQL Server 2014 database that combines several rows into one.
0 Comments
Leave a Reply. |