question

VijayKumar768 avatar image
0 Votes"
VijayKumar768 asked ErlandSommarskog commented

Deadlock Report

2021-03-29 14:18:06.61 spid45s deadlock-list
2021-03-29 14:18:06.61 spid45s deadlock victim=process1022605f848 ---->
2021-03-29 14:18:06.61 spid45s process-list
2021-03-29 14:18:06.61 spid45s process id=process1022605f848 ---> taskpriority=0 logused=0 waitresource=KEY: 8:72058571181719552 (8f457811b5c3) waittime=1547 ownerId=4815203484 transactionname=user_transaction lasttranstarted=2021-03-29T14:17:39.277 XDES=0xbd7ae7f210 lockMode=U schedulerid=9 kpid=32168 status=suspended spid=80 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-03-29T14:18:05.067 lastbatchcompleted=2021-03-29T14:18:05.070 lastattention=1900-01-01T00:00:00.070 clientapp=.Net SqlClient Data Provider hostname=AKKUPWVISS012 hostpid=331024 loginname=US\SRCCAEIIS isolationlevel=serializable (4) xactid=4815203484 currentdb=8 currentdbname=TESTDB1 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
2021-03-29 14:18:06.61 spid45s executionStack
2021-03-29 14:18:06.61 spid45s frame procname=TESTDB1.dbo.OrgMilestoneUpdate line=93 stmtstart=4738 stmtend=6694 sqlhandle=0x03000800ca3c6b6af6886701cdac000001000000000000000000000000000000000000000000000000000000
2021-03-29 14:18:06.61 spid45s UPDATE ORG_MILESTONE SET
2021-03-29 14:18:06.61 spid45s name = @Name,
2021-03-29 14:18:06.61 spid45s role_mask = @Role,
2021-03-29 14:18:06.61 spid45s update_date = GetDate(),
2021-03-29 14:18:06.61 spid45s meta_data = @Metadata,
2021-03-29 14:18:06.61 spid45s consent = @Consent,
2021-03-29 14:18:06.61 spid45s pre_offset = @PreOffset,
2021-03-29 14:18:06.61 spid45s post_offset = @PostOffset,
2021-03-29 14:18:06.61 spid45s reminder_recurrence = @Recurrence,
2021-03-29 14:18:06.61 spid45s reminder_duration = @Duration,
2021-03-29 14:18:06.61 spid45s is_org_task = @IsTtask,
2021-03-29 14:18:06.61 spid45s notification_mask = @NotifMask,
2021-03-29 14:18:06.61 spid45s execution_meta_data = @ExecutionData,
2021-03-29 14:18:06.61 spid45s early_cost_id = @EarlyCostId,
2021-03-29 14:18:06.61 spid45s late_cost_id = @LateCostId,
2021-03-29 14:18:06.61 spid45s duration_type = (CASE WHEN @DurationType IS NULL THEN duration_type ELSE @DurationType END),
2021-03-29 14:18:06.61 spid45s text_id = NULL,
2021-03-29 14:18:06.61 spid45s user_id = @AssignedTo,
2021-03-29 14:18:06.61 spid45s VF_01 = @VF_01,
2021-03-29 14:18:06.61 spid45s VF_02 = @VF_02,
2021-03-29 14:18:06.61 spid45s VF_03 = @VF_03,
2021-03-29 14:18:06.61 spid45s VF_04 = @VF_04,
2021-03-29 14:18:06.61 spid45s VF_05 = @VF_05,
2021-03-29 14:18:06.61 spid45s VF_06 = @VF_06,
2021-03-29 14:18:06.61 spid45s VF_07 = @VF_07,
2021-03-29 14:18:06.61 spid45s VF_08 = @VF_08,
2021-03-29 14:18:06.61 spid45s VF_09 = @VF_09,
2021-03-29 14:18:06.61 spid45s VF_10 = @VF_10,
2021-03-29 14:18:06.61 spid45s member_goal_priority_id = @MemberGoalPriorityId,
2021-03-29 14:18:06.61 spid45s category_id = CASE @category_id WHEN 0 THEN NULL ELSE @category_id END
2021-03-29 14:18:06.61 spid45s WHERE
2021-03-29 14:18:06.61 spid45s id = @MilestoneI
2021-03-29 14:18:06.61 spid45s inputbuf
2021-03-29 14:18:06.61 spid45s Proc [Database Id = 8 Object Id = 1785412810]
2021-03-29 14:18:06.61 spid45s process id=process1022602fc28 taskpriority=0 logused=0 waitresource=KEY: 8:72058571181719552 (8f457811b5c3) waittime=1623 ownerId=4815389623 transactionname=user_transaction lasttranstarted=2021-03-29T14:17:59.483 XDES=0xa8bafcc590 lockMode=X schedulerid=3 kpid=32064 status=suspended spid=182 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-03-29T14:18:04.990 lastbatchcompleted=2021-03-29T14:18:04.993 lastattention=1900-01-01T00:00:00.993 clientapp=.Net SqlClient Data Provider hostname=AKKUPWVISS012 hostpid=331024 loginname=US\SRCCAEIIS isolationlevel=serializable (4) xactid=4815389623 currentdb=8 currentdbname=TESTDB1 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
2021-03-29 14:18:06.61 spid45s executionStack
2021-03-29 14:18:06.61 spid45s frame procname=TESTDB1.dbo.OrgMilestoneUpdate line=93 stmtstart=4738 stmtend=6694 sqlhandle=0x03000800ca3c6b6af6886701cdac000001000000000000000000000000000000000000000000000000000000
2021-03-29 14:18:06.61 spid45s UPDATE ORG_MILESTONE SET
2021-03-29 14:18:06.61 spid45s name = @Name,
2021-03-29 14:18:06.61 spid45s role_mask = @Role,
2021-03-29 14:18:06.61 spid45s update_date = GetDate(),
2021-03-29 14:18:06.61 spid45s meta_data = @Metadata,
2021-03-29 14:18:06.61 spid45s consent = @Consent,
2021-03-29 14:18:06.61 spid45s pre_offset = @PreOffset,
2021-03-29 14:18:06.61 spid45s post_offset = @PostOffset,
2021-03-29 14:18:06.61 spid45s reminder_recurrence = @Recurrence,
2021-03-29 14:18:06.61 spid45s reminder_duration = @Duration,
2021-03-29 14:18:06.61 spid45s is_org_task = @IsTtask,
2021-03-29 14:18:06.61 spid45s notification_mask = @NotifMask,
2021-03-29 14:18:06.61 spid45s execution_meta_data = @ExecutionData,
2021-03-29 14:18:06.61 spid45s early_cost_id = @EarlyCostId,
2021-03-29 14:18:06.61 spid45s late_cost_id = @LateCostId,
2021-03-29 14:18:06.61 spid45s duration_type = (CASE WHEN @DurationType IS NULL THEN duration_type ELSE @DurationType END),
2021-03-29 14:18:06.61 spid45s text_id = NULL,
2021-03-29 14:18:06.61 spid45s user_id = @AssignedTo,
2021-03-29 14:18:06.61 spid45s VF_01 = @VF_01,
2021-03-29 14:18:06.61 spid45s VF_02 = @VF_02,
2021-03-29 14:18:06.61 spid45s VF_03 = @VF_03,
2021-03-29 14:18:06.61 spid45s VF_04 = @VF_04,
2021-03-29 14:18:06.61 spid45s VF_05 = @VF_05,
2021-03-29 14:18:06.61 spid45s VF_06 = @VF_06,
2021-03-29 14:18:06.61 spid45s VF_07 = @VF_07,
2021-03-29 14:18:06.61 spid45s VF_08 = @VF_08,
2021-03-29 14:18:06.61 spid45s VF_09 = @VF_09,
2021-03-29 14:18:06.61 spid45s VF_10 = @VF_10,
2021-03-29 14:18:06.61 spid45s member_goal_priority_id = @MemberGoalPriorityId,
2021-03-29 14:18:06.61 spid45s category_id = CASE @category_id WHEN 0 THEN NULL ELSE @category_id END
2021-03-29 14:18:06.61 spid45s WHERE
2021-03-29 14:18:06.61 spid45s id = @MilestoneI
2021-03-29 14:18:06.61 spid45s inputbuf
2021-03-29 14:18:06.61 spid45s Proc [Database Id = 8 Object Id = 1785412810]
2021-03-29 14:18:06.61 spid45s resource-list
2021-03-29 14:18:06.62 spid45s keylock hobtid=72058571181719552 dbid=8 objectname=TESTDB1.dbo.ORG_MILESTONE indexname=PK_ORG_MILESTONE id=lockd7ed3bd000 mode=U associatedObjectId=72058571181719552
2021-03-29 14:18:06.62 spid45s owner-list
2021-03-29 14:18:06.62 spid45s owner id=process1022602fc28 mode=U
2021-03-29 14:18:06.62 spid45s owner id=process1022602fc28 mode=X requestType=convert
2021-03-29 14:18:06.62 spid45s waiter-list
2021-03-29 14:18:06.62 spid45s waiter id=process1022605f848 mode=U requestType=convert
2021-03-29 14:18:06.62 spid45s keylock hobtid=72058571181719552 dbid=8 objectname=TESTDB1.dbo.ORG_MILESTONE indexname=PK_ORG_MILESTONE id=lockd7ed3bd000 mode=U associatedObjectId=72058571181719552
2021-03-29 14:18:06.62 spid45s owner-list
2021-03-29 14:18:06.62 spid45s owner id=process1022605f848 mode=S
2021-03-29 14:18:06.62 spid45s owner id=process1022605f848 mode=U requestType=convert
2021-03-29 14:18:06.62 spid45s waiter-list
2021-03-29 14:18:06.62 spid45s waiter id=process1022602fc28 mode=X requestType=convert

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ErlandSommarskog commented

Hi VijayKumar768,
Please check this which might help:
82650-01.jpg

82632-02.jpg
Best Regards,
Amelia



01.jpg (223.4 KiB)
02.jpg (76.0 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Exellent presentation.

Can you please provide how to resolve this issue?

0 Votes 0 ·

Can you please provide how to resolve this issue?


Did you read my reply?

We can explain why this deadlock happens, but we don't have enough information on how to resolve it, because that requires detailed knowledge of what the application is doing.

I can think of several ways of addressing this, but your applications team may frown on all of them. And for good reason. And, besides, maybe it simply a mistake that there are two processes running in parallel.

You will need to talk with your applications team, sharing the information we have provided in this thread.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Both processes are running a multi-batch transaction.

Furthermore, both processes are running with isolation level SERIALIZABLE, which means that when they read rows, they will hold sharead locks on them to the end protection. They also take range locks to prevent new rows to be inserted.

My interpretation of the deadlock is that one of the processes has read a row X and is now trying to update row Y. However, the other processes has already updated that row, so it is blocking the first process. That other process is now trying to update row Y.

I can't say how this situation should be resolved, as this requires knowledge about the application.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.