| | | 1 | | using AsyncResponse.Internal; |
| | | 2 | | using Microsoft.Data.SqlClient; |
| | | 3 | | using Microsoft.Extensions.Logging; |
| | | 4 | | using Microsoft.Extensions.Options; |
| | | 5 | | using System.Runtime.CompilerServices; |
| | | 6 | | |
| | | 7 | | namespace AsyncResponse.Transports.SqlServer; |
| | | 8 | | |
| | | 9 | | internal enum SqlServerSubscriberRole |
| | | 10 | | { |
| | | 11 | | Worker, |
| | | 12 | | ResponseIngress |
| | | 13 | | } |
| | | 14 | | |
| | | 15 | | /// <summary>A claimed SQL Server transport row, decoupled from SqlClient types for dispatch tests.</summary> |
| | | 16 | | /// <remarks> |
| | | 17 | | /// <c>RenewAsync</c> extends the claim's lease (<c>locked_until</c>) by the original lock timeout, |
| | | 18 | | /// fenced on the claim's <c>lock_id</c>; it returns <c>false</c> when the fence no longer matches |
| | | 19 | | /// (the lease lapsed and another subscriber re-claimed the row). |
| | | 20 | | /// </remarks> |
| | | 21 | | internal sealed record SqlServerTransportDelivery( |
| | | 22 | | Guid Id, |
| | | 23 | | string Queue, |
| | | 24 | | string Payload, |
| | | 25 | | IReadOnlyDictionary<string, string> Headers, |
| | | 26 | | int Attempt, |
| | | 27 | | Func<ValueTask> AckAsync, |
| | | 28 | | Func<TimeSpan, ValueTask> NakAsync, |
| | | 29 | | Func<Exception, bool, CancellationToken, ValueTask<bool>> DeadLetterAsync, |
| | | 30 | | Func<ValueTask<bool>> RenewAsync); |
| | | 31 | | |
| | | 32 | | /// <summary>Small SQL adapter for the SQL Server transport queue table.</summary> |
| | | 33 | | internal sealed class SqlServerTransportStore |
| | | 34 | | { |
| | | 35 | | // SQL Server duplicate-key error numbers: 2627 = PRIMARY KEY/UNIQUE constraint violation, |
| | | 36 | | // 2601 = unique index violation. A concurrent retry of the same idempotent publish can lose the |
| | | 37 | | // WHERE NOT EXISTS race; the duplicate is the outcome the caller asked for, not a failure. |
| | | 38 | | private const int PrimaryKeyViolation = 2627; |
| | | 39 | | private const int UniqueIndexViolation = 2601; |
| | | 40 | | |
| | | 41 | | // Interpolated into DDL: literal braces cannot appear directly inside the interpolated raw string. |
| | | 42 | | private const string EmptyJsonObject = "{}"; |
| | | 43 | | |
| | | 44 | | /// <summary> |
| | | 45 | | /// An EXACT queue-name predicate — the only kind this table can be filtered by safely, because |
| | | 46 | | /// its three logical queues share one table and are told apart by nothing but this column. |
| | | 47 | | /// <c>queue = @queue</c> alone is not exact in two independent ways: SQL Server pads the shorter |
| | | 48 | | /// operand of an equality comparison with spaces (under EVERY collation, binary ones included), |
| | | 49 | | /// so <c>'worker '</c> answers a query for <c>'worker'</c>; and on a table an older build or a |
| | | 50 | | /// hand-written migration left with the server's default collation, the comparison also folds |
| | | 51 | | /// case, accent, and width. |
| | | 52 | | /// <para> |
| | | 53 | | /// The second comparison closes both. Appending a non-blank sentinel to each side makes the |
| | | 54 | | /// last character non-blank, so the padding SQL Server may add can no longer bridge two |
| | | 55 | | /// different strings — <c>'worker .'</c> versus <c>'worker. '</c> differ at the seventh |
| | | 56 | | /// character — and the explicit collation makes the comparison ordinal whatever the column's |
| | | 57 | | /// own collation is. The first comparison is kept as the seekable driver, so the claim index is |
| | | 58 | | /// still used and this only filters the rows it returns. |
| | | 59 | | /// </para> |
| | | 60 | | /// <para> |
| | | 61 | | /// Verified on SQL Server 2022, which is also why the shape is this one and not the more |
| | | 62 | | /// obvious <c>DATALENGTH(queue) = DATALENGTH(@queue)</c>: byte counts are meaningless across |
| | | 63 | | /// types, so that form silently matches NOTHING on a <c>varchar</c> column, and pushing the |
| | | 64 | | /// explicit collation onto the driver comparison costs the index seek on a case-folding column. |
| | | 65 | | /// This form keeps an Index Seek on both, and was measured exact against <c>nvarchar</c> |
| | | 66 | | /// binary, <c>nvarchar</c> case-insensitive, and <c>varchar</c> columns alike. |
| | | 67 | | /// </para> |
| | | 68 | | /// <para> |
| | | 69 | | /// Exactness belongs HERE rather than in a post-claim re-check: a row the query returns has |
| | | 70 | | /// already been claimed, and releasing it leaves it first in line for the very next poll, which |
| | | 71 | | /// starves every valid row behind it. |
| | | 72 | | /// </para> |
| | | 73 | | /// </summary> |
| | | 74 | | private const string ExactQueueMatch = |
| | | 75 | | "queue = @queue AND queue + N'.' = @queue + N'.' COLLATE Latin1_General_100_BIN2"; |
| | | 76 | | |
| | | 77 | | private readonly string _connectionString; |
| | | 78 | | private readonly SqlServerAsyncResponseTransportOptions _options; |
| | | 79 | | private readonly ILogger<SqlServerTransportStore>? _logger; |
| | 231 | 80 | | private readonly SemaphoreSlim _ensureGate = new(1, 1); |
| | | 81 | | private bool _created; |
| | | 82 | | private long _lastDeadLetterPruneTicks; |
| | | 83 | | |
| | 231 | 84 | | public SqlServerTransportStore( |
| | 231 | 85 | | IOptions<SqlServerAsyncResponseTransportOptions> options, |
| | 231 | 86 | | ILogger<SqlServerTransportStore>? logger = null) |
| | | 87 | | { |
| | 231 | 88 | | _options = options.Value; |
| | 231 | 89 | | _logger = logger; |
| | 231 | 90 | | SqlServerTransportOptionsValidator.ValidateCommon(_options); |
| | 231 | 91 | | _connectionString = _options.ConnectionString!; |
| | 231 | 92 | | Schema = Quote(_options.SchemaName); |
| | 231 | 93 | | MessageTable = $"{Schema}.{Quote(_options.MessageTable)}"; |
| | 231 | 94 | | } |
| | | 95 | | |
| | 438 | 96 | | public string Schema { get; } |
| | 4298 | 97 | | public string MessageTable { get; } |
| | | 98 | | |
| | | 99 | | /// <summary> |
| | | 100 | | /// Raised after a row is inserted (with the logical queue name) or released for retry |
| | | 101 | | /// (<c>null</c>). Same-process subscribers use it to wake immediately instead of waiting out |
| | | 102 | | /// their empty-poll delay; SQL Server has no LISTEN/NOTIFY, so cross-process wakes rely on polling. |
| | | 103 | | /// </summary> |
| | | 104 | | public event Action<string?>? MessagePublished; |
| | | 105 | | |
| | | 106 | | public async Task EnsureCreatedAsync(CancellationToken cancellationToken = default) |
| | | 107 | | { |
| | 2653 | 108 | | if (_created) |
| | 2043 | 109 | | return; |
| | | 110 | | |
| | 610 | 111 | | await _ensureGate.WaitAsync(cancellationToken).ConfigureAwait(false); |
| | | 112 | | try |
| | | 113 | | { |
| | 610 | 114 | | if (_created) |
| | 384 | 115 | | return; |
| | | 116 | | |
| | 226 | 117 | | await using var connection = await OpenConnectionAsync(cancellationToken).ConfigureAwait(false); |
| | | 118 | | |
| | 214 | 119 | | if (!_options.AutoCreateSchema) |
| | | 120 | | { |
| | | 121 | | // Operator-managed schema: no DDL and no DDL lock, but catalog verification all the |
| | | 122 | | // same — an operator-provisioned queue table whose payload_json, headers_json, or |
| | | 123 | | // timestamp columns have the wrong shape breaks every insert or silently reorders |
| | | 124 | | // the timestamps this store compares, which is exactly what verification exists to |
| | | 125 | | // catch. An absent object is fine: the migration has not run yet, the first query |
| | | 126 | | // surfaces a clear SQL Server error (the documented "create it yourself, later" |
| | | 127 | | // workflow), and _created stays unlatched so a later operation re-verifies once the |
| | | 128 | | // migration lands. |
| | 8 | 129 | | if (!await ObjectExistsAsync(connection, cancellationToken).ConfigureAwait(false)) |
| | | 130 | | return; |
| | | 131 | | |
| | 7 | 132 | | await VerifyRelationsAsync(connection, transaction: null, selfCreated: false, cancellationToken).Configu |
| | 5 | 133 | | _created = true; |
| | 5 | 134 | | return; |
| | | 135 | | } |
| | | 136 | | |
| | 206 | 137 | | await using var transaction = (SqlTransaction)await connection.BeginTransactionAsync(cancellationToken).Conf |
| | | 138 | | |
| | | 139 | | // Serialize schema creation across processes. The IF-NOT-EXISTS guards are not atomic |
| | | 140 | | // against a concurrent create of the same object: two instances starting together both |
| | | 141 | | // pass the existence check and collide on the catalog (error 2714/2627). A |
| | | 142 | | // transaction-scoped application lock (keyed by schema, shared with the channel store) |
| | | 143 | | // lets one instance build the schema while the rest wait and then find it already present. |
| | 206 | 144 | | await using (var lockCommand = connection.CreateCommand()) |
| | | 145 | | { |
| | 206 | 146 | | lockCommand.Transaction = transaction; |
| | 206 | 147 | | lockCommand.CommandText = |
| | 206 | 148 | | """ |
| | 206 | 149 | | DECLARE @lock_result int; |
| | 206 | 150 | | EXEC @lock_result = sp_getapplock |
| | 206 | 151 | | @Resource = @lock_resource, |
| | 206 | 152 | | @LockMode = 'Exclusive', |
| | 206 | 153 | | @LockOwner = 'Transaction', |
| | 206 | 154 | | @LockTimeout = 60000; |
| | 206 | 155 | | IF @lock_result < 0 |
| | 206 | 156 | | THROW 51000, N'Failed to acquire the AsyncResponse DDL application lock.', 1; |
| | 206 | 157 | | """; |
| | 206 | 158 | | lockCommand.Parameters.AddWithValue("@lock_resource", SchemaLockResource(_options.SchemaName)); |
| | 206 | 159 | | await lockCommand.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false); |
| | | 160 | | } |
| | | 161 | | |
| | 206 | 162 | | await using var command = connection.CreateCommand(); |
| | 206 | 163 | | command.Transaction = transaction; |
| | 206 | 164 | | command.CommandText = |
| | 206 | 165 | | $""" |
| | 206 | 166 | | IF SCHEMA_ID(N'{_options.SchemaName}') IS NULL |
| | 206 | 167 | | EXEC(N'CREATE SCHEMA {Schema}'); |
| | 206 | 168 | | |
| | 206 | 169 | | IF OBJECT_ID(N'{MessageTable}', N'U') IS NULL |
| | 206 | 170 | | CREATE TABLE {MessageTable} ( |
| | 206 | 171 | | id uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED, |
| | 206 | 172 | | queue nvarchar(200) COLLATE Latin1_General_100_BIN2 NOT NULL, |
| | 206 | 173 | | payload_json nvarchar(max) NOT NULL, |
| | 206 | 174 | | headers_json nvarchar(max) NOT NULL DEFAULT N'{EmptyJsonObject}', |
| | 206 | 175 | | created_at datetime2 NOT NULL DEFAULT SYSUTCDATETIME(), |
| | 206 | 176 | | available_at datetime2 NOT NULL DEFAULT SYSUTCDATETIME(), |
| | 206 | 177 | | locked_until datetime2 NULL, |
| | 206 | 178 | | lock_id uniqueidentifier NULL, |
| | 206 | 179 | | attempts int NOT NULL DEFAULT 0, |
| | 206 | 180 | | dead_letter_reason nvarchar(max) NULL |
| | 206 | 181 | | ); |
| | 206 | 182 | | |
| | 206 | 183 | | IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'{IndexName(_options.MessageTable, "claim")}' AND |
| | 206 | 184 | | CREATE INDEX {Quote(IndexName(_options.MessageTable, "claim"))} |
| | 206 | 185 | | ON {MessageTable} (queue, available_at, locked_until, created_at); |
| | 206 | 186 | | IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'{IndexName(_options.MessageTable, "created")}' A |
| | 206 | 187 | | CREATE INDEX {Quote(IndexName(_options.MessageTable, "created"))} |
| | 206 | 188 | | ON {MessageTable} (created_at); |
| | 206 | 189 | | """; |
| | | 190 | | try |
| | | 191 | | { |
| | 206 | 192 | | await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false); |
| | 205 | 193 | | } |
| | 1 | 194 | | catch (SqlException ex) |
| | | 195 | | { |
| | | 196 | | // The batch can break BEFORE the verification below runs: a name held by another |
| | | 197 | | // component's table suppresses the guarded CREATE and the index that follows hits |
| | | 198 | | // the wrong table, and a name held by a view fails outright with error 2714. Run |
| | | 199 | | // the same catalog checks now, on a fresh connection (the objects in question are |
| | | 200 | | // somebody else's and already committed), so the operator gets the precise reason. |
| | 1 | 201 | | await SqlServerRelationVerifier.ThrowDiagnosedCollisionAsync( |
| | 1 | 202 | | OpenConnectionAsync, |
| | 1 | 203 | | ex, |
| | 1 | 204 | | _options.SchemaName, |
| | 1 | 205 | | "transport", |
| | 1 | 206 | | ExpectedObjects(selfCreated: true), |
| | 1 | 207 | | cancellationToken).ConfigureAwait(false); |
| | 0 | 208 | | throw; |
| | | 209 | | } |
| | | 210 | | |
| | | 211 | | // Post-DDL catalog verification inside the DDL transaction (and therefore under the |
| | | 212 | | // shared application lock): the existence guard above only asks "is there a user table |
| | | 213 | | // with this name", so another component's table silently suppresses creation and a |
| | | 214 | | // view or synonym makes the CREATE fail with raw error 2714. |
| | 205 | 215 | | await transaction.CommitAsync(cancellationToken).ConfigureAwait(false); |
| | | 216 | | |
| | | 217 | | // Verified AFTER the commit, on the same connection but outside the transaction. The |
| | | 218 | | // checks read the catalog, and a transaction that has just run DDL still holds |
| | | 219 | | // schema-modification locks — catalog reads under those deadlock (error 1205) against |
| | | 220 | | // this store's own live traffic, which is already polling by the time a later |
| | | 221 | | // EnsureCreated re-runs. Correctness does not need the transaction: the application |
| | | 222 | | // lock serialized the DDL, and what these checks look for is somebody ELSE'S committed |
| | | 223 | | // object occupying a name, never our own uncommitted work. |
| | 205 | 224 | | await VerifyRelationsAsync(connection, transaction: null, selfCreated: true, cancellationToken).ConfigureAwa |
| | 203 | 225 | | _created = true; |
| | 203 | 226 | | } |
| | | 227 | | finally |
| | | 228 | | { |
| | 610 | 229 | | _ensureGate.Release(); |
| | | 230 | | } |
| | 2636 | 231 | | } |
| | | 232 | | |
| | | 233 | | private Task VerifyRelationsAsync(SqlConnection connection, SqlTransaction? transaction, bool selfCreated, Cancellat |
| | 212 | 234 | | => SqlServerRelationVerifier.VerifyAsync( |
| | 212 | 235 | | connection, |
| | 212 | 236 | | transaction, |
| | 212 | 237 | | _options.SchemaName, |
| | 212 | 238 | | "transport", |
| | 212 | 239 | | ExpectedObjects(selfCreated), |
| | 212 | 240 | | cancellationToken); |
| | | 241 | | |
| | | 242 | | /// <summary> |
| | | 243 | | /// Reports whether ANY object occupies the configured queue-table name (any kind: a view or |
| | | 244 | | /// foreign component's object must reach verification, which names the precise wrong-kind |
| | | 245 | | /// reason instead of skipping the checks). The catalog's own collation decides case matching, |
| | | 246 | | /// exactly as the server resolves the runtime identifier. |
| | | 247 | | /// </summary> |
| | | 248 | | private async Task<bool> ObjectExistsAsync(SqlConnection connection, CancellationToken cancellationToken) |
| | | 249 | | { |
| | 8 | 250 | | await using var command = connection.CreateCommand(); |
| | 8 | 251 | | command.CommandText = |
| | 8 | 252 | | """ |
| | 8 | 253 | | SELECT CASE WHEN EXISTS ( |
| | 8 | 254 | | SELECT 1 |
| | 8 | 255 | | FROM sys.objects o |
| | 8 | 256 | | JOIN sys.schemas s ON s.schema_id = o.schema_id |
| | 8 | 257 | | WHERE s.name = @schema AND o.name = @table) THEN 1 ELSE 0 END; |
| | 8 | 258 | | """; |
| | 8 | 259 | | command.Parameters.AddWithValue("@schema", _options.SchemaName); |
| | 8 | 260 | | command.Parameters.AddWithValue("@table", _options.MessageTable); |
| | 8 | 261 | | return (int)(await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false))! == 1; |
| | 8 | 262 | | } |
| | | 263 | | |
| | | 264 | | /// <summary>The catalog shape this store expects — the single source for the post-DDL |
| | | 265 | | /// verification, the failed-batch diagnosis, and the operator-provisioned check.</summary> |
| | | 266 | | /// <remarks> |
| | | 267 | | /// A bare <c>datetime2</c> declaration is <c>datetime2(7)</c>; the expected types state the |
| | | 268 | | /// scale, because a reduced-scale column rounds <c>available_at</c>/<c>locked_until</c> on |
| | | 269 | | /// store — a claim lease that rounds backwards is already expired when it is written. |
| | | 270 | | /// <para> |
| | | 271 | | /// <paramref name="selfCreated"/> distinguishes a table this build's DDL created — where any |
| | | 272 | | /// drift means somebody ALTERed it, so the queue column is held to the exact declared shape — |
| | | 273 | | /// from an operator-provisioned one, where the queue column's type and collation are |
| | | 274 | | /// deliberately unconstrained: <see cref="ExactQueueMatch"/> supplies the binary collation in |
| | | 275 | | /// the query itself and its sentinel concat defeats trailing-space padding, so every logical |
| | | 276 | | /// queue is told apart exactly whatever string type the migration chose and whatever collation |
| | | 277 | | /// the column carries. Every other column keeps its expectation on both paths: a wrong |
| | | 278 | | /// <c>payload_json</c>, <c>headers_json</c>, or timestamp shape breaks inserts or reorders the |
| | | 279 | | /// timestamps this store compares no matter who created the table. |
| | | 280 | | /// </para> |
| | | 281 | | /// </remarks> |
| | | 282 | | private SqlServerRelationVerifier.ExpectedObject[] ExpectedObjects(bool selfCreated) => |
| | 229 | 283 | | [ |
| | 229 | 284 | | new(_options.MessageTable, SqlServerObjectKind.Table, |
| | 229 | 285 | | [ |
| | 229 | 286 | | new("id", "uniqueidentifier", Nullable: false), |
| | 229 | 287 | | selfCreated |
| | 229 | 288 | | ? new("queue", "nvarchar(200)", Nullable: false, RequiresBinaryCollation: true) |
| | 229 | 289 | | : new("queue", Type: null, Nullable: false), |
| | 229 | 290 | | new("payload_json", "nvarchar(max)", Nullable: false), |
| | 229 | 291 | | new("headers_json", "nvarchar(max)", Nullable: false, DefaultExpression: "(N'{}')"), |
| | 229 | 292 | | new("created_at", "datetime2(7)", Nullable: false, DefaultExpression: "(sysutcdatetime())"), |
| | 229 | 293 | | new("available_at", "datetime2(7)", Nullable: false, DefaultExpression: "(sysutcdatetime())"), |
| | 229 | 294 | | new("locked_until", "datetime2(7)", Nullable: true), |
| | 229 | 295 | | new("lock_id", "uniqueidentifier", Nullable: true), |
| | 229 | 296 | | new("attempts", "int", Nullable: false, DefaultExpression: "((0))"), |
| | 229 | 297 | | new("dead_letter_reason", "nvarchar(max)", Nullable: true) |
| | 229 | 298 | | ], |
| | 229 | 299 | | PrimaryKey: ["id"]), |
| | 229 | 300 | | // Only on the table this build created (PostgreSQL-sibling parity): the DDL's |
| | 229 | 301 | | // index guard is name-only, so a pre-existing same-name index with the WRONG |
| | 229 | 302 | | // definition silently suppressed the CREATE and cost the claim its seek. An |
| | 229 | 303 | | // operator-owned table keeps its own indexing strategy — the same philosophy as |
| | 229 | 304 | | // the unconstrained queue column — because indexes are claim performance, not |
| | 229 | 305 | | // correctness. |
| | 229 | 306 | | .. selfCreated |
| | 229 | 307 | | ? (SqlServerRelationVerifier.ExpectedObject[]) |
| | 229 | 308 | | [ |
| | 229 | 309 | | new(IndexName(_options.MessageTable, "claim"), SqlServerObjectKind.Index, |
| | 229 | 310 | | OwningTable: _options.MessageTable, KeyColumns: ["queue", "available_at", "locked_until", "c |
| | 229 | 311 | | new(IndexName(_options.MessageTable, "created"), SqlServerObjectKind.Index, |
| | 229 | 312 | | OwningTable: _options.MessageTable, KeyColumns: ["created_at"]) |
| | 229 | 313 | | ] |
| | 229 | 314 | | : [] |
| | 229 | 315 | | ]; |
| | | 316 | | |
| | | 317 | | /// <summary> |
| | | 318 | | /// Publishes a queue row. The caller supplies the id so a retried publish is idempotent |
| | | 319 | | /// (insert-if-absent) rather than inserting a duplicate job. |
| | | 320 | | /// </summary> |
| | | 321 | | public async Task PublishAsync( |
| | | 322 | | Guid id, |
| | | 323 | | string queue, |
| | | 324 | | string payload, |
| | | 325 | | IReadOnlyDictionary<string, string>? headers, |
| | | 326 | | CancellationToken cancellationToken, |
| | | 327 | | TimeSpan? delay = null) |
| | | 328 | | { |
| | 426 | 329 | | await InsertAsync(id, queue, payload, headers, deadLetterReason: null, notify: true, cancellationToken, delay).C |
| | 424 | 330 | | await PruneDeadLettersIfDueAsync(cancellationToken).ConfigureAwait(false); |
| | 424 | 331 | | } |
| | | 332 | | |
| | | 333 | | public async Task<SqlServerTransportDelivery?> TryClaimAsync(string queue, TimeSpan lockTimeout, CancellationToken c |
| | | 334 | | { |
| | 1808 | 335 | | await EnsureCreatedAsync(cancellationToken).ConfigureAwait(false); |
| | 1808 | 336 | | var lockId = Guid.NewGuid(); |
| | | 337 | | |
| | 1808 | 338 | | await using var connection = await OpenConnectionAsync(cancellationToken).ConfigureAwait(false); |
| | 1773 | 339 | | await using var command = connection.CreateCommand(); |
| | | 340 | | // READPAST skips rows other subscribers hold UPDLOCK on — SQL Server's equivalent of |
| | | 341 | | // PostgreSQL's FOR UPDATE SKIP LOCKED — so competing consumers never block on each other. |
| | 1773 | 342 | | command.CommandText = |
| | 1773 | 343 | | $""" |
| | 1773 | 344 | | WITH next AS ( |
| | 1773 | 345 | | SELECT TOP (1) id, payload_json, headers_json, attempts, locked_until, lock_id |
| | 1773 | 346 | | FROM {MessageTable} WITH (UPDLOCK, ROWLOCK, READPAST) |
| | 1773 | 347 | | WHERE {ExactQueueMatch} |
| | 1773 | 348 | | AND available_at <= SYSUTCDATETIME() |
| | 1773 | 349 | | AND (locked_until IS NULL OR locked_until <= SYSUTCDATETIME()) |
| | 1773 | 350 | | ORDER BY created_at |
| | 1773 | 351 | | ) |
| | 1773 | 352 | | UPDATE next |
| | 1773 | 353 | | SET attempts = attempts + 1, |
| | 1773 | 354 | | locked_until = {AddMilliseconds("@lock_timeout_ms")}, |
| | 1773 | 355 | | lock_id = @lock_id |
| | 1773 | 356 | | OUTPUT inserted.id, inserted.payload_json, inserted.headers_json, inserted.attempts; |
| | 1773 | 357 | | """; |
| | 1773 | 358 | | command.Parameters.AddWithValue("@queue", queue); |
| | 1773 | 359 | | command.Parameters.AddWithValue("@lock_timeout_ms", (long)lockTimeout.TotalMilliseconds); |
| | 1773 | 360 | | command.Parameters.AddWithValue("@lock_id", lockId); |
| | | 361 | | |
| | 1773 | 362 | | await using var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false); |
| | 1765 | 363 | | if (!await reader.ReadAsync(cancellationToken).ConfigureAwait(false)) |
| | 1324 | 364 | | return null; |
| | | 365 | | |
| | 427 | 366 | | var id = reader.GetGuid(0); |
| | 427 | 367 | | var payload = reader.GetString(1); |
| | 427 | 368 | | var headerJson = reader.GetString(2); |
| | 427 | 369 | | var attempt = reader.GetInt32(3); |
| | 427 | 370 | | var headers = DeserializeHeaders(headerJson); |
| | | 371 | | |
| | | 372 | | // The claim predicate matches the queue exactly (see ExactQueueMatch), so the claimed row's |
| | | 373 | | // queue IS the requested one — no post-claim re-check, and therefore no row that gets |
| | | 374 | | // claimed, rejected, and released back to the head of the same ordering on every poll. |
| | 427 | 375 | | return new SqlServerTransportDelivery( |
| | 427 | 376 | | id, |
| | 427 | 377 | | queue, |
| | 427 | 378 | | payload, |
| | 427 | 379 | | headers, |
| | 427 | 380 | | attempt, |
| | 419 | 381 | | () => AckAsync(id, lockId), |
| | 4 | 382 | | delay => NakAsync(id, lockId, delay), |
| | 4 | 383 | | (exception, deleteOriginal, token) => DeadLetterAsync(id, lockId, queue, payload, headers, exception, delete |
| | 427 | 384 | | () => RenewLeaseAsync(id, lockId, lockTimeout)); |
| | 1751 | 385 | | } |
| | | 386 | | |
| | | 387 | | public async IAsyncEnumerable<SqlServerTransportDelivery> ClaimBatchAsync( |
| | | 388 | | string queue, |
| | | 389 | | int batchSize, |
| | | 390 | | TimeSpan lockTimeout, |
| | | 391 | | [EnumeratorCancellation] CancellationToken cancellationToken) |
| | | 392 | | { |
| | 3580 | 393 | | for (var i = 0; i < batchSize; i++) |
| | | 394 | | { |
| | 1788 | 395 | | var delivery = await TryClaimAsync(queue, lockTimeout, cancellationToken).ConfigureAwait(false); |
| | 1731 | 396 | | if (delivery is null) |
| | 1316 | 397 | | yield break; |
| | 415 | 398 | | yield return delivery; |
| | | 399 | | } |
| | 1318 | 400 | | } |
| | | 401 | | |
| | | 402 | | private async Task InsertAsync( |
| | | 403 | | Guid id, |
| | | 404 | | string queue, |
| | | 405 | | string payload, |
| | | 406 | | IReadOnlyDictionary<string, string>? headers, |
| | | 407 | | string? deadLetterReason, |
| | | 408 | | bool notify, |
| | | 409 | | CancellationToken cancellationToken, |
| | | 410 | | TimeSpan? delay = null) |
| | | 411 | | { |
| | 428 | 412 | | await EnsureCreatedAsync(cancellationToken).ConfigureAwait(false); |
| | 424 | 413 | | await using var connection = await OpenConnectionAsync(cancellationToken).ConfigureAwait(false); |
| | 424 | 414 | | await using var command = connection.CreateCommand(); |
| | | 415 | | // Insert-if-absent keeps a retried publish idempotent. The UPDLOCK/HOLDLOCK hints make the |
| | | 416 | | // existence check and the insert atomic; a concurrent same-id insert that still slips through |
| | | 417 | | // surfaces as a duplicate-key error, which is treated as success below. |
| | | 418 | | // Native delayed delivery: available_at gates the claim query, computed on the DATABASE |
| | | 419 | | // clock (SYSUTCDATETIME + delay) so client clock skew cannot shift the due time. |
| | 424 | 420 | | command.CommandText = |
| | 424 | 421 | | delay is null |
| | 424 | 422 | | ? $""" |
| | 424 | 423 | | INSERT INTO {MessageTable} (id, queue, payload_json, headers_json, dead_letter_reason) |
| | 424 | 424 | | SELECT @id, @queue, @payload_json, @headers_json, @dead_letter_reason |
| | 424 | 425 | | WHERE NOT EXISTS (SELECT 1 FROM {MessageTable} WITH (UPDLOCK, HOLDLOCK) WHERE id = @id); |
| | 424 | 426 | | """ |
| | 424 | 427 | | : $""" |
| | 424 | 428 | | INSERT INTO {MessageTable} (id, queue, payload_json, headers_json, dead_letter_reason, available_at) |
| | 424 | 429 | | SELECT @id, @queue, @payload_json, @headers_json, @dead_letter_reason, {AddMilliseconds("@available_de |
| | 424 | 430 | | WHERE NOT EXISTS (SELECT 1 FROM {MessageTable} WITH (UPDLOCK, HOLDLOCK) WHERE id = @id); |
| | 424 | 431 | | """; |
| | 424 | 432 | | command.Parameters.AddWithValue("@id", id); |
| | 424 | 433 | | command.Parameters.AddWithValue("@queue", queue); |
| | 424 | 434 | | command.Parameters.AddWithValue("@payload_json", payload); |
| | 424 | 435 | | command.Parameters.AddWithValue("@headers_json", AsyncResponseJson.Serialize(headers ?? EmptyHeaders)); |
| | 424 | 436 | | command.Parameters.AddWithValue("@dead_letter_reason", (object?)deadLetterReason ?? DBNull.Value); |
| | 424 | 437 | | if (delay is { } pending) |
| | 1 | 438 | | command.Parameters.AddWithValue("@available_delay_ms", (long)pending.TotalMilliseconds); |
| | | 439 | | |
| | | 440 | | try |
| | | 441 | | { |
| | 424 | 442 | | await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false); |
| | 424 | 443 | | } |
| | 0 | 444 | | catch (SqlException ex) when (ex.Number is PrimaryKeyViolation or UniqueIndexViolation) |
| | | 445 | | { |
| | 0 | 446 | | } |
| | | 447 | | |
| | 424 | 448 | | if (notify) |
| | 424 | 449 | | MessagePublished?.Invoke(queue); |
| | 424 | 450 | | } |
| | | 451 | | |
| | | 452 | | private async ValueTask AckAsync(Guid id, Guid lockId) |
| | | 453 | | { |
| | 420 | 454 | | await using var connection = await OpenConnectionAsync(CancellationToken.None).ConfigureAwait(false); |
| | 420 | 455 | | await using var command = connection.CreateCommand(); |
| | 420 | 456 | | command.CommandText = $"DELETE FROM {MessageTable} WHERE id = @id AND lock_id = @lock_id;"; |
| | 420 | 457 | | command.Parameters.AddWithValue("@id", id); |
| | 420 | 458 | | command.Parameters.AddWithValue("@lock_id", lockId); |
| | 420 | 459 | | await command.ExecuteNonQueryAsync(CancellationToken.None).ConfigureAwait(false); |
| | 420 | 460 | | } |
| | | 461 | | |
| | | 462 | | private async ValueTask<bool> RenewLeaseAsync(Guid id, Guid lockId, TimeSpan lockTimeout) |
| | | 463 | | { |
| | 0 | 464 | | await using var connection = await OpenConnectionAsync(CancellationToken.None).ConfigureAwait(false); |
| | 0 | 465 | | await using var command = connection.CreateCommand(); |
| | 0 | 466 | | command.CommandText = |
| | 0 | 467 | | $""" |
| | 0 | 468 | | UPDATE {MessageTable} |
| | 0 | 469 | | SET locked_until = {AddMilliseconds("@lock_timeout_ms")} |
| | 0 | 470 | | WHERE id = @id AND lock_id = @lock_id; |
| | 0 | 471 | | """; |
| | 0 | 472 | | command.Parameters.AddWithValue("@id", id); |
| | 0 | 473 | | command.Parameters.AddWithValue("@lock_id", lockId); |
| | 0 | 474 | | command.Parameters.AddWithValue("@lock_timeout_ms", (long)lockTimeout.TotalMilliseconds); |
| | 0 | 475 | | return await command.ExecuteNonQueryAsync(CancellationToken.None).ConfigureAwait(false) > 0; |
| | 0 | 476 | | } |
| | | 477 | | |
| | | 478 | | private async ValueTask NakAsync(Guid id, Guid lockId, TimeSpan delay) |
| | | 479 | | { |
| | 4 | 480 | | await using var connection = await OpenConnectionAsync(CancellationToken.None).ConfigureAwait(false); |
| | 4 | 481 | | await using var command = connection.CreateCommand(); |
| | 4 | 482 | | command.CommandText = |
| | 4 | 483 | | $""" |
| | 4 | 484 | | UPDATE {MessageTable} |
| | 4 | 485 | | SET available_at = {AddMilliseconds("@delay_ms")}, |
| | 4 | 486 | | locked_until = NULL, |
| | 4 | 487 | | lock_id = NULL |
| | 4 | 488 | | WHERE id = @id AND lock_id = @lock_id; |
| | 4 | 489 | | """; |
| | 4 | 490 | | command.Parameters.AddWithValue("@id", id); |
| | 4 | 491 | | command.Parameters.AddWithValue("@lock_id", lockId); |
| | 4 | 492 | | command.Parameters.AddWithValue("@delay_ms", (long)delay.TotalMilliseconds); |
| | 4 | 493 | | await command.ExecuteNonQueryAsync(CancellationToken.None).ConfigureAwait(false); |
| | 4 | 494 | | MessagePublished?.Invoke(null); |
| | 4 | 495 | | } |
| | | 496 | | |
| | | 497 | | private async ValueTask<bool> DeadLetterAsync( |
| | | 498 | | Guid id, |
| | | 499 | | Guid lockId, |
| | | 500 | | string sourceQueue, |
| | | 501 | | string payload, |
| | | 502 | | IReadOnlyDictionary<string, string> headers, |
| | | 503 | | Exception exception, |
| | | 504 | | bool deleteOriginal, |
| | | 505 | | CancellationToken cancellationToken) |
| | | 506 | | { |
| | 8 | 507 | | if (!_options.DeadLetterEnabled) |
| | | 508 | | { |
| | 1 | 509 | | if (deleteOriginal) |
| | 1 | 510 | | await AckAsync(id, lockId).ConfigureAwait(false); |
| | 1 | 511 | | return true; |
| | | 512 | | } |
| | | 513 | | |
| | 7 | 514 | | var deadHeaders = new Dictionary<string, string>(headers, StringComparer.OrdinalIgnoreCase) |
| | 7 | 515 | | { |
| | 7 | 516 | | ["AR-DeadLetter-Reason"] = Sanitize(exception.Message), |
| | 7 | 517 | | ["AR-DeadLetter-Source-Queue"] = sourceQueue |
| | 7 | 518 | | }; |
| | | 519 | | |
| | | 520 | | try |
| | | 521 | | { |
| | 7 | 522 | | if (!deleteOriginal) |
| | | 523 | | { |
| | 2 | 524 | | await InsertAsync(Guid.NewGuid(), _options.DeadLetterQueue, payload, deadHeaders, exception.Message, not |
| | 0 | 525 | | return true; |
| | | 526 | | } |
| | | 527 | | |
| | | 528 | | // The DLQ insert and the original-row delete must commit atomically: split across two |
| | | 529 | | // connections, a crash between them leaves the original row to be redelivered and |
| | | 530 | | // dead-lettered again, duplicating the DLQ entry. |
| | 5 | 531 | | await EnsureCreatedAsync(cancellationToken).ConfigureAwait(false); |
| | 3 | 532 | | await using var connection = await OpenConnectionAsync(cancellationToken).ConfigureAwait(false); |
| | 3 | 533 | | await using var transaction = (SqlTransaction)await connection.BeginTransactionAsync(cancellationToken).Conf |
| | 3 | 534 | | await using var command = connection.CreateCommand(); |
| | 3 | 535 | | command.Transaction = transaction; |
| | | 536 | | // Delete FIRST and write the DLQ row only if the fence matched. A stale claim (the lease |
| | | 537 | | // lapsed and a peer re-claimed the row) must no-op here exactly as the fenced ack and |
| | | 538 | | // NAK do; writing the row unconditionally buried a full copy of a message that is still |
| | | 539 | | // live and may yet succeed under its new owner, so the DLQ showed a poison entry for |
| | | 540 | | // work that completed — and an operator replaying it duplicated its side effects. |
| | 3 | 541 | | command.CommandText = |
| | 3 | 542 | | $""" |
| | 3 | 543 | | SET NOCOUNT ON; |
| | 3 | 544 | | DELETE FROM {MessageTable} WHERE id = @source_id AND lock_id = @lock_id; |
| | 3 | 545 | | IF @@ROWCOUNT = 1 |
| | 3 | 546 | | BEGIN |
| | 3 | 547 | | INSERT INTO {MessageTable} (id, queue, payload_json, headers_json, dead_letter_reason) |
| | 3 | 548 | | VALUES (@id, @queue, @payload_json, @headers_json, @dead_letter_reason); |
| | 3 | 549 | | SELECT 1; |
| | 3 | 550 | | END |
| | 3 | 551 | | ELSE |
| | 3 | 552 | | SELECT 0; |
| | 3 | 553 | | """; |
| | 3 | 554 | | command.Parameters.AddWithValue("@id", Guid.NewGuid()); |
| | 3 | 555 | | command.Parameters.AddWithValue("@queue", _options.DeadLetterQueue); |
| | 3 | 556 | | command.Parameters.AddWithValue("@payload_json", payload); |
| | 3 | 557 | | command.Parameters.AddWithValue("@headers_json", AsyncResponseJson.Serialize(deadHeaders)); |
| | 3 | 558 | | command.Parameters.AddWithValue("@dead_letter_reason", exception.Message); |
| | 3 | 559 | | command.Parameters.AddWithValue("@source_id", id); |
| | 3 | 560 | | command.Parameters.AddWithValue("@lock_id", lockId); |
| | 3 | 561 | | var buried = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false) is int and 1; |
| | 3 | 562 | | await transaction.CommitAsync(cancellationToken).ConfigureAwait(false); |
| | | 563 | | |
| | | 564 | | // Zero means the fence was lost, not that the write failed. Report it as a |
| | | 565 | | // non-dead-letter so the caller does not log a burial that did not happen; its NAK |
| | | 566 | | // fallback is fenced too, so the new owner keeps the row untouched. |
| | 3 | 567 | | if (!buried) |
| | | 568 | | { |
| | 1 | 569 | | _logger?.LogWarning( |
| | 1 | 570 | | "SQL Server dead-letter for message {MessageId} from queue {SourceQueue} no-opped: the claim's lease |
| | 1 | 571 | | id, |
| | 1 | 572 | | sourceQueue); |
| | 1 | 573 | | return false; |
| | | 574 | | } |
| | | 575 | | |
| | 2 | 576 | | return true; |
| | 0 | 577 | | } |
| | 4 | 578 | | catch (Exception ex) |
| | | 579 | | { |
| | | 580 | | // Callers decide the redelivery consequence from the false return; log the cause here so |
| | | 581 | | // a failing dead-letter write is never silent. |
| | 4 | 582 | | _logger?.LogError( |
| | 4 | 583 | | ex, |
| | 4 | 584 | | "Failed to write SQL Server dead-letter row for message {MessageId} from queue {SourceQueue}.", |
| | 4 | 585 | | id, |
| | 4 | 586 | | sourceQueue); |
| | 4 | 587 | | return false; |
| | | 588 | | } |
| | 8 | 589 | | } |
| | | 590 | | |
| | | 591 | | /// <summary> |
| | | 592 | | /// Opportunistically deletes dead-letter rows older than the configured retention. No-op unless |
| | | 593 | | /// <see cref="SqlServerAsyncResponseTransportOptions.DeadLetterRetention"/> is set, and throttled |
| | | 594 | | /// so the DELETE runs at most once per minute regardless of publish rate. |
| | | 595 | | /// </summary> |
| | | 596 | | private async Task PruneDeadLettersIfDueAsync(CancellationToken cancellationToken) |
| | | 597 | | { |
| | 424 | 598 | | if (_options.DeadLetterRetention is not { } retention || !ShouldPruneDeadLetters()) |
| | 417 | 599 | | return; |
| | | 600 | | |
| | | 601 | | // Bounded batch (SQL Server channel parity): the dead-letter rows share the queue table |
| | | 602 | | // with live claims, and an unbounded DELETE over a backlog past SQL Server's ~5,000-lock |
| | | 603 | | // escalation threshold takes a table X lock that READPAST cannot skip — every claim, ACK |
| | | 604 | | // and lease renewal blocked behind it for up to the command timeout, which is the whole |
| | | 605 | | // LockTimeout, so a live handler's lease lapsed and a peer re-ran its job concurrently. |
| | | 606 | | // Any backlog beyond the batch waits for the next throttle window. |
| | 7 | 607 | | await using var connection = await OpenConnectionAsync(cancellationToken).ConfigureAwait(false); |
| | 7 | 608 | | await using var command = connection.CreateCommand(); |
| | 7 | 609 | | command.CommandText = $"DELETE TOP ({DeadLetterPruneBatchSize}) FROM {MessageTable} WHERE {ExactQueueMatch} AND |
| | 7 | 610 | | command.Parameters.AddWithValue("@queue", _options.DeadLetterQueue); |
| | 7 | 611 | | command.Parameters.AddWithValue("@negative_retention_ms", -(long)retention.TotalMilliseconds); |
| | 7 | 612 | | await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false); |
| | 424 | 613 | | } |
| | | 614 | | |
| | | 615 | | /// <summary>Rows per prune statement; well under the ~5,000-lock escalation threshold.</summary> |
| | | 616 | | private const int DeadLetterPruneBatchSize = 1000; |
| | | 617 | | |
| | | 618 | | private bool ShouldPruneDeadLetters() |
| | | 619 | | { |
| | 17 | 620 | | var now = DateTime.UtcNow.Ticks; |
| | 17 | 621 | | var last = Interlocked.Read(ref _lastDeadLetterPruneTicks); |
| | 17 | 622 | | return now - last >= DeadLetterPruneThrottle.Ticks |
| | 17 | 623 | | && Interlocked.CompareExchange(ref _lastDeadLetterPruneTicks, now, last) == last; |
| | | 624 | | } |
| | | 625 | | |
| | | 626 | | private async Task<SqlConnection> OpenConnectionAsync(CancellationToken cancellationToken) |
| | | 627 | | { |
| | 2893 | 628 | | var connection = new SqlConnection(_connectionString); |
| | | 629 | | try |
| | | 630 | | { |
| | 2893 | 631 | | await connection.OpenAsync(cancellationToken).ConfigureAwait(false); |
| | 2846 | 632 | | return connection; |
| | | 633 | | } |
| | 47 | 634 | | catch |
| | | 635 | | { |
| | 47 | 636 | | await connection.DisposeAsync().ConfigureAwait(false); |
| | 47 | 637 | | throw; |
| | | 638 | | } |
| | 2846 | 639 | | } |
| | | 640 | | |
| | | 641 | | // Lenient by contract (see DbTransportHeaders): this runs after the claim already committed |
| | | 642 | | // attempts+1/lock_id, so rejecting any content the nvarchar column legally holds would create |
| | | 643 | | // an unkillable poison row. |
| | | 644 | | private static IReadOnlyDictionary<string, string> DeserializeHeaders(string json) |
| | 439 | 645 | | => DbTransportHeaders.Materialize(json); |
| | | 646 | | |
| | 7 | 647 | | private static string Sanitize(string value) => value.Replace('\r', ' ').Replace('\n', ' '); |
| | | 648 | | |
| | | 649 | | /// <summary> |
| | | 650 | | /// SQL expression adding a millisecond bigint parameter to the database clock. DATEADD only takes |
| | | 651 | | /// int arguments, so the value is split into whole seconds and a sub-second remainder — intervals |
| | | 652 | | /// (lock timeouts, redelivery delays, retentions) stay on the database clock, immune to app-side |
| | | 653 | | /// clock skew, without overflowing on long spans. |
| | | 654 | | /// </summary> |
| | | 655 | | internal static string AddMilliseconds(string parameterName) |
| | 1787 | 656 | | => $"DATEADD(SECOND, CAST({parameterName} / 1000 AS int), DATEADD(MILLISECOND, CAST({parameterName} % 1000 AS in |
| | | 657 | | |
| | | 658 | | /// <summary> |
| | | 659 | | /// Stable application-lock resource for serializing schema creation. Must be byte-for-byte |
| | | 660 | | /// identical to the channel store's resource so that, for a shared schema, the channel and |
| | | 661 | | /// transport take the same lock and never race each other on CREATE SCHEMA. |
| | | 662 | | /// </summary> |
| | | 663 | | internal static string SchemaLockResource(string schemaName) |
| | 213 | 664 | | => $"asyncresponse:ddl:{schemaName}"; |
| | | 665 | | |
| | 874 | 666 | | private static string Quote(string identifier) => "[" + identifier + "]"; |
| | | 667 | | |
| | | 668 | | // Suffix space is RESERVED before capping; see RelationalNamePlan.DerivedName for why and for |
| | | 669 | | // the single implementation this and the PostgreSQL / channel stores all share. |
| | | 670 | | internal static string IndexName(string table, string suffix) |
| | 1256 | 671 | | => RelationalNamePlan.DerivedName(table, $"_{suffix}_idx", identifierCap: 128); |
| | | 672 | | |
| | 6 | 673 | | private static readonly TimeSpan DeadLetterPruneThrottle = TimeSpan.FromMinutes(1); |
| | | 674 | | |
| | 6 | 675 | | private static readonly IReadOnlyDictionary<string, string> EmptyHeaders = |
| | 6 | 676 | | new Dictionary<string, string>(0, StringComparer.OrdinalIgnoreCase); |
| | | 677 | | } |