Sean,

> I am not aware of any database server which does not relate
> transaction(s) to a specific database connection -- how else could
> you track/determine 'dead' transaction/connections?

Connections by "pinging" the client, transactions by timeouts. :) I
remember the discussion in firebird-devel about timeouts, and I do not
want to start it again. But in distributed environment you have to use
timeouts (making communication synchronous), because in asynchronous
environment distributed agreement is not possible since there is no
possibility to distinguish slow clients from the failed clients.

At work I have distibuted leasing transaction manager and it works
perfectly. Client on start of transaction tells the server for how
long he wants to lease transaction resource. If lease was not released
or renewed before expiration, transaction is marked as "rollback
only". There is low-priority thread that rolls back all "rollback
only" transactions.

You can implement similar approach for connections too. In this case
you do not need any "ping" thread on the server. It will be client's
responsibility to renew connection and/or transaction leases.

> The "decoupling transactions and connections" is something which can
> (and IMHO should) be done within a threaded client-side
> connection/transaction pool manager.
>
> The abstraction of the transaction/connection can easily be done.
> The solution lies in thinking of a client database object not as a
> connection but as a transaction (naturally with an associated db
> connection).
>
> Thus, a client A could create a new database/transaction object,
> store the object reference (ID #), perform some work, disconnect and
> the reconnect (specifying the database object reference), and resume
> work (commit/rollback).

This is how it works right now. But how many concurrent transactions
can you support? If you have a lot of databases and many clients that
start/suspend/resume/end their transactions, and most of the
transactions are suspended, then you will simply run out of sockets.
20 databases with 50 running transactions (event read-only) in each
database means 1000 open sockets.

Probably we will need to introduce more intelligent caching in the
server, like all "database/transaction objects" with same DPB use the
same socket per thread. But this only complicates middle-tier, and
personally I believe that this problem can be solved much more easier
on the server. And solving this on server would be more correct from
the architectural point of view.

And do not forget native XA plugin, there you need Xid and this
decoupling. ;)

Best regards,
Roman