SQLAlchemy optimistic locking is not scoped to the current tenant #117

The SQLAlchemy optimistic-lock update filters by stream ID and version, but not by tenant ID.

Two tenants can own streams with the same StreamId and the same current version. An append with expected_version from one tenant then updates both stream rows. Since two rows are affected, the backend raises ConcurrentStreamWriteError, but it has already advanced the version of the other tenant’s stream.

Reproduction

1. Create the same StreamId for tenants first and second.
2. Append one versioned event to each tenant’s stream. Both are now at version 1.
3. Append an event to tenant first with expected_version=1.
4. Observe:
   • the call raises ConcurrentStreamWriteError because the update affects two rows;
   • tenant second’s stream version was changed despite not being written to.

This reproduces in both SQLAlchemyBackend and AsyncSQLAlchemyBackend.

Affected code

• event_sourcery_sqlalchemy/event_store.py
• event_sourcery_sqlalchemy/async_/event_store.py

Current predicate:

.where(
    self._stream_model.stream_id == stream_id,
    self._stream_model.version == versioning.expected_version,
)

Proposed fix

Scope the update to the configured tenant:

.where(
    self._stream_model.stream_id == stream_id,
    self._stream_model.tenant_id == self._tenant_id,
    self._stream_model.version == versioning.expected_version,
)

Regression test

For both sync and async SQLAlchemy:

1. Create the same stream in two tenants.
2. Append version 1 in each.
3. Append version 2 only in the first tenant with expected_version=1.
4. Assert the first tenant has versions [1, 2].
5. Assert the second tenant remains at [1].