Validate if snapshot isolation is enabled when outbox is launched with pessimistic concurrency enabled

[ramonsmits]

Describe the feature.

Validate if snapshot isolation is enabled when outbox is launched with pessimistic concurrency enabled.

When snapshot isolation is enabled queries are not able to use pessimistic concurrency. The endpoint should not launch when it detects that snapshot isolation is enabled.

For example, with SQL Server:

SELECT snapshot_isolation_state_desc
FROM sys.databases 
WHERE name=DB_NAME()

Sources:

• https://stackoverflow.com/questions/25429484/verify-if-snapshot-isolation-level-is-on-in-sql-server-2008-r2
• https://learn.microsoft.com/en-us/sql/t-sql/functions/db-name-transact-sql?view=sql-server-ver16

For SQL Server

Only querying a specific database might not be sufficient but its important that the catalog that stored the outbox data does not use snapshot isolation.

Additional Context

No response

[bbrandt]

its important that the catalog that stored the outbox data does not use snapshot isolation

@ramonsmits Do you have any details or background for why these options are not compatible?

We commonly set snapshot isolation as our default isolation mode and we are looking at starting to use the NSB Outbox and prefer pessimistic concurrency to support high throughput.

[ramonsmits]

Snapshot isolation is optimistic concurrency control. Changes are applied at commit and not earlier with locking.

[bbrandt]

Thanks! I am realizing today that I knew less about snapshot isolation than I had thought. This issue is specifically targeted at the Outbox Pessimistic locking enabled like this:

var outboxSettings = endpointConfiguration.EnableOutbox();
outboxSettings.UsePessimisticConcurrencyControl();

Right?

[ramonsmits]

@bbrandt Yes, with UsePessimisticConcurrencyControl we do an INSERT just when the message is received but with snapshot isolation enabled on the database even though that INSERT is already sent to the database the actual modification will not happen immediately only once the database transaction is committed.

Snapsnop isolation is great for not locking data and reduce page/table locks because of this but actually not great when with block contention as it will increase conflicts.

[andreasohlund]

UPDLOCK can be used

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server#understanding-snapshot-isolation-and-row-versioning

Snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. You can avoid this by using UPDLOCK hints for SELECT statements that access data to be modified.

@SzymonPobiega @tmasternak we tested this recently right?