How To Deal With Deadlocks For The Orchestrator Database?

News Knowledge Base
1

Deadlocks in the SQL database can negatively impact the performance of UiPath Orchestrator, causing delays and interruptions. This article discusses the common scenarios where deadlocks can occur in UiPath Orchestrator and provides best practices and recommendations to minimize deadlocks.

Common Deadlock Scenarios:

  1. High concurrency
  2. Long-running transactions
  3. Inconsistent resource access
  4. High database load

Best Practices and Recommendations:

  1. Monitor and Optimize Process Execution

  • Regularly assess the scheduling and execution of jobs, tasks, and processes in Orchestrator
  • Utilize the Orchestrator dashboard to review currently running jobs and identify resource contention
  • Stagger process execution to avoid triggering too many processes simultaneously, alleviating the pressure on shared resources.

  1. Keep Database Transactions Short and Optimize Queries

  • Design workflows to minimize the duration of transactions by limiting the amount of data manipulation and interactions with the SQL database
  • Avoid including activities such as delays or user interactions within transactions
  • Optimize SQL queries used in custom activities to reduce execution time and minimize resource-locking conflicts.

  1. Ensure Consistent Resource Access

  • When designing workflows and custom activities, implement a consistent approach to access and lock database objects like tables or rows
  • Use SQL best practices to ensure proper indexing and efficient database operations
  • Keep the recommended database schema provided by UiPath when making customizations and avoid altering the structure without a proper understanding of its implications.

  1. Scale Infrastructure

  • Regularly monitor SQL Server performance metrics, such as CPU, memory, and disk I/O, to ensure sufficient resources are available to handle workloads
  • Consider implementing SQL Server high availability or scalability features, such as Always On availability groups, log shipping, or replication, to distribute workloads and improve performance.

  1. Analyze and Monitor Deadlocks

  • Implement SQL Server features, such as the Deadlock Graph Event in SQL Server Profiler, extended events, or system_health session to capture deadlock events and information
  • Regularly review deadlock occurrences to identify patterns and potential root causes.
  • Implement necessary changes based on the analysis and monitor the system for ongoing deadlock issues.

  1. Tune SQL Server Settings

  • Adjust SQL Server settings like lock timeouts and isolation levels to balance data consistency and performance requirements.
  • Ensure that SQL Server is properly configured according to UiPath's recommendations in the infrastructure guidelines.

Conclusion:

By following these best practices and recommendations, it is possible to minimize the occurrence of deadlocks in UiPath Orchestrator's SQL database, improving overall performance and reducing interruption risks.

1 Like