Using higher SQL transaction isolation level

1

Hi

We are using AWS SQS transport with SQL persistence.

NServiceBus - 7.8.2
NServiceBus.Persistence.Sql - 6.6.2
NServiceBus.AmazonSQS - 5.6.3

As per your documentation, whenever an NServiceBus’ Handler receives an event from other services, the Outbox (SQL) transaction starts in isolation level = ReadCommitted.
Under this same transaction, we save the application’s data in the database - assuming that Outbox would commit the transaction in the end.

Could we increase the Outbox transaction’s isolation level = Serializable?

Our Accounts-Service receives hundreds of OrderCancelled events from the Order-Service. As a result we have to update the user’s account balances by refunding the order amounts.

The problem that we’re having is that sometimes a user has lots of orders that are cancelled at the same time. So we receive lots of OrderCancelled events for the same user’s account. Our Accounts-Service is usually scaled up to many tasks so these events are processed concurrently by multiple Accounts-Service tasks/threads.

Since the isolation level = ReadCommitted, the account balance in the Accounts table is not updated correctly due to the events processed for the same account. Here is an example for Account ID = 1

Let’s say Account ID = 1 has a balance of $5.00 and we receive the events concurrently:

  1. Received OrderCancelled event (refund $2.00) - Thread 1 - reads $5.00 balance and updates the balance 5 + 2 = $7.00
  2. Received OrderCancelled event (refund $3.00) - Thread 2 - reads $5.00 balance (instead of $7) and updates 5 + 3 = $8.00.

However the order refunds of $2 + $3 should have updated the balance to 5 + 2 + 3 = $10.00

How can we upgrade the Transaction Isolation level here using NServiceBus’ Outbox transaction?

Thanks
-O

2

More on this. Will it change the Outbox transaction’s isolation level to Serializable with the following code:

var outboxSettings = endpointConfiguration.EnableOutbox();
outboxSettings.TransactionIsolationLevel(System.Data.IsolationLevel.Serializable);

Thanks

3

Hi Omair

Yes, as mentioned in our docs, you can change the transaction isolation level. Our outbox supports the levels “ReadCommitted”, “RepeatableRead” and “Serializable”. Changing the isolation level to serializable places a range lock on the DataSet. This prevents other users from updating or inserting rows into the dataset until the transaction is complete.
Hope this helps.

Thanks
Jayanthi
Particular Support Team

1 Like