Tags · ServiceStack/ServiceStack

v10.1.4

Fix DbJobs deadlocks and optimize queue queries with indexing on Back…

…groundJob

- Added [Index] attribute to BackgroundJobBase.DependsOn
- Added [CompositeIndex] on (CompletedDate, RequestId, RunAfter) to optimize
  frequent polling queries in DispatchPendingJobs()

Should prevent cyclic database deadlocks in SQL Server and reduce lock contention/scan
overhead across all database providers (SQL Server, SQLite, PostgreSQL, MySQL)
during background job execution and archival.

When multiple scheduled tasks or background workers complete concurrently,
DbJobs.ArchiveJob() executes an UPDATE query to wake up any dependent jobs:
  WHERE CompletedDate IS NULL AND RequestId IS NULL AND DependsOn = @jobid
Because DependsOn lacked an index, database engines (specifically SQL Server)
performed a full Clustered Index Scan on the BackgroundJob table while holding
exclusive row locks from preceding deletions. Concurrent job completions
collided during these table scans, acquiring Update (U) locks on each other's
rows and resulting in cyclic deadlocks (Process chosen as deadlock victim).
In SQLite, the full table scans resulted in longer write lock hold times,
increasing the likelihood of SQLITE_BUSY concurrency errors under load.