SQL Server: fill a gap in GlobalPosition with a tombstone (GapHandlingTimeoutMs)

Start by reading SqlSubscriptionBase.HandleGapTimeout, SqlServerSubscriptionBase, and the PostgreSQL implementation that calls 9_TryInsertTombstone.sql; then inspect the SQL Server schema procedures and subscription tests. Add SQL Server tombstone insertion using GapHandlingTimeoutMs and ensure tombstones are skipped. Done means a rolled-back identity gap is filled without skipping a slow committed append, and the subscription resumes.

Summary

On SQL Server, a rolled back append leaves a gap in GlobalPosition, and every all-stream subscription stops at that gap. The subscription waits until the event after the gap is older than GapAgeThresholdMs (one hour by default). GapHandlingTimeoutMs does not help on SQL Server, because only the PostgreSQL provider writes tombstones. This issue asks for tombstones on SQL Server too, with the same GapHandlingTimeoutMs option.

Problem

GlobalPosition is an IDENTITY column. SQL Server gives an identity value to an insert when the insert runs, and it does not give the value back when the transaction rolls back. These failures roll back after the insert, so each one leaves a gap:

• An append that breaks UQ_StreamIdAndMessageId, for example an event that is written again with the same message ID.
• Two appends to the same stream that read the same version in check_stream (#597, fixed by #598 for appends to an existing stream).
• A multi-stream append (IEventWriter.AppendEvents with many NewStreamAppend) where a later stream fails after an earlier stream inserted its rows.

The subscription cannot tell a rolled back insert from an append that did not commit yet, so it holds the gap. That is correct, because a skip could lose an event. But the options to release the gap on SQL Server have these problems:

Option	Result on SQL Server
GapAgeThresholdMs (default one hour)	Every all-stream subscription stops for up to one hour.
GapSkipTimeoutMs	The gap is released sooner. An append that takes longer than the timeout to commit is skipped, and its events never reach the subscription.
GapHandlingTimeoutMs	No effect. SqlSubscriptionBase.HandleGapTimeout does nothing, and only PostgresSubscriptionBase overrides it.

Reproduction

Eventuous 0.17.0, SQL Server 2022, a SqlServerAllStreamSubscription with the default options:

1. Append an event with message ID X to stream A, and wait until the subscription handles it.
2. Append another event with message ID X to stream A, with the correct expected version. The append fails with error 2627 on UQ_StreamIdAndMessageId, and it uses one identity value.
3. Append an event to stream B.

The subscription does not handle the event of stream B within 30 seconds. When step 2 fails with a wrong expected version instead (before any insert), the subscription handles the event of stream B within about 60 ms.

Proposal

Do for SQL Server what the PostgreSQL provider does in 9_TryInsertTombstone.sql with OVERRIDING SYSTEM VALUE. SQL Server writes an explicit identity value with SET IDENTITY_INSERT ... ON.

1. Add a try_insert_tombstone procedure to the SQL Server schema. It inserts a tombstone row at the gap position, in a tombstone stream.
2. Override HandleGapTimeout in SqlServerSubscriptionBase to call the procedure.
3. Override ShouldSkipEvent in SqlServerSubscriptionBase to skip the tombstone rows, as the PostgreSQL provider does.

DetectGap already gives remediation its chance before GapSkipTimeoutMs skips a gap, so no change to the shared polling code is necessary.

A sketch of the procedure (not tested):

CREATE OR ALTER PROCEDURE __schema__.try_insert_tombstone
    @gap_position BIGINT,
    @stream_name  NVARCHAR(850),
    @message_type NVARCHAR(128),
    @message_id   UNIQUEIDENTIFIER
WITH EXECUTE AS OWNER /* SET IDENTITY_INSERT needs ALTER permission on the table. */
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SET LOCK_TIMEOUT 5000; /* Do not hold the polling loop while an open append holds the position. */

    DECLARE @stream_id INT, @stream_position INT;

    BEGIN TRY
        BEGIN TRANSACTION;

        /* The tombstone stream can also be created once by the schema script. */
        IF NOT EXISTS (SELECT 1 FROM __schema__.Streams WHERE StreamName = @stream_name)
            INSERT INTO __schema__.Streams (StreamName, [Version]) VALUES (@stream_name, -1);

        UPDATE __schema__.Streams
        SET @stream_position = [Version] = [Version] + 1,
            @stream_id = StreamId
        WHERE StreamName = @stream_name;

        SET IDENTITY_INSERT __schema__.Messages ON;

        INSERT INTO __schema__.Messages (
            GlobalPosition, MessageId, MessageType, StreamId, StreamPosition, JsonData, JsonMetadata, Created
        )
        VALUES (
            @gap_position, @message_id, @message_type, @stream_id, @stream_position, N'{}', N'{}', SYSUTCDATETIME()
        );

        SET IDENTITY_INSERT __schema__.Messages OFF;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        SET IDENTITY_INSERT __schema__.Messages OFF;
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        /*
            2627, 2601: an append committed at the position, so the gap is not a gap.
            1222: an open append still holds the position. The next poll tries again.
        */
        IF ERROR_NUMBER() NOT IN (2627, 2601, 1222) THROW;
    END CATCH;

    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END;

Why a tombstone does not take the position of a slow append

While an append is open, its uncommitted row holds an exclusive lock on its key in the clustered primary key. A tombstone insert at the same position must check the primary key, so it waits for that lock. The append then decides the result:

• When the append commits, the tombstone fails with a duplicate key, and the event stays.
• When the append rolls back, the tombstone fills the position.

I tested these four cases on SQL Server 2022 with a copy of the 0.17.0 Messages table (the same identity column, primary key, and unique constraints, with READ_COMMITTED_SNAPSHOT off), with SET IDENTITY_INSERT ON and SET LOCK_TIMEOUT 3000:

Case	Result of the tombstone insert
The append is open	Waits, then fails with 1222 (lock timeout)
The append rolled back	Succeeds. IDENT_CURRENT does not change.
The append committed	Fails with 2627. The event stays.
The append commits while the tombstone waits	Fails with 2627. The event stays.

So unlike GapSkipTimeoutMs, a tombstone cannot lose an event.

Open questions

• Permissions. SET IDENTITY_INSERT needs ALTER permission on the table, or ownership of the table. The sketch uses EXECUTE AS OWNER. Another way is a note in the docs that the subscription login needs ALTER permission on Messages.
• One table per session. Only one table in a session can have IDENTITY_INSERT on. The procedure turns it off in both paths, so a pooled connection does not keep it on.
• Read committed snapshot. I did not test with READ_COMMITTED_SNAPSHOT on. I expect the same result, because a uniqueness check takes locks and does not read row versions.
• Shared constants. The tombstone stream name and message type (__tombstones__, $tombstone) are in PostgresSubscriptionConstants. They could move to Eventuous.Sql.Base, so both providers use the same names.