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.