Multiple db contexts in retryable transaction

[dazinator]

Type of issue

Missing information

Description

Suppose my database has different schema's and I have seperate DbContext's for those schemas.

Given the retry policy / execution strategy is configured per DB context, how should I
Execute a "resilient transaction" that spans multiple Db Contexts?

Also given people have standardised on using polly for resiliency - can it be mentioned why polly isn't used here? Or is there a way to use polly for this use case instead that could be linked to for those that adopt Polly for resiliency already? Polly is such a big part of the resiliency story in dotnet that I feel it should atleast be mentioned.

Also, should there be some guidance around not putting "side effects" in the delegate that is "retried". I think anything in this method should be idempotent right?

Page URL

https://learn.microsoft.com/en-us/ef/core/miscellaneous/connection-resiliency

Content source URL

https://github.com/dotnet/EntityFramework.Docs/blob/main/entity-framework/core/miscellaneous/connection-resiliency.md

Document Version Independent Id

29002343-fa42-2f49-3362-53592039b26d

Article author

@AndriySvyryd

[AndriySvyryd]

Execute a "resilient transaction" that spans multiple Db Contexts?

Unless they share the same connection object it would need to be a distributed transaction. It's possible to implement via a TransactionScope, but few providers support it and it's generally not recommended.

can it be mentioned why polly isn't used here?

• IExecutionStrategy was introduced before polly became widely used
• We prefer to avoid additional dependencies when possible
• You can create an IExecutionStrategy adapter for a polly ResiliencePipeline

[roji]

Note dotnet/efcore#30552 which tracks EF and Polly integration. I do believe it may make sense to do something here, possibly as an additional package (Microsoft.EntityFrameworkCore.Polly?).

[dazinator]

but few providers support it and it's generally not recommended.

Most ado.net providers do support TransactionScope for local transactions as far as I am aware. For example SqlServer, MySql, Postgres to name just the first few I checked. If there is a list somewhere showing which ones don't that would be useful to know!

EfCore dbcontext seems to work fine with TransactionScope, atleast until you add an IExecutionPolicy.. which is where "suprise" kicks in. After careful consideration I think a better design is to not use EF core retry policy. I've settled on a design where you have your own IUnitOfWork abstraction and where potentially multiple DbContexts (repositories encapsulating dbcontexts) or other subsystems (Dapper repositories etx) can all participate in the same local transaction - and the resilience is handled at that unit of work level - using polly. This ensures you can mix technologies (dapper, efcore, mass transit outbox message publishing etc) in a local transaction and have a sane place to configure the resilience aspect that isn't tied to just one of them, but more tied to the datastore in use by your application (I.e sql server)

Let's treat the guidance on Distributed transactions as seperate because I agree these are generally not recommended..However TransactionScope only attempts to upgrade to a distributed transaction when you attempt to use transaction spanning multiple different data stores. I'd argue that this isn't the only use case for TransactionScope and that it's actually very beneficial to use it for local transaction against a single data source. Consider the following common pattern - the transactional outbox pattern. In this pattern you often have to update some records using EFCore as one subsystem, and say, MassTransit participating in the same transaction as another subsystem - as it wants to write to an outbox table. Because both subsystems support Transaction Scope, its very easy to wrap them in a transaction scope, and because both subsystems are writing to the same data store, there is no distributed transaction that comes into play, and the code is also more solid because we didn't have to modify any of our repository code to manually hook up the mass transit transaction with the dbcontext database transaction to accomodate the transactional participant, its plug and play.

So my thoughts are: the narrative that most providers don't support Transaction Scope and that in general they are not recommended could be refined to something more nuanced: Most popular sql providers do support TransactionScope for local transactions / i.e transactions spanning a single connection string or data store; but its not recommended to use them in situations where you will be spanning potentially multiple data stores as this gives rise to distributed transactions. In general distributed transactions are not recommended due to: [reasons - scalability etc]. This way it doesn't alienate those that want to use TransactionScope for purely local transactions as being bad engineers for going against recommendations. Unless there are other reasons it's frowned upon that I am unaware of perhaps?

[roji]

@dazinator I'd recommend not conflating things. EF's retrying strategy can be used with multiple contexts without TransactionScope; you need to ensure to use the same connection and transaction by all contexts, but this is the case regardless of whether you use TransactionScope or not (otherwise you get a distributed transaction).

Of course, if you want to use Polly rather than EF's retrying strategy, that's totally fine - but as far as I'm aware there's nothing related to "multiple db context" which favors one over the other. Similarly, if you want to use TransactionScope you may do that, but that's again orthogonal.

Re TransactionScope specifically, It's true that it's supported by most providers (IIRC Microsoft.Data.Sqlite is the notable exception). However, the API doesn't support asynchronous I/O (for commit/rollback), for example.

[dazinator]

@roji
Thanks. I think the short of it is I'd love to see, Polly resilience policies available, per data store (azure sql, postgres etc) and then be able to use EF as a participant in a resilient transaction, rather than EF owning the resilience, and thus also the need to initiate the transaction within that. For example, if I want to use raw ado.net or dapper or efcore, with azure sql, ideally I don't want to have to define 3 different resilience policies to handle the same azure sql error codes as its not very DRY. I'd like to able to initiate my own polly retry for, say, azure sql, and open my own transaction, and then use several participants such as dbcontexts, dapper and raw ado.net calls (all using same connection) all in the same transaction, which is then handled by the resilience policy if anything goes wrong with any of them, thanks to the higher level resilience. If in one case I don't use EFCore but I just use dapper, the code stays the same. Right now the resilience story for EF core isn't about it participating in a user initiated resilient transaction, so both the resilience and the transaction has to be owned by a single DbContext.

[dazinator]

Re TransactionScope specifically, It's true that it's supported by most provider (IIRC Microsoft.Data.Sqlite is the notable exception). However, the API doesn't support asynchronous I/O (for commit/rollback), for example.

I hadn't considered that. I wonder if async support can be added!

[roji]

think the short of it is I'd love to see, Polly resilience policies available, per data store (azure sql, postgres etc) and then be able to use EF as a participant in a resilient transaction, rather than EF owning the resilience, and thus also the need to initiate the transaction within that.

As far as I know, there's nothing stopping you from doing that today. There's no nice integration with Polly (that's what dotnet/efcore#30552 tracks), but you're definitely free to do all the things you want - EF, dapper, raw ADO.NET) - within a single retriable code block that's managed by Polly. Give it a try.

I wonder if async support can be added!

See dotnet/runtime#1420. This is a pretty complex thing to do, requiring also changes in database drivers implementing the System.Transactions interfaces. It most likely won't be done any time soon.

I'll go ahead and close this as I don't think there's anything actionable here that isn't already tracked elsewhere.

[dazinator]

@roji Thanks much appreciated