SQL persistence: head truncation breaks optimistic concurrency for aggregates

[nmummau]

Environment

• Eventuous v0.15.1
• Using SqlServer as the EventStore

Description

When using the SQL Server persistence layer, if you truncate events from the head of a stream (e.g., via a truncate_stream stored procedure that deletes Messages with StreamPosition < @position), optimistic concurrency for aggregates breaks.

Eventuous calculates the aggregate’s expected version from the number of events it folds (e.g., events.Length - 1 or similar). The SQL layer, however, compares this expected version against Streams.[Version], which tracks the last assigned StreamPosition. After head truncation, these no longer match, and subsequent appends fail with WrongExpectedVersion even though the data is consistent.

Repro steps (simplified)

1. Start with a clean SQL store using the standard Eventuous schema.

2. Append a 4 events into a stream

   • StreamPosition = 0, 1, 2, 3
   • Streams.[Version] ends up being 3 for this example

3. Truncate the head of the stream at some position p using a stored procedure that:

   • Deletes Messages where StreamPosition < @position.
   • Does not update Streams.[Version].
   • Example state after truncation:
     • Streams row: StreamName = 'ItemInventory-2', Version = 1
     • Messages for that StreamId: a single row: StreamPosition = 1.

4. Now let Eventuous load the aggregate and append another event via normal CommandService flow:

   • It reads one event
   • It sets the aggregate’s Version based on “how many events I saw”
   • It calls check_stream via append_events

5. The SQL check_stream procedure is called and throws an exception.

6. From the application side, this surfaces as an optimistic concurrency exception like:

   Update of ItemInventory failed due to the wrong version in stream ItemInventory-2

Why this happens

• Streams.[Version] is modeled as “last assigned StreamPosition”, not “number of events currently present”.
• Head truncation removes early events but does not (and cannot safely) change existing StreamPosition values or
  Streams.[Version].
• Eventuous derives expected version from the aggregate’s folded event count, which sees only the remaining events.
• After truncation:
  • DB “physical” version: Streams.[Version] = last StreamPosition (e.g., 1).
  • Aggregate “logical” version: events.Length - 1 based on remaining events (e.g., 0).
• check_stream only accepts expected_version == Streams.[Version] (or -2 for Any), so the aggregate’s logical version is rejected even though the stream is internally consistent.

Why it matters

• Head truncation of aggregate streams is a common requirement when paired with snapshots (snapshot + tail) and cold storage.
• As things stand, deleting from the head of a stream breaks Eventuous’ optimistic concurrency semantics with the SQL store.

One possible fix (SQL-side)

In check_stream, for existing streams, allow an expected version that matches either:

• The physical version (Streams.[Version]), or
• A logical version computed from the remaining events:

  DECLARE @min_position INT;

  ...

  IF @stream_id IS NULL
      -- existing behavior for new streams...
  END;
  ELSE
  BEGIN
      -- Allow for streams that have been head-truncated.
      SELECT @min_position = MIN(StreamPosition)
      FROM eventStore.Messages
      WHERE StreamId = @stream_id;

      IF @min_position IS NULL
      BEGIN
          SET @min_position = 0;
      END;

      IF @expected_version != -2
         AND @expected_version != @current_version
         AND @expected_version != (@current_version - @min_position)
      BEGIN
          SELECT @customErrorMessage = FORMATMESSAGE(
              N'WrongExpectedVersion %i, current version %i',
              @expected_version, @current_version);
          THROW 50000, @customErrorMessage, 1;
      END;
  END;

This keeps the existing semantics for non‑truncated streams, while allowing aggregates that base their version on the number of remaining events to continue working after head truncation.

[linear]

EVE-210