This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(153.6, 61%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMS%3C/text%3E%3C/svg%3E)
Hello,
I am facing deadlock issue while insert into single table using multiple threads. It is simple insert SP which has code like
Insert into table A output inserted.value into @inserted values(x,y,z).
select @value = value from @inserted
This table A has one PK clustered index on identity column which we get using above output clause and return to app by using an output parameter. Other information to consider is that this sp takes only a second to execute. This table has one PK clustered index and 9 non clustered indexes.
Since this insert sp called by multiple thread at the same time we are facing massive deadlock with wait resource = Object which is the same table. Below is partial deadlock information extracted from deadlock xml file.
-<objectlock id="lock14d48a27e80" associatedObjectId="1013578649" mode="X" objectname="dbo.A" dbid="5" subresource="FULL" objid="1013578649" lockPartition="0">
-<owner-list>
<owner id="process15250169848" mode="IX" requestType="wait"/>
</owner-list>
-<waiter-list>
<waiter id="process151ae699088" mode="X" requestType="wait"/>
</waiter-list>
</objectlock>
-<objectlock id="lock14d48a27e80" associatedObjectId="1013578649" mode="X" objectname="dbo.A" dbid="5" subresource="FULL" objid="1013578649" lockPartition="0">
-<owner-list>
<owner id="process15250169848" mode="IX" requestType="wait"/>
</owner-list>
-<waiter-list>
<waiter id="process153993c2ca8" mode="X" requestType="wait"/>
</waiter-list>
Appreciate if any help from you guys.
Below is partial deadlock information extracted from deadlock xml file.
Partial informations of the deadlock graph are useless, especially the part you picked out.
Also you provided to less on information.
What's the table design and SP code? How many records does the table contains?
Hello, Thanks for your reply.
The table name is Job which has around 18 columns with ID column is PK with Identity type. As of now table has 2.5 million records. The procedure is being called from .net code and having multiple call at the same time. Sometimes we get 400-500 deadlock within 10 minutes. Below is the exact code from SP. It is single record insert without any condition or filter.
----
set nocount on;
set xact_abort on;
declare @inserted table (jobid bigint)
insert into Job (InvocationData, Arguments,CreatedAt, Source,Destination,EventType) output inserted.id into @inserted values (@invocationData, @arguments, getdate(),@Sérgio Carapinha ,@dest,@eventType);
select @jobid = Jobid from @inserted
----
Please find attached deadlock xml.161115-xml-deadlock-report1.xml
That does not look right. Can you post the output of these two queries:
SELECT @@version
SELECT name, index_ix, type, allow_row_locks, allow_page_locks
FROM [ORCA-QUEUE].sys.indexes
WHERE object_id = object_id('[ORCA-QUEUE].CPRTS.Job')
Hello,
The SQL Server version is Microsoft SQL Server 2017 (RTM-CU25) (KB5003830) - 14.0.3401.7 (X64) Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor).
Below is the output of the query.
There are around 350 more DTA created indexes with the same column values as you see in the attached screenshot. Inserting a single record through AddJob procedure takes around 1 second.
Let me know if you need more information.
There are around 350 more DTA created indexes
That was more then a bad idea to let DTA create that mass of indexes, especially if the table have only 18 columns.
Redesign the indexes.
True sir. Those are however hypothetical indexes. Thanks for your help. Wish you a very happy new year in advance.
The index on line 8 is your problem. Either drop this index entirely, or drop it and recreate it with ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON. (These are the default settings.)
If there are more indexes with 0 in any of these columns, you need to perform the same operation with these indexes.
Because of these settings, the INSERT statement needs to take a table lock. With all indexes permitting row locks, you will get row locks, and you should not see deadlocks from concurrents inserts. You may still get latch contention, but that's another story.
350 indexes are just way too many. Although, some of these may have is_hypothetical = 1, which means that they don't really exist, and I should have included a filter on this column. How many rows do you get with:
SELECT name, index_ix, type, allow_row_locks, allow_page_locks
FROM [ORCA-QUEUE].sys.indexes
WHERE object_id = object_id('[ORCA-QUEUE].CPRTS.Job')
AND is_hypothetical = 0
Thanks Sir. I will apply index recommendation and also share query result with you. I will get back on these soon
Hello Sir, yes those indexes are hypothetical and table has only those 10 actual indexes created by users. Secondly i have applied the changes and dropped the index which has page_lock and row_lock off as suggested by you and monitoring the impact. As of now it looks good. I will update you in 2-3 days. However thanks for your valuable suggestion. Shall mark your comment as "Answer" soon. Happy new year to you in advance.