Transaction options explanation.

    A transaction is a logical unit of work. This means several commands may be comprised in a single transaction. Also, a single command may encompass several units of physical work, for example, an UPDATE with a WHERE clause that yields more than one affected record. When you save (commit) or undo (rollback) a transaction, this affects all actions performed under this transaction (since the transaction was started or the last commit/rollback that retained the transaction context). While the transaction is in progress, changes inside it cannot be viewed by another transaction (except where in dirty read mode, see below). A transaction has no theoretical limit in the number of commands it can undo or save; however, in practice, there are practical limits imposed by the architecture of each RDBMS.

It's important to realize that all IB work is done inside a transaction, either implicit or explicit. This is by design because it allows the multi versioning engine to work. If you aren't controlling transactions, each statement is executed in a transaction that terminates after that statement automatically. This is known as auto commit. Take note that even in this case, each implicit transaction may span several operations, because changing only one record may fire a trigger and it may modify several records in another table and so on. Therefore, auto commit means one statement and all its effects are treated as one transaction that is committed when successful or it's rolled back when any operation fails.

IB doesn't support nested transactions. Hence, the stored procedure and trigger language doesn't support transaction control commands. In IB, each procedure or trigger runs in the context of the transaction initiated by the client application. However, IB allows several concurrent or parallel transactions in the same connection but sadly this capability is not offered through generic database access products like the BDE or ODBC: for this, you have to rely on the IB-API, on the free FIB, on the new IBX or in the third party, full source commercial product known as IBO.

For people used to relational databases, the options offered by IB may seem cryptic, but this is more due to the different names than to completely different possibilities. In fact, IB performs like any other relational database in the general cases. From now, I will use "txn" as a synonym for "transaction". This is the general syntax:

SET TRANSACTION [NAME transaction]

[READ WRITE | READ ONLY]

[WAIT | NO WAIT]

[[ISOLATION LEVEL] {SNAPSHOT [TABLE STABILITY]

| READ COMMITTED [[NO] RECORD_VERSION]}]

[RESERVING <reserving_clause>

| USING dbhandle [, dbhandle ...]];

<reserving_clause> = table [, table ...]

[FOR [SHARED | PROTECTED] {READ | WRITE}] [, <reserving_clause>]

Blocking mode: with wait, the default mode, when a conflict appears, the user that finds a conflict with a previous operation of another uncommitted txn will be kept in a suspended state until that operation can proceed. The control is not returned to the client until the operation proceeds. With no-wait, the user that produces a conflict with a previous operation of another uncommitted txn will see an error message immediately. Take note a conflict not only may happen while writing but while reading as well. For reading behavior, see below "read committed" under "isolation mode". For writing behavior, the response doesn't depend on the isolation mode:

• When txn A inserts a record (tuple) but doesn't commit, if txn B tries to insert a record with the same PK than A, then with WAIT, B has to wait until A commits (and then receives an error) or rolls back (and then can proceed) whereas with NO WAIT, B receives immediately an error when the insertion is posted.
• When txn A inserts a record but doesn't commit, if txn B tries to delete or update records from the same table, no problem arises, because these records from A are not seen by B until A commits and B has isolation read committed or B has a higher isolation and commits after A.
• When txn A updates a record but doesn't commit, if txn B tries to update or delete the same record or uses an update or delete statement that includes records touched by A, then with WAIT, B keeps locked until A commits or rolls back. If A commits, B should see a message saying "Deadlock - update conflicts with concurrent update" but if A rolls back, B can proceed, whereas with NO WAIT, B should see immediately a message saying "Lock conflict on no wait transaction - Deadlock".

Isolation mode: also known as isolation level, it controls the visibility of one txn with respect to the changes made by other concurrent txns. People that come from desktop databases and without acquaintance with transactions and relational concepts, often find problems understanding why they must care about this feature. There are 4 known levels:

• Read uncommitted: called Dirty Read in the BDE, it allows one txn to read all changes made by other txns to any record, even those changes that are uncommitted. This level is neither enforced nor recommended for relational engines because it allows to read inconsistent and partial information. The value of a txn is that it's visible to the rest of the txns after all changes have been made so the database is in a new consistent state and dirty read goes against that basic idea. It's typical of Paradox and Dbase. Among the relational servers, only DB2 and Informix support it. Even though in theory IB can use its MGA to offer this level, it doesn't support it. So PLEASE don't ask IBO or BDE for this level, because Interbase doesn't accept read uncommitted. 
• Read committed: called in the same way in the BDE and almost any relational server, it's the typical level of an engine. It allows a txn to read only committed changes made by other txns. This help to ensure such txn only reads consistent states of the database (of course, this assumes all clients make a set of interrelated changes inside one comprising txn). Even if another txn commits after the txn using read committed has begun, these changes can be read. This is the default level for the BDE and the recommended for interactive users. There are two sub-modes that only apply to this level:
      * Record version: the last committed version of a record is read immediately. It's the default.
      * No record version: if there's an uncommitted version of a record generated from another txn, the current txn waits until that record is committed or rolled back if blocking mode is WAIT or gives an error (exception) immediately if NO WAIT is the blocking mode. Anyway, the txn always tries to read the most recent version of a record and at the same time that version must be committed. Of course, this applies to changes made by other txns, because one txn always can read its own uncommitted changes. This sub-mode causes a lot of deadlock messages so you should use it with care.
• Snapshot: called Repeatable Read in the BDE, it allows a txn to get a snapshot of the complete database at the time it's started. This txn cannot see any change made by other concurrent txns. This level is ideal for reporting, because with such txn, a record can be read multiple times and always it will return the same value unless that txn itself changes anything. That txn only can see changes made by itself. It's not recommended for interactive users because they need timely data not frozen data. Also, it must be used for the needed operation and then it must be committed or rolled back, because it prevents garbage collection: all record versions at the time it was started must be kept to offer a steady vision of the database. At the time Delphi 3 appeared (1997), Oracle only supported this level in read only mode and Sybase and MS-SqlServer didn't support it. Probably, a "normal" relational server pays a great penalty in performance or resources to support this mode whereas IB is designed to handle such requirement so it degrades gracefully and support this snapshot isolation level in full read write mode.
• Snapshot table stability: aka Forced Repeatable Read, this level is specific to IB and doesn't exist in other relational servers and it's not supported by the BDE. Maybe it might be called Snapshot write stability, because it has the same properties than the previous level but also, as soon as it reads from a table, this table is write-locked for all other txns. So, the txn with this level takes control of that table and all other txns only can read from it. This level is not recommended for general purposes; it must be used only when really needed and for short periods of time and then committed or rolled back, because every other txn trying to write to the same tables will get an error or will be put in wait state. It's easy to get tons of conflicts or cause a general deadlock in the database. Of course, if you try to use a table that's being modified by another txn, you'll either have to wait for it to finish or you'll get an immediate error message, depending on the blocking mode specified for your txn.

To understand how Read Access and Isolation Level interacts, I've decomposed a matrix of possibilities into a list of possibilities:

• Two snapshot table stability txns only can share a table if both have READ access.
• Any number of txns can use a table provided that all of them have READ access, regardless of their isolation level.
• No txn can use a table with WRITE access if a snapshot table stability txn with WRITE access is using such table.
• A snapshot table stability txn with WRITE access cannot use a table if any txn with WRITE access is using such table (the reverse of the prior case).
• Combinations of snapshot and read committed transactions with WRITE access can share tables, but conflicts can appear when modifying data.

See blocking mode above to understand the type of messages that appear when such conflicts happen in WRITE mode.

Pre allocation mode: although SET TRANSACTION is available from SQL, DSQL and isql, this mode seems to be used mostly by embedded SQL applications. It's called "table reservation" in the official documentation. It allows finer granularity over the requested resources (in this case, tables) when the txn starts instead of waiting the txn to attempt its operations on table. This technique reduces the possibility for deadlocks. I will skip the USING form because it's available only to embedded SQL (it's not for Dynamic SQL) and specifies handles to complete databases, so it limits from the start the number of databases accessed by a txn. Regarding the RESERVING form, it's followed by a comma-separated list of tables and then the FOR reserved word and these sharing options:

• SHARED: the table(s) can be shared with other concurrent txns for read of write access.
• PROTECTED: the table(s) can't be shared with other concurrent txns for read or write access.

and these access mode options:

• READ: the txn only wants to anticipate read only usage of the table(s) according with one of the two sharing options shown above.
• WRITE: the txn wants to anticipate write usage of the tables(s) according with one of the two sharing options shown above.

so you have 4 possible combinations. After them, you can put a comma followed by a new comma-separated list of tables followed by FOR and their desired pre allocation mode and so on. Let's try to address the four combinations:

• Shared, write: permits any transaction with WRITE access mode and isolation levels of concurrency or read committed, to update, while other transactions with these isolation levels and READ access mode can read data.
• Shared, read: permits any transaction to read data, and any transaction with a WRITE access mode to update. This is the most liberal reservation mode.
• Protected, write: prevents other transactions from updating. Other transactions with isolation levels of concurrency or read committed can read data, but only this transaction can update.
• Protected, read: prevents all transactions from updating, but permits all transactions to read data.

A good way of trying different combinations and watching what happens is to launch two instances of the command line utility isql.exe and experimenting. It supports the complete syntax shown at beginning (with the exception of USING because it doesn't work in Dynamic SQL as it was explained previously) so you can play with waiting, conflicts, deadlocks and error messages.

It's worth to say that probably you don't see the behavior described in the default parameters when connected through the BDE. Probably, the BDE's Interbase driver uses "no wait" and "read committed" to behave as with other engines, for compatibility reasons.

Also, it can't be overstated to emphasize that generators fall outside transactions. They have a unique value for the database at one instant of time regardless of the isolation level of the txns using them. Also, the changes made to a generator value by means of gen_id aren't affected by either commit or rollback, so gen_id is really an atomic global call, because not only it returns the new generator value to all transactions but its invocations are serialized, to guarantee uniqueness of such generator value when used to increment or decrement the current value.

This page was last updated on 2000-10-17 02:32:08 -0400 .