Part 1 part 2 part 3


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


Working with transactions in multiple databases


InterBase and Firebird servers enable developers to use a single transaction while working with multiple databases. Two-phase commit in limbo transactions and other similar features are described in different articles about transactions, so I will not consider this topic in detail.

FIBPlus components have full support of transactions in multiple databases. Besides, the library has special components, which enable robust work with several databases. In the example below I will use the UpdateObject project (with slight changes):

There are two databases: COUNTRY.GDB and PERSON.GDB. COUNTRY.GDB has a country reference REFCOUNTRY, PERSON.GDB has the PERSON table:

CREATE TABLE PERSON (
CODPERS INTEGER NOT NULL,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(20),
COUNTRY CHAR(3),
PRIMARY KEY (CODPERS)
);

I create the GEN_PERSON generator to get a value of the artificial primary key CODPERS. This is a trigger which fills in the primary key with a generator value::

CREATE TRIGGER CREATE_PERSON FOR PERSON
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
IF (NEW.CODPERS IS NULL) THEN
NEW.CODPERS = GEN_ID(GEN_PERSON, 1);
END

The table has 22 records from a demo database EMPLOYEE.GDB.

Note: the record has a column - the COUNTRY code. The column is a foreign key, which refers to a country code in REFCOUNTRY (column CODCTR) placed in another database COUNTRY.GDB. No links between tables in different databases are possible at the data structure level described by SQL means. So I cannot write FOREIGN KEY there and use database server means for data link integrity. I also cannot write a trigger or a stored procedure to do these actions as both of them (the trigger and SP) can work only with their "native" database. So I should find my own way out.

Luckily FIBPlus has necessary means, so I will consider them now.

At first I will create a new project MultiBase. Then I'll place two DBGrid components on the form: one for the country reference and the other for the list of employees; set the Align property and put Splitter. After this I'll drop two TpFIBDatabase components, two TpFIBTransaction components (one for reading, one for writing), two datasets and two DataSource components. Then I'll set the DefaultTransaction property to ReadTransaction in both TpFIBDatabase components (DefaultTransaction is a default transaction), and the DefaultUpdateTransaction to WriteTransaction (DefaultUpdateTransaction is a default Update transaction). As a result both databases will be in the internal database list of each transaction. The transactions will start in both databases at once. If I place 10 database components on the form and set default transactions, each of them will be started in the ten databases.

To check this fact I will add another menu item: Transact Databases. Database names for both transactions can be seen on calling this item:

procedure TFormMain.MTransactDatabasesClick(Sender: TObject);
var i: integer;
S: String;
begin
S := 'WriteTransaction' + #10#13;
S := S + 'DatabaseCount: ' +
IntToStr(WriteTransaction.DatabaseCount) + #10#13;
for i := 0 to WriteTransaction.DatabaseCount - 1 do
S := S + WriteTransaction.Databases[i].Name + #10#13;
S := S+#10#13 + 'ReadTransaction' + #10#13;
S := S + 'DatabaseCount: ' +
IntToStr(ReadTransaction.DatabaseCount) + #10#13;
for i := 0 to ReadTransaction.DatabaseCount - 1 do
S := S + ReadTransaction.Databases[i].Name + #10#13;
Application.MessageBox(PChar(S), 'ReadTransaction', MB_OK);
end;

If you add new database components and set default transactions for them, you will see these databases included into the transaction list.

In the PersonData component I'll set automatic transaction start and automatic transaction commit (and I won't set these options in CountryData) writing the SQL operators. Then the components should be linked with each other by means of corresponding property values.

Let's place a menu onto the form and create two items: Commit and Rollback, which will commit the transaction and roll it back:

procedure TFormMain.MCommitClick(Sender: TObject);
begin
WriteTransaction.CommitRetaining;
PersonData.FullRefresh;
end;
procedure TFormMain.MRollbackClick(Sender: TObject);
begin
WriteTransaction.RollbackRetaining;
CountryData.FullRefresh;
end;

Commit makes sense when I am editing the country reference (on editing employees Commit is automatic). To show changes of Employees records in DBGrid I call the FullRefresh dataset method.

Similarly when I am editing countries, new (changed) records are shown in the list. On rolling back the transaction I need to read the countries anew in order to return to the initial record variant.

In the OnShow event I will write commands to connect transactions, start transactions and open both datasets. On closing the form I close the transactions, commit/roll them back and disconnect.

 8

Picture 8. Project MultiBase

Everything works well: both transactions are started for both databases. And I can view and edit both tables independently. But actually we need to create links between these tables placed in different databases.

I will use the TpFIBUpdateObject component in FIBPlus: place two TpFIBUpdateObject components (UpdateObjectEdit and UpdateObjectDelete) onto the form and use them to bring the PERSON table to conformity with REFCOUNTRY. They are responsible for foreign key functions: ON UPDATE CASCADE and ON DELETE CASCADE.

In UpdateObjectEdit I'll set the Database property to DatabaseData. This means that the SQL operator of this component works with the database defined in DatabaseData (the database PERSON.GDB) and its SQL operator will refer to the table in that database.

Then set the DataSet property to CountryData be selecting it in the pop-up list. So you have defined the dataset name, and the component will react at one of the dataset events. The dataset refers to a table placed in another database. I'll select some event from the pop-up list of the KindUpdate: ukModify property. This means that the SQL command of the component will be executed after the data in CountryData will be changed. I need to set the transaction for the WriteTransaction component. This is a transaction where the SQL command of the UpdateObjectEdit component will be executed.

Write the following statement in the SQL property:

UPDATE PERSON SET COUNTRY = :CODCTR
WHERE COUNTRY = :OLD_CODCTR

How does it work? If a user changes any string in REFCOUNTRY and commits the Update transaction (by clicking any other table record), the SQL command of the component starts. It replaces all code country values (COUNTRY) in PERSON by a new value taken from REFCOUNTRY (the parameter :CODCTR). Changes will be applied only to records which country code equals to the old country code value in REFCOUNTRY (the parameter :OLD_CODCTR).

So you have imitated the ON UPDATE CASCADE statement in the foreign key description.

In the same way set the UpdateObjectDelete properties. The only difference is that you should select the ukDelete value in the KindUpdate property and write the following code in the SQL property:

DELETE FROM PERSON
WHERE COUNTRY = :OLD_CODCTR

Select the same code as for UpdateObjectEdit in AfterExecute event handler.

Then run the application. Change any country code in the country reference and move to another string to send the changes to the server. As the transaction has not been committed, you cannot see any changes in the second table (the writing transaction sees only committed changes). Commit the transaction in the corresponding menu item. Now you will see that the country codes have changed in all necessary tables in PERSON. Delete some country (Ctrl+Del and OK in the dialog window). Commit the transaction and all PERSON records referring to this country will be deleted.

You can do as many changes and deletes as you need. The personnel list will be changed only after the transaction is committed.

So you have imitated the foreign key behaviour (one way).

Now you need to check how to add new records into PERSON correctly and change foreign key values in this table (the country code). You need to forbid changing or adding this column if a country table lacks for some record with the corresponding primary key. As the foreign key can have a NULL value, you shouldn't check Null values of the column.

Create an exception and a stored procedure to check Null values in COUNTRY.GDB:

CREATE EXCEPTION NO_COUNTRY
'There is no country with such a code in the country reference';
COMMIT;

SET TERM !! ;

CREATE PROCEDURE TEST_COUNTRY (CODCTR CHAR(3))
AS
DECLARE VARIABLE COUNTRY_NUM integer;
BEGIN
if (:CODCTR != '') then
begin
select count(*) from REFCOUNTRY where CODCTR = :CODCTR
INTO :COUNTRY_NUM;
if (:COUNTRY_NUM = 0) then
EXCEPTION NO_COUNTRY;
end
END !!
SET TERM ; !!
COMMIT;

Place two components: UpdateObject: UpdateObjectAddChild and UpdateObjectEditChild onto the form. The UpdateObjectEditChild properties are shown in the picture.

 9

Picture 9. UpdateObjectEditChild component properties

The SQL property refers to the stored procedure:

EXECUTE PROCEDURE TEST_COUNTRY (:CODCTR)

The UpdateObjectAddChild component has the same properties except for KindUpdate (it has the ukInsert value).

Write the BeforePost: event handler for the PersonData dataset:

procedure TFormMain.PersonDataBeforePost(DataSet: TdataSet);
begin
UpdateObjectAddChild.ParamByName(CODCTR).AsString :=
PersonData.FieldByName(COUNTRY).AsString;
UpdateObjectEditChild.ParamByName(CODCTR).AsString :=
PersonData.FieldByName(COUNTRY).AsString;
end;

Here we form CODCTR parameter values of UpdateObject components.

If a new record has been added to PERSON or a value of the current country code value has not been found in the country reference, the exception "There is no country with such a code in the country reference" will arise.

Run the application and in PERSON change the country code to some non existing value. You will get this exception. If you delete the country code value (which will get a NULL value), nothing will happen. This is the behaviour we wanted to have.

Note. This was only a demonstration example showing how FIBPlus can work with multiple databases. In practice you need to optimize your code and thus set more options, in particular, use other component events, write your own exception handler, etc.

Nested transactions

InterBase and Firebird have nested transactions, which are also called user savepoints.

In case you have a long transaction savepoints enable you to create points which fix the current database state. The application names these savepoints. Looking at the savepoints you can know the database state at any moment (before the transaction commit or rollback). In this case previous savepoints remain whereas the current savepoint (which was rolled back) and all consequent savepoints are cancelled.

Actually everything is simple. To create a savepoint use the SQL SAVEPOINT statement:

SAVEPOINT <identifier>;

The identifier is any correct database object name shorter than 31 symbols. If there is already a savepoint with the existing name, it will be replaced by a new savepoint. That means that the old savepoint will be deleted and a new savepoint with the existing name will be created.

FIBPlus uses the SetSavePoint: transaction component method:

SetSavePoint(<identifier>);
Use the following SQL command to roll back to a certain savepoint:
ROLLBACK [WORK] TO [SAVEPOINT] <identifier>;

FIBPlus uses the following SetSavePoint: transaction component method to roll back the savepoint:

RollBackToSavePoint(<identifier>);

To release the savepoint use SQL RELEASE SAVEPOINT:

RELEASE SAVEPOINT <identifier> [ONLY];

If you don't set the keyword ONLY, all the savepoints (starting from the defined one) will be released and rolled back.

FIBPlus uses the ReleaseSavePoint: transaction component method:

ReleaseSavePoint(<identifier>);

This method always releases all savepoints starting from the defined savepoint (this method is realized in version 6.25).

To demonstrate how to work with savepoints we will use the SavePoint example (with minor changes).

Create a new project. Place TPanel onto the form and align it to the right, it will be a toolbar. Then place a button closing the application, a pop-up ComboBox and five TButton's: to create a savepoint (see the text on the Add button), to roll back to a savepoint (Rollback), to commit the transaction (Commit), to release it (Release) and to add a savepoint with the existing name (AddExist).

Put StatusBar (to show a number of country records), DBGrid and DataSource. Add FIBPlus components to work with the database: TpFIBDatabase, TpFIBTransaction, TpFIBDataSet.

Now set SQL commands for the dataset, and define a long transaction for it (just do not set AutoCommit to True).

 10

Picture 10. Project SavePoint

COUNTRY.GDB is a demo database.

The main issue in this project is how to write handlers. At first write the SavePoint:variable in private.

SavePoint: Integer;

There will be a current savepoint number. The OnShow event handler will be:

procedure TFormMain.FormShow(Sender: TObject);
begin
Database.Connected := True;
WriteTransaction.StartTransaction;
CountryData.Open;
SavePoint := 0;
StatusBar1.Panels.Items[1].Text := IntToStr(CountryData.RecordCount);
DBGrid1.SetFocus;
end;

Just close the dataset and disconnect from the database after the application end. By default an active transaction will be rolled back. Now write the following click handler for savepoint saving:

procedure TFormMain.BSAddClick(Sender: TObject);
var NewPoint: string;
begin
SavePoint := SavePoint + 1;
NewPoint := 'SavePoint' + IntToStr(SavePoint);
WriteTransaction.SetSavePoint(NewPoint);
CSavePoints.Items.Add(NewPoint);
CSavePoints.ItemIndex := CSavePoints.Items.Count - 1;
DBGrid1.SetFocus;
end;

Here you create a savepoint name (it must be unique for the transaction execution context) and put it into ComboBox. The savepoint creation command is:

WriteTransaction.SetSavePoint(NewPoint);

The transaction can be rolled back to the savepoint selected from ComboBox by clicking the Rollback button:

procedure TFormMain.BSRollbackClick(Sender: TObject);
var NewPoint: string;
i, NewIndex: Integer;
begin
if
CSavePoints.ItemIndex < 0 then exit;
NewIndex := CSavePoints.ItemIndex - 1;
NewPoint := CSavePoints.Items.Strings[CSavePoints.ItemIndex];
WriteTransaction.RollBackToSavePoint(NewPoint);
CountryData.FullRefresh;
for i := CSavePoints.Items.Count - 1 downto NewIndex + 1 do
CSavePoints.Items.Delete(i);
CSavePoints.ItemIndex := NewIndex;
if NewIndex = -1 then CSavePoints.Clear;
SavePoint := CSavePoints.Items.Count;
StatusBar1.Panels.Items[1].Text := IntToStr(CountryData.RecordCount);
DBGrid1.SetFocus;
end;

The Rollback command is the following:

WriteTransaction.RollBackToSavePoint(NewPoint);

Other commands only put savepoint names in order.

After committing the transaction you need to put the savepoint list to the initial state:

procedure TFormMain.BSCommitClick(Sender: TObject);
begin
WriteTransaction.CommitRetaining;
CountryData.FullRefresh;
CSavePoints.Items.Clear;
CSavePoints.ItemIndex := -1;
SavePoint := 0;
DBGrid1.SetFocus;
end;

The command for the savepoint releasing is similar to the Rollback command:

procedure TFormMain.BSReleaseClick(Sender: TObject);
var NewPoint: string;
i, NewIndex: Integer;
begin
if CSavePoints.ItemIndex < 0 then exit;
NewIndex := CSavePoints.ItemIndex - 1;
NewPoint := CSavePoints.Items.Strings[CSavePoints.ItemIndex];
WriteTransaction.ReleaseSavePoint(NewPoint);
CountryData.FullRefresh;
for i := CSavePoints.Items.Count - 1 downto NewIndex + 1 do
CSavePoints.Items.Delete(i);
CSavePoints.ItemIndex := NewIndex;
if NewIndex = -1 then CSavePoints.Clear;
SavePoint := CSavePoints.Items.Count;
DBGrid1.SetFocus;
end;

In ComboBox savepoints are placed according to the time of creation, so if you need to create a savepoint with the existing name you should delete the corresponding line from the list and add the savepoint name to the end of the list:

procedure TFormMain.BSAddExistClick(Sender: TObject);
var NewPoint: string;
begin
if CSavePoints.ItemIndex < 0 then exit;
NewPoint := CSavePoints.Items.Strings[CSavePoints.ItemIndex];
CSavePoints.Items.Delete(CSavePoints.ItemIndex);
WriteTransaction.SetSavePoint(NewPoint);
CSavePoints.Items.Add(NewPoint);
CSavePoints.ItemIndex := CSavePoints.Items.Count - 1;
DBGrid1.SetFocus;
end;

And finally you should write the event handler for the line deleting to correct the number of countries shown in StatusBar:

procedure TFormMain.CountryDataAfterDelete(DataSet: TDataSet);
begin
StatusBar1.Panels.Items[1].Text := IntToStr(CountryData.RecordCount);
end;

Here you are, execute the application. Create as many savepoints as necessary, change and delete data, return to any savepoint, release savepoints, create savepoints with existing names. Everything works fine.

As a long updating transaction is used for user savepoints, use this transaction with the SNAPSHOT TABLE STABILITY isolation level exclusively.

Conclusion

In this article I have considered in detail how to use transactions in InterBase/Firebird databases and used main transaction characteristics by writing applications for data access using FIBPlus components.

The READ COMMITTED isolation level is the best for simultaneous multi-user work. The Update transactions must be short.

The SNAPSHOT isolation level is not convenient because of a high probability of lock exceptions.

If you need exclusive access to some database tables, you should use SNAPSHOT TABLE STABILITY. Only remember that on starting such a transaction and trying to open datasets you can get a lock exception in case some concurrent transaction made changes in the table without committing the transaction. If you use short Update transactions, the lock exception probability is small.

If you use separate transactions to read and write data, the reading transaction must be READ COMMITTED. The writing transaction can have any isolation level and additional characteristics depending on the task.

The most suitable variants for writing transactions are the WAIT mode together with NO RECORD_VERSION and NO WAIT together with RECORD_VERSION.

WAIT with NO RECORD_VERSION enable you to avoid the lock exception after committing the transaction or rolling it back (the exceptions will arise if the transaction tries to change a record deleted by another process). In case of using NO WAIT and RECORD_VERSION you will get the exception immediately and then decide on taking other actions.

As a rule it's not good to use NO RECORD_VERSION if you have many concurrent users. When they are actively changing data in the database you can hardly start this transaction. In case you really need to get the latest changes and data updates, then this mode suits you most of all.

If you correctly use two transactions in FIBPlus (for reading and writing data), you can considerably increase the efficiency of server resource use and almost decrease users' problems in case of locks.

Using FIBPlus protected editing mode you prevent users from changing or deleting a record which is being edited by another user.

InterBase/Firebird servers (and FIBPlus components) can start transactions for multiple databases. In this case principles of work with multiple databases do not differ much from work with one DB. It's only important to link these databases. For this purpose FIBPlus has a very handy feature: theUpdateObject component.

Nested transactions help to fragment a long updating transaction and roll back its part instead of the whole.

I have been mentioning lock probability very often. Maybe that's why you can think that it's bad and you should avoid it as much as possible. Of course it's not right. as everything depends on the task you have. You often need to lock other clients' changes in the database. In practice you can use both: hard locking means (SNAPSHOT TABLE STABILITY) and the protected editing mode. InterBase/Firebird and FIBPlus components have various means for every task requiring to working with data in the client-server application.

Translated by Marina Novikova

Part 1 part 2 part 3


Preview text: InterBase and Firebird servers enable developers to use a single transaction while working with multiple databases. Two-phase commit in limbo transactions and other similar features are described in different articles about transactions, so I will not consider this topic in detail.
Prices in Euro:

235 (1 copy)
1250 (unlimited)

Volume discounts are available...

Navigation



"FIBPlus is a great choice if you're going to work with InterBase and/or Firebird. The components let you easily use all the features of both databases in your applications...FIBPlus will make your InterBase and Firebird development easier than ever before...". Read the full story. >>

Bill Todd, president of The Database Group, Inc., a contributing editor to Delphi Informant Magazine, and a member of Team B
FOR CUSTOMERS
Download full versions and updates in your Personal Area