InterBase server transactions and their support in FIBPlus

Source: FIBPlus Site

Modes of InterBase server transactions

Interbase supports the following isolation levels of transactions:

• READ COMMITTED RECORD_VERSION
• READ COMMITTED NO RECORD_VERSION
• SNAPSHOT
• SNAPSHOT TABLE STABILITY

SNAPSHOT isolation level

It corresponds to the REPEATABLE READ ANSI/ISO level. At the moment of the program start a transaction gets a "picture" of database. This "picture" remains the same untill the end of the transaction. Reading of data updated by the concurrent transaction is allowed but the changes are not available. Modification of data updated by another transaction causes the deadlock (Deadlock, SQLCODE = -913). Still it is not possible to change data updated by the concurrent transactions even after the end of these transactions. It happens because the "picture" no longer shows the current status of database (Deadlock. Update conflicts with concurrent update. SQLCODE = -913).

The SNAPSHOT level is the highest isolation level but it can hamper any updates in database if there are many concurrent transactions. Apply this level for identical results in identical queries to database in the frames of one transaction. In Interbase it is set by default.

READ COMMITTED RECORD_VERSION isolation level

It approximately corresponds to the READ COMMITTED ANSI/ISO level. The transaction can read changes made by other not ended transactions. Modification of data updated by another not ended transaction causes the deadlock (Deadlock, SQLCODE = -913). Unlike the READ COMMITTED ANSI/ISO level it allows to update data that are changed and committed by another transaction without finishing the first transaction. In this mode there can appear phantom strings and uncoordinated data. It is possible to apply it to queries for database that are executed only once in the course of the transaction and do not store total results.

READ COMMITTED NO RECORD_VERSION isolation level

It is an analogue of the READ COMMITTED RECORD_VERSION mode but even simple reading of data updated by another transaction causes the deadlock (Deadlock, SQLCODE = -913). After the end of the concurrent transactions it becomes possible to read and update data changed by them.

Thus the NO RECORD_VERSION mode unfits for numerous selections because records are read only until the deadlock appears.

SNAPSHOT TABLE STABILITY isolation level

In fact it blocks a table from writing. Other transactions are read only.

Correspondence of Interbase and ANSI isolation modes

Access modes

All isolation levels have additional access options: only reading (READ ONLY) or reading and writing (READ WRITE). Read only transactions are probably better because the server does not need to save all the changes in a database for finding conflicts with other transactions. The READ WRITE mode is set by default.

Interaction of different level transactions

WAIT and NO WAIT waiting modes

If there is any conflict the waiting mode allows a transacion to wait until the end of the concurrent transactions. Then it allows to make changes in a database (WAIT) or return an error code just after revealing of the conflict. Use the WAIT mode only if the isolation level allows to change records that were blocked earlier. That means that it is a READ COMMITTED level. It is useless to wait in the SNAPSHOT mode that is set by default. Besides an application that executes a query in such a mode hangs up for a period of waiting. It is recommended to use NO WAIT with a handling of an error code. The WAIT mode is set by default.

Commit and rollback of transactions

All the operations with database (including DDL commands) are executed in Interbase in the context of some transaction. Transactions can be of two types: explicit and implicit. Implicit transaction has READ WRITE WAIT SNAPSHOT parameters, starts after executing of any command and continues untill the complete end of the transaction (COMMIT, ROLLBACK). Interbase server also allows to start explicit transactions for executing of a transaction with other parameters and simultaneous executing of several transactions from one client. The following commands are used for ending a transaction: COMMIT (a confirmation of a transaction and its end), ROLLBACK (refusal from changes and the end of a transaction) and COMMIT RETAINING (a confirmation of a transaction with saving a context). The ROLLBACK RETAINING command is included into Interbase 6.0.

COMMIT RETAINING fixes a transaction but then immediately starts a new one with the same parameters as the ended transaction has. It also saves the same cursor. In that way the client's program does not need to create a cursor and execute FETCH anew.

Note: More detailed information about Interbase transactions read in Programmer's Guide and API Guide.

Using of FIBPlus components

TpFIBTransaction component

The TpFIBTransaction component controls all the transactions in the course of using FIBPlus. The components have the following key properties:

• TRParams
• TimeoutAction
• Timeout
• TPBMode

and methods:

• StartTransaction
• Commit
• CommitRetaining
• Rollback
• RollbackRetaining

A mode which runs a transaction is set in the TRParams property in the form of a list of symbol strings. Each mode option is set in a separate string without commas. More detailed information about options of transactions read in Interbase API Guide.

Besides the TRParams property a mode of transactions can be set in TPBMode. This property has several predetermined levels of isolation: tpbDefault, tpbReadCommitted, tpbRepeatableRead. If you use TPBMode different from tpbDefault then the value of the TRParams property is ignored in any case.

Access mode:

• isc_tpb_write - READ WRITE
• isc_tpb_read - READ ONLY

Waiting mode:

• isc_tpb_nowait - NO WAIT
• isc_tpb_wait - WAIT

Isolation level:

• isc_tpb_read_committed, isc_tpb_no_rec_version - READ COMMITTED NO RECORD_VERSION
• isc_tpb_read_committed, isc_tpb_rec_version - READ COMMITTED RECORD_VERSION
• isc_tpb_concurrency - SNAPSHOT
• isc_tpb_consistency - SNAPSHOT TABLE STABILITY

Note: Do not set the isc_tpb_version3 parameter as it is said in API Guide.

Use either Active or InTransaction properties to check if the transaction is active. In both cases there is used the GetInTransaction method.

Default action executing after the end of the transaction is set by the TimeoutAction property. By default TimeoutAction equals TACommit, that is confirmation of changes and cursor's closing.

The Timeout property determines an interval in milliseconds. After the interval the transaction will be ended automatically according to the TimeoutAction property. For creating a timer there is used a component of the TTimer type. If Timeout equals 0, the default action will not be executed even once.

Implicit transactions in FIBPlus

If you do not call TpFIBTransaction.StartTransaction manually but open a set of data, TpFIBDataSet checks if the poStartTransaction option is in the Options property. If it is so TpFIBDataSet automatically calls the StartTransaction method of the corresponding TpFIBTransaction component. The transaction started in such a way will not be closed at the end of the query. You should commit or rollback this transaction manually.

Explicit transactions in FIBPlus

For better control of transactions it is necessary to call TpFIBTransaction.StartTransaction. Then it becomes possible not only to commit but also cancel all the changes. Commit and Rollback finish a transaction and close all the datasets, associated with it. CommitRetaining confirms changes and starts a new transaction without changing the context. CommitRetaining also has a by-effect that is a dataset is not closed and due to this a user can continue changing of data.

Default use of a timer and an action

It is possible to use TimeoutAction and Timeout for periodical commit of changes and decrease of possible conflicts among transactions. For this set Timeout equal some not high value, e.g. 1000 and TimeoutAction := TACommitRetaining. Then TpFIBTransaction will execute COMMIT RETAINING every second.

Note:

• Do not forget about the timer in the case of explicit control of transactions with a help of StartTransaction and Commit/Rollback. It will not be possible to finish the transaction manually if the action starts by the timer.
• In bdereadme.txt of BDE 5.1 it is vaguely written "Soft commits are a feature of InterBase that let the driver retain the cursor when committing changes." A person who does not know about Soft Commit (the same is COMMIT RETAINING) can think that this mode confirms a transaction and retains an open cursor outside the transaction. But it is not so. All actions with BDE happen in the context of a transaction so COMMIT RETAINING simply clones the comitted transaction. It is naive to think (so did I) that it is possible to start a transaction, download data from a server, execute COMMIT RETAINING and then view the data in off-line mode. In the course of work with data by means of data-aware components there always exists an open transaction.
• For data editing outside the context of transactions you should use CachedUpdates mode of FIBPlus.

So do not set Timeout := 1 and TimeoutAction := TACommitRetaining in the hope of quick data downloading, ending of a transaction and further viewing of the data in the interior buffer. It will be even worse: every millisecond (actually, rarely) the program will commit changes on the server and this will cause a considerable increase of traffic.

If TimeoutAction equals TACommit or TARollback, the transaction will be finished after the first action by the timer.

Using of CachedUpdates

Setting of CachedUpdates equal True changes the ideology of program work. You can make a query, fetch all or some records into the client application, close connection to a database and edit data outside the transaction context. After changes you will be able to connect to a database again. Then it is also possible to commit the changes.