Prevent Deadlocks on SqlServer when setting removal time concurrently

What are the steps to reproduce your problem?

1. Setup the engine to use an empty, supported SqlServer instance
2. Create a process model without wait state (one activity is enough, including other elements like variables and decisions will make the problem more severe)
3. Configure a general TTL in the engine configuration or in the process definition
4. Start multiple instances of the process model with a short delay, e.g. using Postman (more instances and a shorter delay will make the problem more prominent)

What is the problem?

• A substantial amount of instances will end up failing in the end event due to deadlock exceptions when setting the removal time on various tables (historic process instance and historic activity instance or historic variable instance and so on)

What would be the expected behavior:

• All process instances finish successfully and set a removal time on all necessary entities

Hints (optional):

• SqlServer creates an update query using an index scan when there is no or only a small amount of data in the tables because it considers this to be faster than using an index seek (that is usually used when more data is contained in the tables as this is usually faster then)
• The index scan is locking substantial amounts of data in the tables opposed to the index seek
• Different transactions block different tables first and start creating deadlocks for the remaining tables to update
• Using an index seek can be forced with SqlServer by using the query hint FORCESEEK
• Using the hint could also be optional and configurable via engine configuration flag if necessary (e.g. if we expect negative impact for current SqlServer users by enabling it by default)
• Forcing the use of this hint would mean to create SqlServer-specific mappings in the following mapping files
  • Attachment.xml
  • Authorization.xml
  • Comment.xml
  • HistoricActivityInstance.xml
  • HistoricBatch.xml
  • HistoricDecisionInputInstance.xml
  • HistoricDecisionInstance.xml
  • HistoricDecisionOutputInstance.xml
  • HistoricDetail.xml
  • HistoricExternalTaskLog.xml
  • HistoricIdentityLinkLog.xml
  • HistoricIncident.xml
  • HistoricJobLog.xml
  • HistoricProcessInstance.xml
  • HistoricTaskInstance.xml
  • HistoricVariableInstance.xml
  • UserOperationLogEntry.xml
  • VariableInstance.xml