Multiple concurrent transactions in the same connection.

    IB doesn't support nested transactions, as explained in the transaction options. This is the reason you don't see any reference to levels of nesting in the guides. SqlServer offers nesting and Oracle should do, too but it's not a widely implemented feature on other engines.

Concurrent transactions can coexist in IB thanks to the multi versioning engine. They act as if they were owned by different connections: they are independent but they interact when trying to write to a table. IB promises no locking among readers and writers, not among writers. Allow every writer to proceed at any time would mean the end of the data integrity.

Allowing multiple concurrent transactions in the same connection is a bless when you are working on an interactive application: each transaction can have its own isolation level and can commit or rollback at the time you need. This means you can run a set of data entry forms with an "interactive level" (read committed) while running a report with a consistent (frozen) view of the data (snapshot) in the same application.

Being able to do these tricks in one connection has its merits: because connections are related to the number of licenses (currently, 4 connections per license), you'll want to preserve connections in case the same user runs other tools that need their own connections and avoid needing more than one license per user, for example. Also, several transactions in one connection represent less resources in the server than several connections with one transaction per connection. Furthermore, cooperating tasks that can work on disjoint sets of data can proceed in different transactions and be committed or rolled back separately without interfering.

Other people may tell you that these features don't matter as you should get all the data from the database, disconnect, make changes and finally reconnect and submit all your modifications in a batch mode. They prefer the multi tier architecture or the disconnected datasets. They probably have legitimate arguments to use this model, because it's the foundation for distributed data access. In the classic C/S model, however, the client works with live data and submit changes to the server as they are posted and it always has communication with the server, so this is the field where you can grasp the benefits of simultaneous transactions. Of course, there are platforms like mobile connectivity where you cannot afford to have permanent communication with the database server. On the other hand, although web servers' operations are more akin to the disconnected model (due to the stateless nature of the HTTP protocol), connections are often cached and reused for better performance.

Be careful that only one transaction that's kept for a very long time will cause the OAT to stall even if such transaction only reads from the database. To avoid this, it's important to recycle transactions; this means doing a commit or rollback after a reasonable amount of operations. In the meantime, you can use Commit Retaining to control your transactions without affecting the graphical data aware controls. However, only a hard "Commit" lets garbage collection to proceed.

At that time, only IBO, FIB, IBX and the raw IB-API let you manage multiple transactions inside one connection. BDE/SQLLinks and ODBC drivers are limited to one transaction per connection and there's no OLE-DB/ADO driver yet for IB.

This page was last updated on 2000-05-26 04:28:46