This topic is based on the article written by Michael Zhuravlev (firstname.lastname@example.org).
Interbase supports the following isolation levels of transactions:
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.
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.
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.
In fact it blocks a table from writing. Other transactions are read only.
|SNAPSHOT TABLE STABILITY||isc_tpb_consistency|
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.
|isc_tpb_write||conflict is possible||-||conflict||conflict|
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.
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.
The TpFIBTransaction component controls all the transactions in the course of using FIBPlus. The components have the following key properties:
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.
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.
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.
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.
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.
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.
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.