Intermittent SQL deadlock seen during the execution of 'UpdatePstLocatorTask' stored procedure
Article ID: 100066996
Updated On:
Description
Error Message
The following error is seen in the d-trace log:
895919 11:44:44.530 [10060] (DirectoryService) <66140> EV~I DirectoryService: ADO: UpdatePstLocatorTask |
905396 11:44:46.507 [10060] (DirectoryService) <66140> EV:H {CADODataAccess::ExecuteSQLCommand} HRXEX fn trace : Error [0x80004005], ref(1): [ESC12], [d:\builds\57\ev\x-prod-x\sources\source\common\adointerface\adodataaccess.cpp, lines \{1326,1328,1347,1365}, built Feb xx 09:25:37 2023].
905397 11:44:46.507 [10060] (DirectoryService) <66140> EV:L CADODataAccess::ExecuteSQLCommand Encountered error. hr=Unspecified error (0x80004005) Command retry count:{0}, Connection retry count {0}, retry flag {0}
905398 11:44:46.507 [10060] (DirectoryService) <66140> EV:L DecodeAndLogError entry
905399 11:44:46.507 [10060] (DirectoryService) <66140> EV:H {DecodeAndLogError:#922} Error Description: [Transaction (Process ID 671) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]
905400 11:44:46.507 [10060] (DirectoryService) <66140> EV:H {LogADOError:#1112} Native error: [1205]
We see below in the deadlock graph:
Cause
The Stored procedure 'UpdatePstLocatorTask' executes to update the task table in the EnterpriseVault Directory Database. However, when this stored procedure gets executed simultaneously by multiple threads, deadlock is seen while converting the SQL shared lock to an exclusive lock. This occurs due to an incorrect isolation level used in the 'UpdatePstLocatorTask' stored procedure.
Resolution
Change the isolation level from REPEATABLE READ to READ COMMITTED in the stored procedure 'UpdatePstLocatorTask'.
Note: Please contact the SME team to seek assistance with the above plan of action.
Issue/Introduction
Additional Information
