Part 1 part 3 part 4


Transactions in InterBase/Firebird: how to use them in FIBPlus (part 2)



Transaction characteristics


All operations with a database (any changes of data, metadata, data selection, etc) are executed in the context of a transaction. All changes done in the context the transaction can be either committed (if there are no exceptions) or rolled back. You cannot commit the transaction if an exception error appears in any transaction operation, you can only roll back all the operations.

To start a transaction and set its characteristics the SET TRANSACTION operator is used in the SQL language. InterBase/Firebird API uses the function isc_start_transaction() and transaction parameter buffer TPB.

SQL COMMIT operator or its API equivalent isc_commit_transaction() are used for transaction committing, ROLLBACK or API isc_rollback_transaction() are used for rolling the transaction back.

FIBPlus uses corresponding API functions to work with a database server. To start a transaction with certain characteristics a developer should set the characteristics by any possible way and call the StartTransaction component or set the Active property to True.

Call the Commit method to commit the transaction and the Rollback method to roll it back. If you Commit/Rollback the transaction, all its datasets will be closed.

There are two more API functions and two FIBPlus methods of the transaction components: for committing the transaction with saving the context (the function isc_commit_retaining(), the CommitRetaining method) and for rolling the transaction back with saving the context (the function isc_rollback_retaining(), the RollbackRetaining method). These methods help developers to execute "soft" Commit/Rollback and make his life easier. Without them the programmer has to save the position of the current record (usually the primary key value is saved), restart the transaction, open a dataset and go to a necessary record. Using CommitRetaining and RollbackRetaining developers avoid doing these operations, but more server resources are used (it's a drawback). Besides in SNAPSHOT isolation level after "soft" Commit/Rollback the transaction won't see any changes executed by other processes.

InterBase/Firebird and FIBPlus also enable to create transaction save points and rollback to save points. I will consider these powerful features later.

To set characteristics from the transaction parameter buffer (TPB) mnemonic constants are used. Unfortunately constant names do not often correspond to SET TRANSACTION syntax elements.

Here is a simplified SET TRANSACTION syntax:

SET TRANSACTION
[READ WRITE | READ ONLY] /* access mode */
[WAIT | NO WAIT] /* permission locking mode */
[[ISOLATION LEVEL] /* isolation level */
{SNAPSHOT |
SNAPSHOT TABLE STABILITY |
READ COMMITTED [{RECORD_VERSION |
NO RECORD_VERSION}]]
[RESERVING <reserving clause>]

RESERVING clause sets the table reservation. The RESERVING clause syntax is:

<table> [, <table> ...]
[FOR [SHARED | PROTECTED] {READ | WRITE}]
[, <reserving clause> ...]

Default transaction value is (if there are no defined characteristics SET TRANSACTION or the transaction parameter buffer is empty):

SET TRANSACTION READ WRITE WAIT SNAPSHOT;

These default characteristics correspond to these TPB parameters:

isc_tpb_concurrency
isc_tpb_write
isc_tpb_wait

Using FIBPlus components you can form the transaction parameter buffer by placing a list of mnemonic constants defined in the module ibase.pas to the TRParams property of TpFIBTransaction.

The simplest characteristic is access mode. READ WRITE (isc_tpb_write TPB) enables developers to read and modify database data in the context of this transaction. READ ONLY (isc_tpb_read TPB) provides them with read only data operations.

The most important transaction characteristic is isolation level. The table below demonstrates three possible isolation levels.

Transaction isolation levels in InterBase/Firebird.

SQL TPB Constant Value
READ COMMITTED isc_tpb_read_committed Reading of committed changes. The transaction can see the latest committed database changes done by other transactions.

This isolation level has two opposite parameters:

Default NO RECORD_VERSION (isc_tpb_no_rec_version in TPB) requires Commit of all data changed by other transactions.

RECORD_VERSION (isc_tpb_rec_version in TPB) enables you to read the latest version of committed changes, even if there are other uncommitted changes.

SNAPSHOT isc_tpb_concurrency Snapshot is a default setting; its other name is Repeatable Read. It shows the database snapshot before the transaction start. This transaction sees no changes executed by other transactions, only its own changes.
SNAPSHOT TABLE STABILITY isc_tpb_consistency It is an isolated snapshot, Serializable. It is similar to SNAPSHOT, the only difference is that other transactions can read data from tables of this transaction, but they cannot make any changes.

Besides the isolation levels listed in the tables there is another level Dirty Read or Read Uncommitted, described in books on databases. This isolation level helps to read uncommitted changes done by other transactions. InterBase and Firebird do not support Dirty Read.

One more important transaction characteristic is the locking permission mode. If WAIT (isc_tpb_wait) is set to True and a conflict arises, the transaction will wait for locking permission from other transactions by giving them Commit or Rollback operators. If NO WAIT (isc_tpb_nowait) is set to True, in case of locking this transaction shows an exception and forms code values. WAIT is set by default.

I will provide you with more details about reserving in the corresponding part of the article.

If you set the TPBMode transaction property to tpbReadCommitted (READ COMMITTED isolation level; this value is set for the transaction when you put a component onto the form), after the transaction start this property will have the following constants (regardless the values you define in TRParams):

isc_tpb_write
isc_tpb_nowait
isc_tpb_rec_version
isc_tpb_read_committed

Similar, if you set TPBMode to tpbRepeatableRead (SNAPSHOT isolation level), TRParams will have:

isc_tpb_write
isc_tpb_nowait
isc_tpb_rec_version

Besides this will also be a default setting:

isc_tpb_concurrency

So if you want to manage transaction characteristics directly from the application, set tpbDefault to True in TPBMode.

Note: the first parameter sent to TPB is always isc_tpb_version3, which sets the transaction version. You do not need to send this parameter when using FIBPlus, because it is formed and sent to API function isc_start_transaction() automatically. Working with the application click ParamTransact and you will see the form with both mnemonic names of transaction parameters (which you have set in TRParams) and real numbers in TPB. Always the first number will be 3 - it is the value of isc_tpb_version3 parameter.

Here are some numeric parameter values used to form TPB. I will not consider several of them in this article. The parameters are defined in ibase.pas.

isc_tpb_version1 = 1;
isc_tpb_version3 = 3;
isc_tpb_consistency = 1;
isc_tpb_concurrency = 2;
isc_tpb_shared = 3;
isc_tpb_protected = 4;
isc_tpb_exclusive = 5;
isc_tpb_wait = 6;
isc_tpb_nowait = 7;
isc_tpb_read = 8;
isc_tpb_write = 9;
isc_tpb_lock_read = 10;
isc_tpb_lock_write = 11;
isc_tpb_verb_time = 12;
isc_tpb_commit_time = 13;
isc_tpb_ignore_limbo = 14;
isc_tpb_read_committed = 15;
isc_tpb_autocommit = 16;
isc_tpb_rec_version = 17;
isc_tpb_no_rec_version = 18;
isc_tpb_restart_requests = 19;
isc_tpb_no_auto_undo = 20;
isc_tpb_last_tpb_constant = isc_tpb_no_auto_undo;

Experimenting with transaction characteristics

Let's start experiments with transaction characteristics. Notice that the program has so called "long" transaction: a user manually starts the transaction, does different data changes and commits/rolls the transaction back whenever necessary. This may cause locking. An example of a "short" transaction is: the user presses OK on the data adding/editing form; the application calls Insert or Edit for the corresponding dataset; sets column values and calls the Post method, which sends changes to the database; Update transaction is started on calling Post; after Post the transaction is committed. As a rule such a short transaction lives less than a millisecond, thus less locking possibility occurs in multi-user database environment. I would like to investigate on which conditions a locking occurs, so I will use long transactions.

READ COMMITTED isolation level

READ COMMITTED isolation level is used most often. It enables transactions to read committed changes executed by other transactions. I will demonstrate you how to use this level. Run two copies of the test application. Set the following transaction characteristics clicking the Characteristics button in the first application:

isc_tpb_write
isc_tpb_read_committed
isc_tpb_rec_version
isc_tpb_nowait

This means that the transaction can read and write data (isc_tpb_write) and has the isolation level READ COMMITTED (isc_tpb_read_committed), i.e. the transaction reads all committed changes done in other transactions. The transaction will be "happy" with any latest committed version of the record, even if there other uncommitted version of the same record (isc_tpb_rec_version). If a locking conflict occurs the transaction won't wait for the conflict solution from another transaction but shows an exception (isc_tpb_nowait).

Set the following characteristics for the second transaction:

isc_tpb_write
isc_tpb_read_committed
isc_tpb_rec_version
isc_tpb_wait

The only difference from the first transaction is the last string. In case of the locking conflict the second transaction will wait for the solution from another transaction.

In any isolation levels the locking conflict always occurs when two transactions try to edit the same able record.

Ready? Now I will create conflict situations and solve them.

Start a transaction in the first application, open a dataset and change some column value in DBGridCountry in the region reference. Then after making the changes click/navigate to any other table record and make it current. The Post operation will send the changes to the database.

Do the same sequence of actions in the second application: start the transaction, open the dataset, change the same record (as in the first application), you can change the value of any record column. Make another record current to send the changes to the database.

What has happened? The second application is out of order: it's good if you can see the mouse cursor, in some cases it's not seen at all. The point is that you have set (isc_tpb_wait) mode for the second application (it is waiting for the locking conflict solution). Click to commit the transaction in the first table, the second application will suppress a corresponding error message (your event handler has done this).

Repeat the same actions: change a record in the first program, change the same record in the second application. The second program will start waiting. Roll back the transaction in the first program, and the changes from the second program will be sent to the database without errors.

Conclusion 1. Do not use isc_tpb_wait mode when using long transactions.

Change the waiting mode in the second application: remove the isc_tpb_wait parameter from the transaction characteristics and add isc_tpb_nowait.

Note! isc_tpb_wait is used by default. You should always set isc_tpb_nowait explicitly.

A small example from my practice: when I was preparing a demo example to show how short transactions behave well in multi-user environment (see "Advantages of using FIBPlus components"), I made a simple mistake and created a long transaction instead (I had not set values of two properties). 11 students of mine changed the same record on client machines and went to another record to send the changes to the server. If I had had a short transaction, the locking could have occurred only on a couple of machines. In my case the long transaction caused an error exception at ten computers and only the 11th could update the changes well.

Now I will show another conflict. Delete USA record from the first application by pressing Ctrl+Del; confirm the delete operation in the standard dialog form. Do not commit the transaction. Delete the same USA record from the second program. You will get the conflict. Roll back the transaction in both cases. The deleted record will appear in the list again. Again delete it from application 1. Try to change or delete any record in region tables (DBGridRegion) of the same country in the second program. Another conflict occurs. It will also occur when you try to change the key column (country code) in the first program and then to change/delete the detail region record in the second. In the region table the CodCtr column is a foreign key, which refers to the country code in the country table. The foreign key description is:

CONSTRAINT "Region_FOREIGN_KEY"
FOREIGN KEY (CodCtr) REFERENCES REFCOUNTRY (CodCtr)
ON DELETE CASCADE
ON UPDATE CASCADE

This means that on changing the primary key value of the master table the detail foreign key value will be also changed.

In other words conflicts will always occur when you try to change (edit or delete) any record of any table (master or detail) involved into the changing process in another transaction.

Note. Notice that the restriction name is set explicitly in the foreign key description. Actually it is always good to name all column and table restrictions, as you will get a more clear error message from the database server in case of the restriction violation.

Now let's do another experiment. Close the dataset in the second program. Change a record without committing the transaction in the first program. Open the dataset in program 1. As you see everything is ok, the second transaction sees the old unchanged record version. Now change characteristics of the second transactions. Remove its isc_tpb_rec_version parameter and set it to isc_tpb_no_rec_version. Start the transaction in the second program and try to open the dataset. You will get an exception.

It happens because of the isc_tpb_no_rec_version parameter. It requires committing of all changes of data used in tables of your transaction and modified by other transactions. The case is even worse: if you change a record in the first program without committing the transaction, roll back the transaction in the second application and then try to reopen the dataset, you will also get an exception.

Cconclusion 2. Do not use isc_tpb_no_rec_version together with isc_tpb_nowait if you have long transactions or many clients who work with you database.

Note! The isc_tpb_no_rec_version parameter is used by default. You should set isc_tpb_rec_version explicitly.

Now try to set isc_tpb_no_rec_version together with isc_tpb_wait, and you will get very interesting results. Of course you remember that if you set isc_tpb_rec_version with isc_tpb_wait, in case of conflict the second program will be in the waiting mode; if the transaction is committed in the first program, the second will show an exception.

If you set isc_tpb_no_rec_version and isc_tpb_wait, the second program shows an exception neither after rolling back the transaction nor after committing it in the first program. This feature enables to considerably decrease or even avoid locking conflicts in a multi-user environment. I will demonstrate you in more detail how to use such transaction characteristics when describing separate transactions.

Let's have a look at how to set the isc_tpb_read parameter. Set it for a transaction after removing isc_tpb_write and try to change the data. You will get an exception. The transaction with isc_tpb_read really does not allow you to do data changes.

It's time to show dead lock (mutual lock) situation: both programs will be waiting for Commit/Rollback from each other.

Set the following transaction characteristics in both applications:

isc_tpb_write
isc_tpb_read_committed
isc_tpb_wait
isc_tpb_rec_version

Star the transactions and open datasets. Change a record in application 1 and another (different) record in application 2. Then in application 2 try to change the same record, which was modified in the first program. At this moment application 2 will be in the waiting mode as it has isc_tpb_wait transaction parameter (which waits for locking conflict solution from another transaction). Now in application 1 change the record, which was modified by the second application. The first application also starts waiting and deadlock occurs, both applications are waiting for each other.

Actually nothing serious has happened. In a few seconds the first application will show an exception message: "deadlock. update conflicts with concurrent update". The database server has features responsible for mutual locks. Lock Manager analyses locks not permanently but from time to time (in a certain period of time) to increase application performance. The interval for Lock Manager is set by the DeadlockTimeout parameter in firebird.conf for Firebird 1.5 or by DEADLOCK_TIMEOUT in ibconfig for InterBase. The default interval value is 10 seconds.

Now do the last experiment. Roll back both transactions and reopen the datasets in both programs. Change a record in the first program and commit the transaction. Change the same record in the second application and also do Commit. This operation is a success. Now reopen the dataset in both applications. The database keeps changes only of the last operation.

Reminding. To make the transaction with READ COMMITTED see committed changes of other transactions you only need to reopen the dataset (FIBPlus usually uses the FullRefresh method). For this you don't need to stop and restart the transaction.

SNAPSHOT isolation level

SNAPSHOT isolation level is set by default. It helps to read unchanged database state on transaction starting. In this transaction you cannot see changes done by other transactions; this transaction sees only its own changes.

It the first application set the following parameters for READ COMMITTED isolation level:

isc_tpb_write
isc_tpb_read_committed
isc_tpb_rec_version
isc_tpb_nowait
For the second application set:
isc_tpb_write
isc_tpb_concurrency
isc_tpb_nowait

Change a record in the first program without committing the transaction. Try to modify the same record in application 2 and you will certainly get the lock exception. Now change any record in application 1 and commit the transaction. Close the dataset in application 2 and reopen it. The second application with SNAPSHOT does not see modifications committed in the other transaction. That's correct, because SNAPSHOT isolation level shows the database snapshot made at the transaction start, and this snapshot cannot be changed by any concurrent transactions. Changes made by other transactions can be seen in application 2 after you stop the transaction, restart it and open the dataset.

One more interesting test: modify any record and commit the transaction in the first program. Try to change the same (committed!) record in the second application (it does not see changes of the first program).You will also get an exception. I suspect many users will be surprised by such application behaviour.

This is almost everything I wanted to tell about SNAPSHOT isolation level. Use it when you either want to know nothing about the external situation (which changes were made after your transaction started) or want to get uncontrolled exceptions.

SNAPSHOT TABLE STABILITY isolation level

It is an isolated, serializable snapshot. It is almost similar to SNAPSHOT, the only difference is that other transactions regardless their isolation levels can only read data from tables of this transaction without modifying them.

Modify transaction characteristics in the second application; make the following list of parameters:

isc_tpb_write
isc_tpb_nowait
isc_tpb_consistency
Start the transaction and open the dataset.

In the first application try to change or delete data in master or detail table and you will get an exception. SNAPSHOT TABLE STABILITY enables you to work with the table on your own; nobody else can do the same simultaneously with you. There is one not obvious problem: if the transaction with SNAPSHOT TABLE STABILITY is started and another transaction has already changed the data without committing them, dataset opening will cause an exception.

Let's check this in practice. Stop the transaction in application 2. Change a record in the country table in the first application. Now you can do this because the second transaction is not active. Then start the transaction is application 2 and try to open the dataset. You will see an exception message "lock conflict on no wait transaction". Stop the transaction in the second application, commit/rollback the changes in the first program and open the dataset in the second program.

See what will happen when after seeing this exception at first you commit changes in the first program and then click OK in the exception message of the second application. You will see the dataset without changes, executed and not committed by the first application at the moment of the second application start! As you understand, you should be very cautious when writing event handlers.

Part 1 part 3 part 4


Preview text: 

All operations with a database (any changes of data, metadata, data selection, etc) are executed in the context of a transaction. All changes done in the context the transaction can be either committed (if there are no exceptions) or rolled back. You cannot commit the transaction if an exception error appears in any transaction operation, you can only roll back all the operations.


Prices in Euro:

235 (1 copy)
1250 (unlimited)

Volume discounts are available...

Navigation



Well, the ballots are in and the votes for the 2003 Delphi Informant Readers Choice Awards have been tallied - Congratulations Devrace, you are the runner-up in Database Connectivity category! >>

Debbie Holmgren, VP of Sales, Delphi Informant Magazine
FOR CUSTOMERS
Download full versions and updates in your Personal Area