Prevent Deadlocks in SQL Server Databases

Owned by Kavitha Thangaswamy (Unlicensed)
Last updated: Aug 22, 2023 by Pamela Totas (Deactivated)
2 min read

When you submit Data Mart models to build in a SQL Server database, database deadlocks might cause the mart load to fail. This may occur when more than one Data Mart Manager engine is running at the same time, and a child engine of each tries to insert or update rows of the same table at the exact same time. Multiple Data Mart Manager engines run at the same time during:

  • An ad hoc Submit for a date range, where each date generates its own Data Mart Manager engine, and multiple dates process concurrently.

  • A build of the Mart using Process Manager, initiating multiple simultaneous workflows that each launch a Data Mart Manager engine.

If deadlocks occur in your SQL Server database during the mart build process, you can configure a way to eliminate them using the PACE System Parameters, parameter #149, Retry count to resolve deadlocks in SQL Server.

To configure system parameter:

  1. From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.

  2. Enter System Management Center in the Start Search text box and click the System Management Center link. 
    You see the System Management Center opens with the Service workspace.

  3. From the left navigation, select System Settings > System Parameters. 
    You see the PACE system parameters workspace with all the available settings configured for the current environment.

  4. Select the setting no. 149 and click Edit in the Home tab.
    You see the system parameter details open in edit mode.

  5. In the Value box, enter the new value for the setting.
    If you set this parameter to a value greater than 0, then whenever a database deadlock occurs, the engine makes that number of attempts to perform the operation.
    NOTE: Eagle recommends a value of 5 for the retry count. The slight delay involved in each retry should be enough time for the contending process to finish and permit access the new process, avoiding a deadlock. There is no overhead associated with choosing a retry count, as retries are only conducted when needed.

  6. Click Save to save the changes.
    The Last Modified on and Last Modified By fields updated to display your user ID and the current timestamp. 

    Â