No transaction for read queries but upgrade for write queries

[Piedone]

During the Orchard community meeting it came up that in our linq2db Orchard integration we use a transaction every time any query is executed. This is not an issue when there's already a transaction in flight for the given connection, or if the query is a write query but it has a performance penalty otherwise if the query is only a read query.

While you can create a DataConnection without a transaction it's not clear to me whether linq2db will open one automatically if the query is a write one? To put it otherwise, is there an optimization built-in that seamlessly allows you to execute read queries without a transaction but write queries with one?

[MaceWindu]

linq2db never opens transaction without explicit request and we don't have any intentions to provide such opionated functionality.

[Piedone]

It might be opinionated but it's also quite useful in high-load scenarios :). Hence EF doing it too. Thus even if not always on but I think such functionality would be useful in an optional way. However, I wasn't really asking for a feature but about the current behavior, so thank you.

By "interceptor" do you mean ProcessSourceQueryable?

[MaceWindu]

Nope, we replaced current assorted set of events and delegates with this https://github.com/linq2db/linq2db/wiki/Releases-and-Roadmap#interceptors

BTW, we actually have one place where we create transaction if it is missing: multi-query eager load operation will be wrapped into transaction of connection doesn't have active transaction

[sdanyliv]

I cannot get why we have to open transaction implicitly. All linq2db write operations (DML) are atomic.

[viceroypenguin]

I agree with @sdanyliv - there is no reason for l2db to have automatic transactions.

EF uses transactions to cover the "Save" operation, which may have multiple writes to complete the operation.

l2db operates at a different level; each operation is implicitly atomic at the db level, so there is no reason to create a transaction to cover them. If you are writing a larger operation that includes multiple db writes, then a transaction should be explicitly created, either via .BeginTransaction()/.EndTransaction() or via TransactionScope

[Piedone]

Yeah, since I'm coming from Orchard, there is always an ambient transaction if write queries already happened within the request. So, we're using linq2db in a context where there are always multiple queries (read/write, with linq2db and outside of it but on the same connection alike). Thus linq2db queries should hook into that too, hence my question.

Thanks everyone for the details!