Transaction options explained

By Claudio Valderrama - Copyright www.cvalde.net

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 Firebird/InterBase^Ž 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.

Firebird/InterBase^Ž doesn't support nested transactions. Hence, the stored procedure and trigger language doesn't support transaction control commands. In Firebird/InterBase^Ž, each procedure or trigger runs in the context of the transaction initiated by the client application. However, Firebird/InterBase^Ž 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 Firebird/InterBase^Ž-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 Firebird/InterBase^Ž may seem cryptic, but this is more due to the different names than to completely different possibilities. In fact, Firebird/InterBase^Ž performs like any other relational database in the general cases. 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>]

Access mode

This is fairly obvious. A read write transaction, the default mode, can retrieve data and alter the contents of the database, provided that the user that starts the transaction has been given RW permissions (rights) over the affected objects. A read-only transaction cannot execute commands that alter contents of the database; it only can retrieve data, provided that the user that starts the transaction has been given RO permissions over the affected objects. Note that RW means insert/update/delete and RO means select/reference in this context.

Blocking mode

With WAIT, the default mode, when a conflict appears, the user that finds a conflict with a previous operation of another uncommitted transaction 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 transaction 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 transaction A inserts a record (tuple) but doesn't commit, if transaction 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 transaction A inserts a record but doesn't commit, if transaction 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 transaction A updates a record but doesn't commit, if transaction 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 transaction with respect to the changes made by other concurrent transactions. 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 transaction to read all changes made by other transactions 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 transaction is that it's visible to the rest of the transactions 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 Firebird/InterBase^Ž 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 transaction to read only committed changes made by other transactions. This help to ensure such transaction only reads consistent states of the database (of course, this assumes all clients make a set of interrelated changes inside one comprising transaction). Even if another transaction commits after the transaction 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 transaction, the current transaction 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 transaction 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 transactions, because one transaction 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 transaction to get a snapshot of the complete database at the time it's started. This transaction cannot see any change made by other concurrent transactions. This level is ideal for reporting, because with such transactions, a record can be read multiple times and always it will return the same value unless that transaction itself changes anything. That transaction 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 operation needed 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 SQL Server didn't support it. Probably, a "normal" relational server pays a great penalty in performance or resources to support this mode whereas Firebird/InterBase^Ž is designed to handle such requirements 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 Firebird/InterBase^Ž 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 transactions. So, the transaction with this level takes control of that table and all other transactions 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 transaction 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 transaction, 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 transaction.

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 transactions only can share a table if both have READ access.
Any number of transactions can use a table provided that all of them have READ access, regardless of their isolation level.
No transaction can use a table with WRITE access if a snapshot table stability transaction with WRITE access is using such table.
A snapshot table stability transaction with WRITE access cannot use a table if any transaction 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 transaction starts instead of waiting the transaction 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 transaction. 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 transactions for read of write access.
PROTECTED: the table(s) can't be shared with other concurrent transactions for read or write access.

and these access mode options:

READ: the transaction only wants to anticipate read only usage of the table(s) according with one of the two sharing options shown above.
WRITE: the transaction 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 transactions 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.

The following table lists the equivalences of the names used for different isolation levels:

API level constants & Firebird/InterBase^ŽO	Language level & tools	BDE level
(Not supported)	(Not supported)	Dirty Read
Read Committed	Read Committed	Read Committed
Concurrency	Snapshot	Repeatable Read
Consistency	Snapshot table stability	(Not supported)