Description

By default Sitecore sets Read Committed transaction isolation level for Master, Core and Web databases for on-premise solutions. In Azure Web Apps, however, a Read Committed Snapshot transaction isolation level is used instead.

SQL databases might experience deadlocks under heavy load when multiple concurrent queries are executed. It happens when multiple connections to SQL database are trying to read and write data at the same time, blocking each other and forcing SQL Server to kill one of the requests.

Solution

• If a deadlock appears between READ and WRITE operations then switching isolation level from Read Committed to Read Committed Snapshot helps to mitigate the issue.
• SQL deadlocks return SQL Exception to Sitecore instances with a specific message in Sitecore logs:

  Transaction (Process ID ###) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

• Deadlocks between S lock types and either X or U are READ and WRITE conflicts can be resolved with switching to snapshot isolation.
• To confirm whether the deadlock appears between READ and WRITE operations, a deadlock graph must be collected and shared with Sitecore Support to verify.