Part 1 part 2 part 4


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


How to reserve tables


SNAPSHOT TABLE STABILITY isolation level reserves tables used in such transaction. There additional features enabling you to reserve tables in the transaction or vice versa to allow other transactions modify tables in SNAPSHOT TABLE STABILITY isolation level. In the SQL language it is a reserving operator, in TPB these are constants isc_tpb_lock_read, isc_tpb_lock_write, isc_tpb_exclusive, isc_tpb_shared, isc_tpb_protected. Here are examples on how to use them in practice.

Editing tables in READ COMMITTED isolation level

Run two copies of the application. In the first application set the following transaction parameters:
isc_tpb_write
isc_tpb_read_committed
isc_tpb_nowait
isc_tpb_rec_version
isc_tpb_lock_write=REFCOUNTRY
isc_tpb_protected

In SQL it will be:

SET TRANSACTION
READ COMMITTED READ WRITE
NO WAIT
RECORD_VERSION
RESERVING REFCOUNTRY FOR PROTECTED WRITE;

Note! The isc_tpb_protected parameter must be just after the name of the reserved table.

For the second application set a standard READ COMMITTED transaction:

isc_tpb_write
isc_tpb_read_committed
isc_tpb_nowait
isc_tpb_rec_version

Start the transactions and open datasets in both applications. Then try to change any record in the second application. You will get a lock exception because REFCOUNTRY is reserved.

Now stop the transaction in the applications and close the datasets. Start the transaction in application 1 without opening the dataset. Start the transaction in application 2, open the dataset and try to modify it. Again an exception appears. You can see that the tables are being reserved on the transaction start, not on the dataset opening (like in SNAPSHOT TABLE STABILITY).

In the first transaction replace isc_tpb_protected by isc_tpb_exclusive. The result will be the same. Change isc_tpb_lock_write=REFCOUNTRY to isc_tpb_lock_read=REFCOUNTRY, the result is the same again. If you set the isc_tpb_shared parameter, no reserving will be in READ COMMITTED isolation level.

The same results will be obtained if you remove SNAPSHOT and SNAPSHOT TABLE STABILITY isolation levels in the second application. In the last case you also won't be able to modify the data in application 1.

So reserving for READ COMMITTED transaction helps to prevent the reserved table from changes by another transaction with any isolation level.

Reserving tables in SNAPSHOT isolation level

Set these transaction characteristics for the first application:

isc_tpb_write
isc_tpb_concurrency
isc_tpb_nowait
isc_tpb_lock_read=REFCOUNTRY
isc_tpb_exclusive

Do the same tests as with READ COMMITTED. You will get absolutely similar results.

Reserving tables in SNAPSHOT TABLE STABILITY isolation level

SNAPSHOT TABLE STABILITY isolation level does not allow other transactions to modify data in used tables. But reserving means can allow changes for certain tables. Set the transaction characteristics in the first program:

isc_tpb_write
isc_tpb_nowait
isc_tpb_lock_write=REFCOUNTRY
isc_tpb_shared
isc_tpb_consistency

If the second transaction is READ COMMITTED or SNAPSHOT, it can change data in the defined table. But of course it's still not possible to change data in the same record simultaneously.

Start the transactions and open the datasets. You can modify the table with (isc_tpb_shared) in the second application.

Reminding. Parameters isc_tpb_shared, isc_tpb_exclusive and isc_tpb_protected must be placed just after isc_tpb_lock_write (isc_tpb_lock_read). Any table name must be mentioned in reserving operators not more than once.

Using separate transactions

FIBPlus components enable developers to use two transactions for work with datasets: one for reading and the other for updating/writing. I will demonstrate you which transaction characteristics are possible.

Create a new project Transaction2 based on the current project. Make some changes. Add another transaction component and name it Transaction2. It will be an updating/writing transaction. Set Transaction2 as updating in the database component and in both datasets (the DefaultUpdateTransaction property in Database; UpdateTransaction in datasets). Set AutoCommit and poStartTransaction (in Options) to True in the datasets. It is a short updating transaction. It will be started every time after Post (when the application sends changes to the database) and automatically committed in case of no exceptions.

Set TPBMode to tpbDefault in the second transaction and the following values in TRParams window:

isc_tpb_write
isc_tpb_read_committed
isc_tpb_nowait
isc_tpb_rec_version

Note. This slightly modified test application is shown in the project Transaction2.

Run the application and set characteristics for the first transaction:

isc_tpb_read
isc_tpb_concurrency
isc_tpb_nowait

These characteristics correspond to the SNAPSHOT isolation level. Start the transaction, open the dataset and change any record. Click any other record to do Post and Commit for the modified record. The record will still have the old unchanged column value! That's correct; the reading transaction Transaction1 sees no changes executed by the updating transaction Transaction2 in the same application. Even if you close the dataset and reopen it, nothing will change. You need to stop the transaction, restart it and open the dataset, and only then you will see a new changed column value. The reading transaction sees no changes done by the other transaction even if both of them are in the same application.

I have demonstrated you a bad variant, but it can be even worse. Create the following characteristics for the reading transaction:

isc_tpb_read
isc_tpb_nowait
isc_tpb_consistency

The reading transaction will have SNAPSHOT TABLE STABILITY isolation level, so other transaction cannot change its tables. Start the transaction, reopen the dataset and try to change a record. You will see lock conflict.

Of course these are normal parameters for the reading transaction:

isc_tpb_read
isc_tpb_nowait
isc_tpb_read_committed
isc_tpb_rec_version

Besides the reading transaction must do no table reserving. As for the updating/writing transaction, you can choose any necessary isolation level and additional characteristics.

If you have a separate long reading transaction (with READ ONLY or isc_tpb_read mode), you will considerably save database server resources.

Advantages of using FIBPlus components

You have spent much time and many efforts to get locks in applications which work with the same database. Now I will consider all FIBPlus advantages to help you to create "correct" programs and decrease lock possibility.

Using separate transactions correctly

Run the project Transaction2. Set these parameters for the reading transaction (Transaction1):
isc_tpb_read
isc_tpb_read_committed
isc_tpb_nowait
isc_tpb_rec_version

This is a long read-only transaction with READ COMMITTED. One of its advantages is minimal use of server resources.

The updating transaction (Transaction2) is short. It is started on calling the Post method, which posts changes to the server, and it is committed automatically after Post (if no Update exceptions occur). You can hook the transaction start and commit by writing event handlers for Transaction2 transaction component: AfterStart or BeforeStart to hook the start and AfterEnd (BeforeEnd) to close the transaction. The test application will show corresponding messages for these events (you can hide comments in real applications).

Run the second application and set the same parameters for the reading transaction. Now try to get a lock by changing the same record in both applications. Most likely you won't be able to get a lock on one local machine due to the short update transaction. Many times 11 students and I practiced a lot, trying to simultaneously change the same record in the server database in the local network and to move to a new record together to commit the transaction. In all the cases we got from 0 to 3 lock conflicts.

You can absolutely avoid update conflicts when using separate transactions. Set these parameters for the updating transaction:

isc_tpb_write
isc_tpb_read_committed
isc_tpb_wait
isc_tpb_no_rec_version

In case of lock conflict with the concurrent process the transaction will be in the waiting mode. It won't last for a long time as the concurrent updating transaction is short. After the concurrent transaction is rolled back/committed, our process will be executed. The database will keep only the last data modification. We checked this situation on the 11 computers in the network and got no conflict.

It's up to you to decide whether it is good or bad, it depends on a certain task you need to do.

Note. Of course if the conflict has occurred when one process deleted a record and the other tried to change this deleted record, the exception message will be shown.

So if you know how to use separate transactions correctly, you will have far fewer lock exceptions.

Using poProtectedEdit mode

Now return to the first project. Set poProtectedEdit to True in the Option property in the DataSetCountry dataset. This will help you to lock a record which will be modified.

Run the two programs. Set READ COMMITTED isolation level for both transactions. Try to change a record in the first application without committing the transaction. Then modify the same record in the second application.

You will certainly get a lock conflict, but the exception message will appear at once, when you try to commit at least a record symbol, not after the second application tries to commit the transaction (as you've seen in our first experiments). Due to this feature you can avoid the following annoying situation: a user is modifying a database record with many columns for a long time, tries to send the changes to the database and suddenly gets to know that the record could not be modified as another user has been modifying the same record.

Due to the mechanism of dummy updates FIBPlus helps you to avoid this situation: to reserve only one record and forbid its concurrent changes, FIBPlus shows the UPDATE operator. It changes nothing in the record (i.e. sets the same value to one column). The server creates a new record version which does not differ from the last committed version and locks this record. In this case concurrent users cannot change this record until the transaction is committed.

Note. In this way you can lock as many records as necessary. When you start changing a new record in the context of one transaction, this record becomes protected/locked. There are no other ways to cancel the record locking but to commit/roll back the transaction.

Part 1 part 2 part 4


Preview text: 

SNAPSHOT TABLE STABILITY isolation level reserves tables used in such transaction. There additional features enabling you to reserve tables in the transaction or vice versa to allow other transactions modify tables in SNAPSHOT TABLE STABILITY isolation level. In the SQL language it is a reserving operator, in TPB these are constants isc_tpb_lock_read, isc_tpb_lock_write, isc_tpb_exclusive, isc_tpb_shared, isc_tpb_protected. Here are examples on how to use them in practice.


Prices in Euro:

235 (1 copy)
1250 (unlimited)

Volume discounts are available...

Navigation



FIBPlus has taken the headache out of this project. When I got the current contract, FIBPlus was included on the work computer from the client. I quickly found it easier to use and more powerful than any other Firebird component suite I had tested. When I ran into a problem setting up a persistent calculated field, the tech support people saved the day. I would also add that I tested Zeos, IBX, DBGo, UIB, and other FBconnectors.  FIBPlus outperformed all of them, and FIBPlus was run on an older machine than the others were.  That was most impressive.

>>

Frank Luke, Big Creek software, LLC
FOR CUSTOMERS
Download full versions and updates in your Personal Area