Commented by: F.D.Castel (fdcastel)

Attached is a patch to implement this suggestion using a new property: ImplicitTransactionBehavior (in FbCommand)

The default behavior is CommitOnExecute, to keep backward compatibility. This is not the ideal, since it won't fix the problem with ORMs (or any code using System.Data.Common). The default should be CommitOnDispose, but this could be changed in a next "bigger" release (3.1 or 4.0)

In this patch there is also some performance tests (not actual tests, should not be commited into /trunk) only to show the difference between the two behaviors:

ImplicitTransaction_CommitOnExecute: 100000 commands in 17,949 seconds; 5571,34102178394 commands per second
ImplicitTransaction_CommitOnDispose: 100000 commands in 7,578 seconds; 13196,093956189 commands per second

Even with CommitOnDispose as the default behavior, all current unit tests run successfully.

Two considerations:

1) In ICloneable.Clone() there is a duplicate assignment of UpdatedRowSource. Is it necessary?

2) In Release() I've kept the rollback for backward compatibility only. I believe it could be always a commit, in both cases.

Commented by: @cincuranet

This is not going to happen. This behavior is standard in whole http://ADO.NET world (and not only there), and any provider is expected to behave like this.

The ORMs handle transactions, very often, per unit-of-work (when pushing it to database), and you can (should) use TransactionScope (or custom tx handling depending on ORM) if you need more control.

Getting EF model is done using one command (but fairly huge), so there's actually sequence of starting implicit transaction, executing command and committing. But because the command is huge (takes a time to execute, especially because FB's optimizer isn't that good in this case) and it's loading huge amount of data (FB's wire protocol isn't that succinct as it could be (and there's still room for improvement on client side as well)).

Commented by: F.D.Castel (fdcastel)

You're right. I did the same test with SqlServer and using implicit transaction is (marginally) slower.

* Using implicit transaction:
SqlServer: 100000 commands in 6,2946 seconds; 15886,6329870047 commands per second

* Using explicit transaction:
SqlServer: 100000 commands in 6,1479 seconds; 16265,7167488085 commands per second

So, my suggestion was a bad call. The current implementation follow the http://ADO.NET guidelines for providers and must be kept. Thank you very much for the pointers!