[Firebird-net-provider] Feature Request: Multiple Transactions per Connection....

[Willibald K. <wi...@dw...>]

Hi!

I'm using the Firebird ADO.NET provider 1.0 for some time now and I have to
say that it's well done.

Of course there are some things I would like to see improved, like it would
be nice to have differnet FbExceptions: (FbDatabaseNotFoundException,
FbSQLException with an Enum for the Error, ..). However, this is  just a
'nice to have' feature and one can work around that by directly comparing
the error code with a hard coded value to obtain the reason for the
exception. (Yes, it's ugly, but it works)

But there is one behaviour, which is considered ADO.NET standard (AFAIWT),
that makes me feel sick: Only one transaction per connection is allowed.
Let me provide you some details of why I think the .NET provider should have
an option in the connection string to turn this behaviour off:

We all know that Firebird is a multi-generational architecture database
management system. This implies that working with one long lasting
transaction is considered to be bad practice. Instead updates/inserts should
be made in a separate - short - transaction besides the longer lasting read
only transaction that fetches the data.
Currently the .NET provider allows only one transaction per connection. If
you consider a client application that has several windows that display
different data and these can be edited (for each dataset in edit state you
need a transaction for 'locking' ) / shown at the same time, you'll come to
the conclusion, that you need more than one transaction per client.
Unfortunately this leads to the consequence that one needs more than one
_connection_ per client.
This is not only impractical when you want to implement some licensing
schema that allows only a fixed number of connections (= 'clients'), but
also brings unnecessary load to the firebird server!
Just imagine you have client applications that might open five concurrent
transactions and you have five clients attached - then the server has to
manage 25 connections instead of 5...
If you happen to implement the database logic on a server too (so the
clients are getting their data through this 'application server' and are
considered to be somewhat 'thin clients'), you see that the application
server needs to create 20 more unnecessary connection objects (with all the
things attached to them).

In my mind multiple transaction per connection is a cornerstone and a 'must
have' for writing efficient and good style firebird client applications.

Thanks for reading,
 Willibald Krenn