February 28, 2021 at 4:00 pm
Hello DBAs,
I am facing some deadlock issue and after investigation from trace flag capture, found the processes involved. Both processes are running update but based on the parameter which defines different targets ( for eg. One update works on condition "where column1 = 'abc'" and other one on "where column1 = 'def'").
First Question here is if the target set of update are different still how can deadlock happen across processes ? There's no "begin tran" written in any processes so update statement should be treated as single statement inside the procedure, right ?
Resource list shows like this:
<pagelock fileid="3" pageid="65985" dbid="11" subresource="FULL" objectname="dbname.dbo.tablecustom" id="lockid4e3e78936" mode="U" associatedObjectid="24132524677638">
<pagelock fileid="3" pageid="256937" dbid="11" subresource="FULL" objectname="dbname.dbo.tablecustom" id="lockidd0cfah3500" mode="U" associatedObjectid="24132524677638">
Sexon question here is that what to make of it ? With just this information, can I get the data/records which was involved in deadlock ?
February 28, 2021 at 6:30 pm
Ok. I was able to get the records in the involved page crating deadlock... What's the best way to handle page lock ?
All I can think of is to forcing the update with rowlock. (Considering the where clause condition separating the target rows every time, I think it will be better option).
What would u suggest ?
March 1, 2021 at 6:02 pm
March 1, 2021 at 6:14 pm
Thanks for the link Steve. Though I love Pinal and often enjoy visiting his blogs whenever get free time, I didn't find this particular blog helpful (maybe my naive mind missed something?). In my scenario, there's two different processes updating same table but with different condition in WHERE clause so definitely target data is different and I get the deadlock reason as the locks are at page level which may contain multiple records (associated with those attribute values used in WHERE condition in both processes).
My question here is if we can force row lock hint to avoid deadlock ? Will it have a significant performance issue ?
Or, is there any other way to handle it ?
Is retry on deadlock event (using TRY...CATCH method) a good methodology ?
March 1, 2021 at 6:17 pm
Does the table have an index with column1 in it, or preferably where column1 is the first column in an index? If no, SQL would have to scan the table looking for rows that match, and that could easily cause a deadlock situation.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 1, 2021 at 6:20 pm
March 1, 2021 at 6:31 pm
An index might help.
I think your situation is similar to Pinal's, as in there are two processes that are colliding. If your update is really each updating one row, you should have blocking, not deadlocking, even on the same page. IIRC, the only reason this might cause an issue is with some update. If there are no indexes, I'm confused.
March 1, 2021 at 6:36 pm
If there's no index at all, might as well make it a clus index on column1. Yes, an index should help, since it will avoid scanning the whole table for every search for column1.
SQL strongly prefers unique indexes. Therefore, if the table has an identity column, add it to the end of the clus index so you can specify the index as unique. That is, make the index on ( column1, $IDENTITY ) rather than on just ( column1 ).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 1, 2021 at 6:37 pm
Both the processes fire update statement to change status from Y to N based on column1 values. Process 1 updates for column1 = "ABC" & Process 2 for column1 = "DEF". The table has around 300000 records with 5 distinct values for that column1. There's no index on that table because of the nature of the table. Should we create a non clustered index on column1 to avoid deadlocks ? or a clustered index will be helpful ?
March 1, 2021 at 9:22 pm
A clustered index would be the best chance to avoid deadlocks. So there are roughly 60K rows for each column1 value? And you want to change all of them in one UPDATE statement? Hmm, that's gonna take long enough that it could cause some serious blocking no matter how it's done. but the clus index is still the best try.
You may need to rebuild the table periodically if you see too much fragmentation on the table, but the clustering should mean blocks of whole pages are being removed which won't be a problem.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply