Understanding Transaction Lifetimes

Posted by on in Blogs

Understanding Transaction Lifetimes

Read this article in Russian

InterBase 7.1 radically changes the rules for transaction lifetime management in InterBase. Furthermore, some of the commonly repeated admonitions regarding the effect of long transactions on certain internal characteristics of the InterBase server are not correct. This article will explain in detail the life cycle of an InterBase transaction and explain when it is possible to use long transactions without degrading server performance. In older versions of InterBase there is never a safe way to do this, but with InterBase 7.1 the rules are pretty simple.

What is a Long Transaction?

In truth, it isn't so much the length of a transaction which can affect server performance but rather the number of transactions started after the oldest active transaction. But if we presume a more or less consistent stream of transactions starting and ending over time then it is simpler to talk about transaction length. A "long" transaction can be loosely defined as one which is either left open indefinitely (for example, for as long as a user happens to leave a dialog box open, given that they may decide to go out for lunch before hitting OK), or which is left open for longer than is required by an application.

It is always an error to leave a transaction open longer than required by an application. For example, if you open a transaction, make some changes to a record, and then do not end the transaction then you are blocking other users from either seeing your changes or changing that record themselves.

On the other hand, there may be a genuine need to have a transaction open indefinitely. For example, InterBase Performance Monitor refreshes its view of the InterBase temporary system monitoring tables every ten seconds. If it used a short transaction for every refresh it would burn through tens of thousands of transaction IDs per day. In most cases this isn't really a problem but ideally you should be able to leave Performance Monitor open 24/7. Transaction IDs are a signed 32 bit integer and it is necessary to backup and restore the database when you run out of transaction IDs (just over two billion transactions). Because of this, InterBase Performance Monitor uses a single, long-running transaction, and exploits features of InterBase 7 which allow this kind of transaction lifetime without a negative performance impact on the server.

Transaction Options

InterBase allows you to specify a number of different transaction options, but two in particular are important when considering transaction lifetime: Isolation mode, and read-only. The two most commonly used transaction isolation modes are read committed, which allows a transaction to see data committed by other transactions, and snapshot, which ensures that a transaction will see a consistent view of the database as it existed at the instant the transaction began (plus changes made by the transaction itself). A read-only transaction, obviously, is not allowed to change data in the database, whereas a read-write transaction can.

So, without further ado, here are the rules for long-running transactions in InterBase 7.1, Service Pack 1, based on your transaction options:

Transaction options Effects of long-running transaction
Read committed, read only This transaction can run forever with no negative impact on performance.
Read committed, read-write This transaction can run forever with no negative impact on performance if you do a commit retaining from time to time.
Snapshot, read only, or snapshot, read-write Snapshot transactions must be ended with a hard commit in order to avoid slowing the server. Commit retaining will not help.

This means that long-running transactions don't need to be a problem in InterBase 7.1 unless you either need to have a very large group of changes succeed or fail as a group or unless you need snapshot isolation for a protected period of time.

That's the simple overview. If you'd like to understand what's going on under the hood, read on....

Transaction Internals

Transactions are sequentially numbered as they start. The "oldest" transaction is the one which started first, and has the lowest number.

Before defining a few terms it is necessary to explain an implementation detail of how InterBase performs rollbacks. For reasons which will become clear later on, InterBase performs better when it commits rather than rolls back a transaction. Because of this InterBase will attempt to change a user request for a rollback into a commit under the hood when possible. It does this by undoing the changes the user made to records and then committing the transaction with the changes undone. InterBase can do this, in version 7.1 SP1, as long as the transaction has modified less than 100,000 records. If the user has modified more than 100,000 records InterBase rolls back the transaction "for real". InterBase also does a "real" rollback if the server crashes when a transaction is active -- the transaction will be rolled back when the server restarts." 

Oldest Active Transaction
As the name implies, the oldest transaction which a user has started on an active connection but which they have not committed or rolled back yet.
Oldest Interesting Transaction 
The first transaction with a state other than committed. In practice, this is either (1) the same as the oldest active transaction or (2) the number of the last "real" rollback (see above) or (3) an in-limbo (half complete) two-phase commit. Doing such a rollback will "stick" the OIT, but it will be "unstuck" by the sweep when it runs. The OIT is also "stuck" if there is a long-running snapshot transaction, for the lifetime of the transaction. Finally, a read committed, read-write transaction will "stick" the OIT until a commit retaining is performed on that transaction.
Oldest Snapshot Transaction
This sounds like it should be the number of the oldest transaction with snapshot isolation, but this is never the case. Each transaction has its own "snapshot number." The Oldest Snapshot Transaction is the oldest "snapshot number" of any active transaction (note that you can see this value for every transaction using InterBase 7 performance monitoring). A read-only, read committed transaction has no snapshot number. A read-write, read committed transaction has a snapshot number equal to its own transaction number. Since doing a commit retaining essentially means starting a new transaction with the same context as the old transaction, doing a commit retaining on a read-committed, read-write transaction will move its snapshot number forward. For a snapshot transaction, the "snapshot number" is the number of the Oldest Active Transaction at the time that transaction was started. The Oldest Snapshot Transaction is updated when a new transaction starts (or a commit retaining is performed), or when the sweep is run.
Next Transaction
This is the number which will be assigned to the assigned to the next transaction to start.


In a database where no large rollback has been performed, and no snapshot transactions performed, then, these four milestones will be ordered as follows, from oldest to newest:


In a database where a large rollback has been performed but no snapshot transactions have been performed the order will be as follows:

OIT -> OST = OAT -> NT

In a database where no large rollback has been performed but a snapshot transaction is active the order might be:

OST -> OIT = OAT -> NT

What These Numbers Mean

InterBase has a special page in the database called the Transaction Inventory Page (TIP). When a transaction begins it gets a copy of the TIP all the way back to the Oldest Interesting Transaction (OIT). However, transactions with read committed isolation can share the same copy. Transactions with snapshot isolation get their own copy. So when the OIT gets "stuck" the TIP image each new snapshot transaction has to copy more data than when the OIT is not "stuck." And the shared copy for read committed transactions grows larger and larger as well.

The Oldest Snapshot Transaction controls which back record version can be garbage collected. Another way of phrasing this is that it indicates which record versions might be visible to an active transaction. As long as a transaction with snapshot isolation is open, every record version which was visible at the time the transaction started must remain in the database. So keeping a snapshot transaction open, or keeping a read committed, read-write transaction open without doing a commit retaining from time to time, will result in back record versions accumulating in the database file.

The sweep process is controlled by the difference between the Oldest Interesting Transaction and the Oldest Active Transaction. When the Oldest Interesting Transaction is older than the Oldest Active Transaction by more than the number of transactions specified in the database header page as the sweep interval, the sweep runs. (You can set this number via gfix -housekeeping or IBConsole.) The sweep garbage collects the entire database and it "unsticks" the OIT when it has become "stuck" as the result of a large rollback.

The Oldest Active Transaction will become the "oldest snapshot" for a new snapshot transaction when it begins.

And the Next Transaction is mostly useful when comparing with the other numbers. Unless you have a good reason to have a long-running snapshot transaction the next transaction and oldest active transaction numbers should be close to each other -- within a few hundred, even on a fairly busy database. 

In most cases, the Oldest Snapshot Transaction should be close to the Oldest Active Transaction. The exception is when the Oldest Active Transaction is a snapshot transaction and the database server was fairly busy (lots of active transactions) when the Oldest Active Transaction started. The Oldest Interesting Transaction should be the same as the Oldest Active Transaction unless there was a large rollback in the past or an in-limbo transaction (incomplete two-phase commit on a multi-DB transaction).

Performance Impact of Active Transactions

There are two ways in which transactions can affect server performance:

Memory Use
When the Oldest Interesting Transaction is stuck by one factor or another, the server must consume additional memory, especially if many snapshot transactions are started. This makes the server slower as less memory is available to perform query tasks. In the worst case the OS may start swapping memory to disk, dramatically reducing server performance.
Back Record Versions
When the Oldest Snapshot Transaction is stuck the garbage collector cannot clean up old record versions. Although InterBase 7.1 significantly reduces the number of times these old record versions must be revisited, highest performance is achieved when there are fewer back versions in the record chain. 
Garbage Collection Yield
There is a related problem which occurs when you are doing large numbers of INSERTs or UPDATEs and create back record versions faster than the garbage collector can clean them up. The solution in this case is to set SWEEP_YIELD_TIME to 0 in ibconfig. This gives the garbage collector thread the same priority as a user thread and should help the collector keep up with the old record versions users are creating. This means that the collector thread can potentially take time away from user query execution, but any performance reduction from this will be less than the impact of old record versions piling up in the database file.


Thanks to Charlie Caro, Sriram Balasubramanian, and Shaunak Mistry of InterBase R&D for putting up with my repeated questions on this issue. Thanks to Bill Todd for doing additional research in this area.

Tags: InterBase


  • Guest
    Dmitri Kouzmenko Thursday, 28 October 2004

    Craig, you wrong here

    >The sweep process is controlled by the difference between the >Oldest Interesting Transaction and the Oldest Snapshot >Transaction.

    you can look at tra.c, there is code:

    if ...

    (trans->tra_oldest_active - trans->tra_oldest > dbb->dbb_sweep_interval)...


    TRA_sweep (tdbb, trans);

    I.e. sweep starts when difference between oldest ACTIVE and Oldest transaction became bigger than sweep interval (<>0).

  • Please login first in order for you to submit comments
  • Page :
  • 1

Check out more tips and tricks in this development video: