High-Throughput Queue Architecture for Video Rendering Jobs using Dapper & SQL Server
When thousands of automated webhook calls request video compositions simultaneously, database locking bottlenecks can cause worker deadlocks and runaway cloud memory consumption. This engineering specification details how to build an atomic, multi-consumer job queue using lightweight Dapper mappings and SQL Server lock concurrency hints.
1. The Multi-Worker Polling Bottleneck
In distributed video rendering clusters, multiple independent Linux containers poll a central job database to claim the next pending video render task. Traditional querying architectures fail under concurrent load:
Two separate render workers execute SELECT TOP 1 ... WHERE Status = 'Queued' simultaneously, causing duplicate rendering jobs for identical videos.
Running separate SELECT and subsequent UPDATE statements creates transaction gaps where database locks collide across worker threads.
Strict table locks block available workers from grabbing subsequent queued tasks while a prior row transition commits.
2. The Atomic Dequeue Pattern: UPDLOCK, READPAST, ROWLOCK
To guarantee that a job is claimed by exactly one rendering node without blocking other concurrent workers from scanning subsequent records, SQL Server combines three table hints inside a Common Table Expression (CTE):
WITH NextJob AS (
SELECT TOP (1)
JobId, Status, WorkerNodeId, LockedAtUtc
FROM dbo.VideoRenderJobs WITH (UPDLOCK, READPAST, ROWLOCK)
WHERE Status = 'Queued'
ORDER BY Priority DESC, CreatedAtUtc ASC
)
UPDATE NextJob
SET
Status = 'Processing',
WorkerNodeId = @WorkerId,
LockedAtUtc = SYSUTCDATETIME()
OUTPUT
inserted.JobId,
inserted.CompositionId,
inserted.PayloadJson,
inserted.RetryCount;| Lock Hint | Mechanical Function | Concurrency Benefit |
|---|---|---|
| ROWLOCK | Restricts locking granularity exclusively to the targeted row. | Prevents page-level and full-table lock escalation under heavy bursts. |
| UPDLOCK | Places update locks during reading instead of shared locks. | Eliminates conversion deadlocks when transitioning from read to write. |
| READPAST | Directs the engine to skip rows locked by other concurrent transactions. | Workers never block or wait on each other; execution latency is zero. |
3. High-Performance C# Repository Implementation
Entity Framework Core introduces change-tracking allocations that degrade throughput during fast worker polling cycles. Dapper executes raw compiled SQL strings directly against the ADO.NET connection pool with negligible CPU overhead:
using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
public record RenderJobTask(
Guid JobId,
string CompositionId,
string PayloadJson,
int RetryCount
);
public interface IRenderQueueRepository
{
Task<Guid> EnqueueAsync(string compositionId, string payloadJson, int priority = 0);
Task<RenderJobTask?> ClaimNextJobAsync(string workerNodeId, CancellationToken ct = default);
Task CompleteJobAsync(Guid jobId, string outputVideoUrl);
Task MarkFailedAsync(Guid jobId, string errorMessage);
}
public class DapperRenderQueueRepository : IRenderQueueRepository
{
private readonly string _connectionString;
public DapperRenderQueueRepository(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("SqlDatabase")
?? throw new InvalidOperationException("Connection string 'SqlDatabase' is missing.");
}
private IDbConnection CreateConnection() => new SqlConnection(_connectionString);
public async Task<Guid> EnqueueAsync(string compositionId, string payloadJson, int priority = 0)
{
const string sql = @"
INSERT INTO dbo.VideoRenderJobs (JobId, CompositionId, PayloadJson, Priority, Status, CreatedAtUtc)
OUTPUT inserted.JobId
VALUES (NEWSEQUENTIALID(), @CompositionId, @PayloadJson, @Priority, 'Queued', SYSUTCDATETIME());";
using var conn = CreateConnection();
return await conn.ExecuteScalarAsync<Guid>(sql, new { compositionId, payloadJson, priority });
}
public async Task<RenderJobTask?> ClaimNextJobAsync(string workerNodeId, CancellationToken ct = default)
{
const string sql = @"
WITH NextJob AS (
SELECT TOP (1) JobId, Status, WorkerNodeId, LockedAtUtc
FROM dbo.VideoRenderJobs WITH (UPDLOCK, READPAST, ROWLOCK)
WHERE Status = 'Queued'
ORDER BY Priority DESC, CreatedAtUtc ASC
)
UPDATE NextJob
SET Status = 'Processing',
WorkerNodeId = @workerNodeId,
LockedAtUtc = SYSUTCDATETIME()
OUTPUT
inserted.JobId,
inserted.CompositionId,
inserted.PayloadJson,
inserted.RetryCount;";
using var conn = CreateConnection();
return await conn.QueryFirstOrDefaultAsync<RenderJobTask>(new CommandDefinition(
sql,
new { workerNodeId },
cancellationToken: ct
));
}
public async Task CompleteJobAsync(Guid jobId, string outputVideoUrl)
{
const string sql = @"
UPDATE dbo.VideoRenderJobs
SET Status = 'Completed',
OutputUrl = @outputVideoUrl,
CompletedAtUtc = SYSUTCDATETIME()
WHERE JobId = @jobId;";
using var conn = CreateConnection();
await conn.ExecuteAsync(sql, new { jobId, outputVideoUrl });
}
public async Task MarkFailedAsync(Guid jobId, string errorMessage)
{
const string sql = @"
UPDATE dbo.VideoRenderJobs
SET Status = CASE WHEN RetryCount >= 3 THEN 'DeadLetter' ELSE 'Queued' END,
RetryCount = RetryCount + 1,
LastErrorMessage = @errorMessage,
WorkerNodeId = NULL,
LockedAtUtc = NULL
WHERE JobId = @jobId;";
using var conn = CreateConnection();
await conn.ExecuteAsync(sql, new { jobId, errorMessage });
}
}4. Resilient Worker Daemon & Zombie Job Recovery
If a render node encounters a kernel panic or out-of-memory container termination while rendering a video, a claimed job could remain trapped in Processing state indefinitely.
A periodic watchdog stored procedure resets abandoned rows where LockedAtUtc < DATEADD(MINUTE, -10, SYSUTCDATETIME()) back into the Queued pool, ensuring guaranteed message delivery across the render pipeline.