Understanding InterBase Transactions

From InterBase

Go Up to Transactions


Note: This content comes originally from 2004 BorCon and was published at Embarcadero Developer Network it is now reproduced here for reference.


By: Bill Todd

Abstract: This session covers every aspect of transactions and save points and their effect on InterBase. Topics include isolation levels, the wait option, the record version option, the OIT, OAT, OST and next transaction; what they mean and when they change.

Introduction

A thorough understanding of transactions will let you get the data you want with maximum concurrent access for all of your users. Understanding transactions will also let you avoid errors that can lead to poor performance. The information in this paper applies to InterBase version 7.1 service pack 2 or later unless otherwise noted.

What is a Transaction?

A transaction is a group of changes to one or more tables in the database that are treated as a single logical unit. All of the changes will succeed or all of the changes will fail as a unit. A transaction must exhibit all of the characteristics shown in the following table.

Characteristic Description
Atomicity All of the changes that are part of the transaction will succeed or fail together as a single atomic unit.
Consistency The database will always be left in a logically consistent state. If the server crashes all active transactions will automatically roll back when the server is restarted. The database can never contain changes that were made by a transaction that did not commit.
Isolation Changes made by other transactions are invisible to a transaction until the transaction that made the change commits.
Durability When a transaction has been committed the changes are a permanent part of the database. They cannot be lost or undone.

Note that some databases claim to implement transactions when, in fact, they do not. Paradox tables are a good example. Paradox transactions exhibit neither consistency or isolation. Paradox transactions fail the consistency test because active transactions are not rolled back on restart after a crash thus leaving the database in a logically inconsistent state. Paradox transactions fail the isolation test because they use read uncommitted (sometimes called dirty read) transaction isolation which allows other transactions to see uncommitted changes.

Understanding Transaction Isolation Level

Your transaction's transaction isolation level determines when your transaction can see changes made by other transactions. The ANSI SQL standard defines the following four transaction isolation levels.

ANSI Isolation Level Description
Read Uncommitted Also called "dirty read". Your transaction can see changes made by other transactions that have not committed. This is dangerous because the transaction that made the change can roll back and the data values your transaction saw can vanish. Many databases do not support read uncommitted.
Read Committed Your transaction can see all changes made by other transactions that have committed.
Repeatable Read If your transaction performs a SELECT and later performs the same SELECT again it will see the same values in all of the rows returned by the first select. However, if, after the first SELECT, another transaction inserts a new row and commits the second SELECT will see that new row. Thus, a repeatable read transaction, contrary to what you might expect, does not provide repeatable result sets or a stable view of the data.

InterBase does not support the ANSI standard transaction isolation levels. Instead, InterBase provides the following transaction isolation levels.

InterBase Isolation Level Description
Read Committed Your transaction can see all changes made by other transactions that have committed.
Consistency Also called Table Stability. Provides a stable view of the data and is serializable. However, serializability is achieved by locking tables which blocks updates by other transactions. It is very unlikely that you will ever need to use this isolation level.

Most databases use locking architecture. To provide a stable snapshot of the data in the database they must apply table locks or index range locks to prevent other transactions from updating the data that your transaction is using. This is not multi-user friendly. InterBase uses versioning architecture to provide a snapshot of the database without preventing other transactions from updating the data.

How Does Versioning Work?

All access to data in an InterBase database must take place within a transaction. When a Transaction starts it is assigned a unique number. Transaction numbers are 32 bit integers so every two billion transactions you must backup and restore your database to restart the numbering sequence. Each row version contains the number of the transaction that created it.

For reasons that you will see in a moment, transactions must be able to determine the state of other transactions. InterBase tracks the state of transactions on the transaction inventory pages (TIP). A transaction can be in one of four states so two bits on the TIP are used for each transaction. The four states are:

  • Active
  • Committed
  • Rolled back
  • Limbo

Limbo requires some explanation. InterBase supports transactions that span multiple databases. Committing a transaction that includes changes to two or more databases is done using a process called two-phase commit. Here is what happens when a multi-database transaction is committed.

  1. The server where the commit is issued notifies all other servers involved that a commit has been requested.
  2. Each server sets the transaction state to limbo in each of its databases that is involved in the transaction and notifies the controlling server that it is ready to commit.
  3. When the controlling server receives a message from the other server(s) that they are ready to commit it notifies each server to commit.
  4. When each server receives the command to commit it changes the transaction state to committed.

If one of the servers crashes or if the network connection to one of the servers is lost between steps 2 and 3 above, the transaction will be left in limbo. More about this later.

When a transaction using snapshot isolation starts InterBase makes a copy of the TIP and gives the new transaction a pointer to this copy. This enables the transaction to determine what the state of all other transactions was when it started. When a read committed transaction starts it gets a pointer to the live TIP (actually the TIP cache or TPC) so it can determine the current state of any transaction.

When a transaction updates a row it looks it its TIP to see if there are other active transactions with a transaction number lower than its transaction number. If there are no older transactions that are active it updates the row. If there are older transactions that are still active it creates a new version of the row and enters its transaction number in the new version.

When a snapshot transaction reads a row it looks for the most recent version of that row that was created by a transaction whose state is committed in the snapshot transaction's copy of the TIP. In other words, the snapshot transaction finds the most recent version of the row that was already committed when the snapshot transaction started. Another way to look at this process is that the snapshot transaction ignores all of the changes made by transactions that committed after it started and returns the version of the row that represents the state of the row when the snapshot transaction started. Consider the following example of a row for which four versions exist.

Assume that a snapshot transaction with transaction number 90 attempts to read this row. The read transaction will not see the version of the row created by transaction 100 because the update that created this version took place after transaction 90 began. Transaction 90 also will not read the version created by transaction 80 although it has a lower transaction number because transaction 80 was not commited when transaction 90 started. In this scenario I am assuming that transaction 80 committed after transaction 90 started but before transaction 100 started. Although the version for transaction 60 still exists on disk transaction 60 has rolled back and rolled back versions are always ignored. Therefore, the version that transaction 90 will read is the version created by transaction 40.

The same thing happens when a transaction using read committed transaction isolation reads a row. What is different is that instead of looking a copy of the TIP that it got when it started to determine the state of the transaction that created each version of the row, a read committed transaction looks at the live TIP to determine the current state of the transaction that created the row version. This means that a read committed transaction will get the most recent version of the row that was created by a transaction whose current state is committed regardless of what the state of the creating transaction was at the time the reading transaction started. Therefore, in the example above, a read committed transaction would read the version of row 123 created by transaction 100.

Transaction Options

Besides the isolation level InterBase transactions offer a number of options that you can set to get the behavior you need. These options are divided into several categories and are described in the following sections.

Access Mode

InterBase transactions can be either read only or read write. The default access mode is read write. To set the access mode for a transaction using IBTransaction use the read or, optionally, the write keyword. For example:

concurrency
read

specifies a read only concurrency (snapshot) transaction. You can specify the access mode with concurrency (snapshot), read committed and consistency (table stability) transactions.

Read only transactions consume less overhead and, in the case of read committed read only transactions, do not inhibit garbage collection. Not interfering with garbage collection can lead to better performance. If you do not need to make changes to the database you should always make your transaction read only.

Lock Resolution

When your transaction updates an existing row your transaction places a row level write lock on that row until the transaction ends. This prevents another transaction from updating the same row before your transaction either commits or rolls back. The lock resolution setting determines what happens to the other transaction when it tries to update a row that your transaction has locked.

If the lock resolution setting is wait, the other transaction will wait until your transaction ends, then it will proceed. If the lock resolution setting is nowait the other transaction will return an error immediately when it tries to update the locked row. Here is an example of the IBTransaction parameters for a read write snapshot transaction using the wait option.

concurrency
write
wait

Note that write is not required since the default access mode is read write.

Table Reservation

Normally, if your transaction cannot proceed because a record it needs to update is locked by another transaction it will either return an error or wait depending on the lock resolution setting. If the lock resolution setting is nowait and your transaction generates an error due to a lock conflict your code would probably rollback and try again.

Although rare, there might be a situation where a transaction performs many time consuming operations, possibly requiring hours to complete. In this case it might not be acceptable to get most of the way through the processing and have the transaction fail due to a lock conflict. If, for some reason, you cannot use the wait option there is another alternative.

The table reservation mechanism lets you lock tables when your transaction starts so you are guaranteed the access you need to every row in the tables for the life of your transaction. The disadvantage of table reservation is that no other transaction can update the reserved tables for the life of your transaction. This is why this option is rarely used. The four table reservation options are described in the following table.

Reservation Description
Shared, lock_write Write transactions with concurrency or read committed isolation can update the table. All transactions can read the table.
Shared, lock_read Any write transaction can update the table. Any transaction can read the table.
Protected, lock_write Other transactions cannot update the table. Concurrency and read committed transactions can read the table.
Protected, lock_read No other transaction can update the table. Any transaction can read the table.


Here is an example of the IBTransaction. Params for a concurrency read write transactions that reserves the employee table for protected read only access.

concurrency
nowait
protected
lock_read=EMPLOYEE

Note that the table name is case sensitive. The following shows a read commited transaction that locks the employee table for shared read access.

read_committed
nowait
shared
lock_read=EMPLOYEE

You can reserve as many tables as you need and the tables can have different reservations. For example:

concurrency
nowait
protected
lock_read=EMPLOYEE
shared
lock_read=SALARY_HISTORY

IBX Params Keywords

The following tables give all of the keywords you can use in the IBTransaction Params property with a description of each.

Isolation Level Keywords Description
concurrency Concurrency (also called snapshot) transaction isolation.
read_committed Read commited transaction isolation.
consistency Consistency (also called table stability) transaction isolation.
Access Mode Keywords Description
read Transaction has read only access to the database.
write Transaction has read/write access to the database.
Lock Resolution Keywords Description
wait If a record is locked the transaction will wait until the record is unlocked.
nowait If a record is locked the transaction will return an error.
Table Reservation Keywords Description
shared  
lock_write=TableName 
Write transactions with concurrency or read committed isolation can update the table. All transactions can read the table.
shared  
lock_read=TableName
Any write transaction can update the table. Any transaction can read the table.
protected  
lock_write=TableName
Other transactions cannot update the table. Concurrency and read committed transactions can read the table.
protected  
lock_read=TableName
No other transaction can update the table. Any transaction can read the table.

Ending a Transaction

You can end a transaction by committing it or by rolling it back. When you commit a transaction InterBase changes the transaction's state on the TIP from active to committed.

When you roll back a transaction what happens depends on how many changes the transaction includes. For reason's I will explain later in this paper, rolling back a transaction can degrade performance. Committing a transaction will never degrade performance. Therefore, if the number of changes in the transaction is less than or equal to 10,000 InterBase undoes the changes to each row and commits the transaction. If the number of changes is greater than 10,000 InterBase changes the transaction's state on the TIP from active to rolled back and leaves any record versions created by the transaction in place.

OIT, OAT, OST and Next Transaction

InterBase tracks four values that are important in understanding how transactions work. These values are the oldest interesting transaction (OIT), the oldest active transaction (OAT), the oldest snapshot transaction (OST) and the next transaction.

Next Transaction

The next transaction is the number that will be assigned to the next transaction that starts.

OIT

The OIT is the oldest transaction whose state is other than committed. Put another way, the OIT will be equal to the transaction number of whichever of the following three transactions is oldest.

  • The OAT
  • The oldest rolled back transaction
  • The oldest limbo transaction

Normally the OIT and the OAT are the same transaction. When the OAT is committed both the OIT and the OAT advance to the number of the new oldest active transaction. However, there are three things that can cause the OIT to stop advancing.

  1. Rolling back a transaction that includes over 10,000 changes.
  2. Automatic rollback of transactions that were active when the database server crashed. This happens automatically when InterBase is restarted.
  3. A transaction stuck in limbo.

If 1 or 2 happens the oldest transaction with a state other than committed will no longer be the OAT but instead will be the oldest transaction that was rolled back. If a transaction is stuck in limbo due to a failed two-phase commit it can be the oldest transaction with a state other than committed.

OST

The oldest snapshot transaction is the lowest number that appears in the Oldest Snapshot field of any active transaction. Here is how the Oldest Snapshot value of a transaction is set.

  1. When a read only read committed transaction starts the Oldest Snapshot field is not assigned a value.
  2. When a read/write read committed transaction starts, its snapshot number is the same as its transaction number.
  3. When a snapshot transaction starts, its snapshot number is set to the transaction number of the oldest active read/write transaction.

The OST only moves forward when a new transaction starts, when a commit retaining is done or when a sweep is run. Commit retaining on a snapshot transaction that has performed updates commits the existing transaction and starts a new transaction whose snapshot number is the same as the snapshot number of the transaction it is replacing. This can lead to an OST that is less than the OIT.

Garbage Collection

Since InterBase creates new row versions whenever a row is updated and there are other active transactions that might need the current row values there must be a way to remove row versions that are no longer needed to keep the database from growing rapidly. This process is called garbage collection. Garbage collection happens automatically each time a row is accessed. Garbage collection is done by a background thread so the user does not see any performance degradation when accessing a table with a lot of garbage row versions that need to be deleted.

A sweep is an operation that garbage collects every row in every table in the database. If you leave the sweep interval set to its default of 20,000 transactions a sweep will be triggered automatically when OAT - OIT >= 20,000\. This will only happen if the OIT gets stuck as described above. If the OIT is stuck due to a rollback the sweep will remove all of the row versions belonging to all rolled back transactions as well as all row versions up to the most recent committed row version whose transaction number is less than the OAT. This will unstick the OIT and allow it to move up to the OAT.

You can change the sweep interval using IBConsole by choosing Database > Properties from the menu and setting the sweep interval in the dialog box below.

You can also change the sweep interval using the gfix command line utility. The following command sets the sweep interval to 10,000.

gfix -h 10000 -user sysdba -password masterkey employee.gdb

If the OIT is stuck because a transaction is in limbo garbage collection cannot remove any record version created by a transaction greater than the OIT. The only way to fix this problem is to commit or rollback the limbo transaction. Using IBConsole choose Database > Maintenance > Transaction Recovery from the menu. See the Operations Guide for detailed instructions for recovering limbo transactions with IBConsole.

To fix limbo transactions using gfix use the following command.

gfix -two_phase -user sysdba -password masterkey employee.gdb

The two_phase switch decides automatically whether to commit or rollback each limbo transaction. To see what choice gfix will make without actually committing or rolling back the transaction use the -list swtich. To commit all limbo transactions or a specific transaction use the -commit switch. To rollback one or all limbo transactions use the -rollback switch. For detailed information see the Operations Guide.

Since the events that can stick the OIT and cause an automatic sweep are very rare with InterBase 7.1 SP 1 and later automatic sweeps rarely happen. Automatic garbage collection cleans up rows as they are accessed but in many database applications many rows are accessed rarely. This means that unneeded record versions can remain in the database for a long time. The solution is to run a sweep manually on a regular basis.

You can run a sweep from IBConsole by choosing Database > Maintenance > Sweep from the menu. You can also run a sweep using gfix with the following command.

gfix -sweep -user sysdba -password masterkey employee.gdb

If you want to sweep the database regularly just create a batch file that contains the command above and use Windows Scheduler to run it automatically at the interval you choose.

Possible Problems

There are a few transaction related problems that can occur. The following sections look at what they are, how to prevent them and how to fix them if they occur.

What Happens when the OIT Gets Stuck

Neither automatic garbage collection or a sweep can remove any record version created by a transaction whose number is greater than the OIT. So, if the OIT gets stuck garbage collection stops. When garbage collection stops and new transactions continue to be created performance begins to suffer for three reasons.

  1. Retrieving a row takes longer if there are many record versions that must be examined to find the right one.
  2. Database size increases.
  3. The TIP gets larger.

The TIP gets larger because the TIP contains the state of every transaction with a number equal to or greater than the OIT. If the OIT stops advancing and new transactions are being created the TIP has more transactions to track and grows in size. This takes more memory on the database server. It also makes starting a transaction that uses snapshot (concurrency) transaction isolation slower because each snapshot transaction gets its own copy of the TIP when it starts. As the TIP gets larger it takes both more memory and more time to make the copy. The higher the transaction volume the faster performance will degrade.

Fortunately this is a very rare problem with InterBase 7.1 and later because the events that will stick the OIT are rare. If the OIT does get stuck running a sweep will correct the situation unless the cause is a limbo transaction. If the cause is a limbo transaction you can correct the problem by commtting or rolling back the limbo transaction. If you roll back the limbo transaction you will need to run a sweep to get the OIT moving again.

What Happens when the OAT Gets Stuck?

When the OAT gets stuck because a transaction is left active the OIT also gets stuck. Therefore, the same performance degradation will happen for the same reasons. However, with InterBase 7.1 SP 1 and later not every active transaction will cause the OAT to stick. Here are the rules.

  1. A read only read committed transaction can remain open indefinitely without causing the OAT to stick.
  2. A read/write read committed transaction can remain open indefinitely as long as you call commit retaining each time the transaction updates the database.
  3. Any snapshot transaction will stick the OAT. Snapshot transactions should be committed as soon as possible to prevent performance degradation.

Note that these rules apply only to InterBase 7.1 SP 1 and later. In earlier versions of InterBase any active transaction will stick the OAT.

Savepoints

InterBase 7.1 introduced SQL 92 standard savepoints. A savepoint is a named point in a transaction that you can rollback to without rolling back the entire transaction. Savepoints are particularly useful in stored procedures and triggers. You can create a savepoint with the following statement.

SAVEPOINT MY_SAVEPOINT

where MY_SAVEPOINT is the savepoint's name. The name must be unique within the execution context which is an application, a trigger or a stored procedure. For example you could have many savepoints with the same name if one was in your application, and the others were in different stored procedures and triggers. If you no longer need a savepoint release it as follows.

RELEASE SAVEPOINT MY_SAVEPOINT

where MY_SAVEPOINT is the name of the savepoint to release. To rollback to a save point use the following command.

ROLLBACK TO SAVEPOINT MY_SAVEPOINT

When you rollback to a savepoint the savepoint is also released. If you rollback to a savepoint all savepoints created after the one you rollback to are also rolled back and released. Use savepoints any time you need to undo some of the changes within a transaction. For example, you could create a savepoint at the beginning of a stored procedure and if the stored procedure is unable to complete its work your code could roll back to that save point before exiting the stored procedure.

Using Transactions with ISQL

The ISQL command line tool is a handy way to test transaction behavior in conjuntion with the InterBase Performance Monitor. You can easily start multiple ISQL sessions at the same time with a different transaction in each session. ISQL supports all InterBase transaction options so you can test any transaction or combination of transactions. For detailed documentation on ISQL see chapter 10 of the Operations Guide. For a brief list of ISQL's command line switches enter:

isql -?

at the command prompt to see the following display.


To start ISQL open a command prompt and enter the command:

isql -u sysdba -p masterkey employee.gdb

where sysdba is the username and masterkey is the password. When ISQL starts and connects to the database you will see the ISQL command prompt as shown in the following image. Note that ISQL first displays the name of the database you are attached to and the user you are logged in as. Then it displays the ISQL> prompt to show that it is ready for a command. All ISQL commands must end with the current terminator character which, by default, is the semicolon.


There are two ways to close ISQL. The EXIT command commits the current transaction and closes ISQL. The QUIT command rolls back the current transaction and exits ISQL.

Starting a Transaction in ISQL

When ISQL connects to a database it automatically starts a transaction. You can end the current transaction by issuing the COMMIT or ROLLBACK command. To start a new transaction issue the SET TRANSACTION COMMAND. The following table shows the options for SET TRANSACTION.

Command Description
READ WRITE or READ ONLY
Specifies the transaction's access mode.
WAIT or NO WAIT
Specifies how the transaction will handle lock conflicts.
ISOLATION LEVEL SNAPSHOT [TABLE STABILITY] 
or  READ COMMITTED RECORD_VERSION 
or READ COMMITTED NO RECORD_VERSION
Specifies the transaction isolation level.
RESERVING <table name> FOR [SHARED or PROTECTED] [READ or WRITE]
Specifies the tables to reserve and the locks to be applied.
SET TRANSACTION READ ONLY NOWAIT ISOLATION LEVEL READ COMMITTED;
starts a read only read committed transaction that will return an error immidiately if it needs to lock a row that is already locked by another transaction. Of course the NOWAIT option is superfluous. Since this is a read only transaction it will never lock a row.

Monitoring Transactions

InterBase 7 introduced a set of temporary tables that provide information about the attachments, transactions and statements the server is executing in the database you are connected to. The temporary tables give you the ability to analyze what the server is doing as it runs and, if necessary, force transactions or attachments to terminate. InterBase 7.1 integrates Craig Stuntz's Performance Monitor into IBConsole. This gives you a visual display of the information in the temporary tables. The following image shows the Performance Monitor with the Transactions tab selected.


Here two transactions are active. The second one belongs to the SYSDBA user and is the read only read committed transaction used by Perfromance Monitor. The first transaction is also a read only read committed transaction that belongs to user BILL.

The two toolbar buttons let you close the selected transaction and find the attachment the transaction belongs to. Clicking the Find Attachment button takes you to the Attachments page and highlights the attachment that owns the transaction as shown below.


To see summary information for all of the transactions that are active in the database switch to the Database tab and scroll to the end of the grid as shown below.


The Transactions entry shows the total number of active transactions. A little farther down you can see the Next transaction number, the oldest active transaction number (OAT), the oldest interesting transaction number (OIT) and the oldest snapshot transaction number (OST). This display is particularly useful if you suspect that either the OIT or the OAT is stuck. Since Performance monitor automatically refreshes the display you can watch as transactions are started and committed and tell easily if either the OIT or the OAT is not advancing.

Summary

Understanding transactions and how they interact is the key to writing high concurrency applications that provide the the right view of the data for each task. The IBConsole Performance Monitor makes it easy to see what transactions are active and their properties. With this information you can diagnose any concurrency problem.

Back To: