Subject Using multiple transactions with one connection handle
Author Nikolay Samofatov
Hello, Roman!

We need to be able to work with multiple concurrent transactions over a
single connection.
Do you have recommendations on how this should be implemented in Jaybird?

I can think of the following approach:
1) declare forkConnection method in FirebirdConnection
2) implement method in AbstractConnection which would return null (to
avoid unknown/untested effects with JCA/SPI)
3) implement real method in FBConnection which would make a deep copy of
FBManagedConnection and GDSHelper (except copying the existing
transaction handle so new transaction would be started)

GDS implementation appears to be thread-safe in Jaybird so this would work.

If this approach seems to make sense, I'll go forward with
implementation and send you the patch for review.

The rationale behind the need is as follows:

This is a large application which uses plain vanilla two-tier
architecture. Business logic is implemented on the server using Java
Stored Procedures (using Jaybird) with Red Database
Client side is Swing UI (also using Jaybird). This is a secure
application (class C1 - highest civil security grade).

Reads are conceptually done using read-only read-committed
(pre-committed) transactions or isc_tpb_concurrency transactions. Writes
use separate read-committed transactions which perform record-level
pessimistic locks on data while user is editing something.

Each user can have only one socket connection to the database server.
The reasons for that:
- RDB security policies are configured [1] to shut down dormant
connections, [2] to prevent non-privileged users from opening more than
1 connection

Nikolay Samofatov, MBA
Red Soft Corporation
+7 495 668 3735