Background

SQL Server deadlocks happen when two or more concurrent API threads attempt to access and lock the same database resources (rows, index pages, or tables) in a conflicting order. In Spira (SpiraTest, SpiraTeam, SpiraPlan), high-frequency read and write requests heavily stress the underlying database tables—such as requirements, test runs, or tasks—leading to lock escalation and cycle deadlocks.

1. API Request Strategy & Throttling

  • Reduce Concurrency & Batch Calls: Avoid making hundreds of parallel HTTP threads to the REST API simultaneously. Throttle application calls using a worker queue (e.g., limit to 3–5 concurrent API threads max).

  • Implement Exponential Backoff & Retry Logic: When SQL Server detects a deadlock, it terminates one of the sessions as a deadlock victim (HTTP 500 error from Spira). Build client-side retry logic using exponential backoff with jitter so failed requests retry after a brief delay rather than instantly slamming the database again.

  • Avoid Interleaving Reads and Writes: Do not perform rapid "read-then-write" loops inside parallel loops. Group read operations together, then execute writes sequentially.

2. Spira API Integration Best Practices

  • Use Proper Optimistic Concurrency: Spira relies on a ConcurrencyDate field for updating artifacts via PUT operations. Always perform a GET, modify the returned payload, and send the exact ConcurrencyDate back in the PUT request. This prevents prolonged lock states while waiting for modifications.

  • Optimize GET Request Payload Sizes: Use pagination parameters (starter_row, number_of_rows) when querying endpoints. Fetching thousands of records in a single GET call forces SQL Server to issue large shared locks (S), which directly clash with concurrent UPDATE (X) locks.

  • Avoid Heavy Polling: Replace frequent status polling calls with structured intervals or Webhooks (if using external integrations) to minimize total database operations.

3. SQL Server & Database Maintenance

  • Index Maintenance: Fragmented indexes or missing covering indexes cause SQL Server to perform table scans. Table/page scans widen the lock scope from single rows to entire index ranges or full tables, massively increasing deadlock potential. Run regular index defragmentation and update statistics jobs.

4. Diagnosis & Root Cause Analysis

If deadlocks persist, capture the exact deadlock graphs to see which API endpoints are contending for the same resources:

  • Extended Events: Set up an Extended Event session in SQL Server Management Studio (SSMS) using the xml_deadlock_report event to record the exact SQL statements involved.

  • System Trace Flags: Enable SQL Server trace flags 1204 and 1222 to output deadlock details into the SQL Server Error Log.