Record Level Locking using TUniTable

[[email protected]]

I am porting my app from Advantage to UniDac. My app makes extensive use of 'TAdsTable' components. The code to modify a record follows the path of :-

if tblItem.FindKey(['ABC']) = True then
begin
tblItem.Edit; // Lock Placed Here.
tblItem.FieldByName('WHATEVER').AsString := 'XYZ...';
tblItem.Post; // Lock Released.
end

I have found that this approach does not work, so I tried inserting a tblItem.Lock and tblItem.UnLock however no record lock is implemented and I can update the same record consequtavly (excuse spelling!) from differant terminals at the same time. Am I missing something ?

Once I have my app converted to unidac and fully tested (comparision of Advantage DB from old app to new app), it is then my intention to move the app to firebird.

Regards
Robert.



I

[tobias_cd]

Hello Robert,
have you tried to also set the "LockMode" of the TUniTable component?
There are 3 mode available "lmPessimistic", "lmOptimistic" and "lmNone".
Though I don't know Advantage, I'm using "lmPessimistic" for Oracle so that records are locked upon Edit. "lmOptimistic" checks for locking upon Post.
In a multi-user application you then should implement some sort of exception handler to catch the specific message when a record is locked to display a nicer error message to the user instead.
You should find more info in the Unidac help under "SpecificOptions" or "Lockmode".
Regards,
Tobias

[Plash]

Record locking is not yet implemented for ODBC-based providers. We will add this functionallity in the next UniDAC build.

[[email protected]]

Hi Tobias,
Since I got you message I have been trying all weekend to get locking to work for the TUniTable components using both Advantage and Firebird 2.1.

I have tried both pessismistic and optimistic locking and every combination in between with Advantage. I then switched to firebird, tried both methods for locking and each time, except for once the locks failed. I really dont know why the target record was sucessfully locked on one occassion.

I am fairly stuck at this stage, I can send you a demo app with both the Advantage Database and a Firebird GDB if it helps to look at the problem, I am all out of ideas.

[Plash]

Record locking is not yet implemented for ODBC-based providers (including Advantage). So locking does not work with Advantage in the current build.

If you have problems with Firebird, please send to support*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.

[[email protected]]

Hi Plash,
Thanks for the info. At least from using your components I have a choice of Databases to choose from. I am trying to perform a set of tasks on both my old app (uses Advantage) and my new app so as I can check the validity of the data for the various transactions that my system supports against the the UniDac / firebird technology. DO you know if firebird supports pessimistic or optimistic locking on the UniTable component?


Robert.

[Plash]

UniDAC supports optimistic and pessimistic locking for Firebird.

[[email protected]]

Thanks for the info Plash. Could I just ask you one more question on the subject Is Record Level locking using Table components bad practice for firebird or the other supported DB's? I have been advised from a buddy that it is bad practice, will it work with transactions etc..., I suppose what I am really asking is that would you recommend this approach?

Thanks for your promt help.

Robert

[Plash]

It is bad practice to use pessimistic locking because records in database are locked for a long time.

Using optimistic locking is not bad. The record is locked only for the time of an update query execution. This locking allows you to check that another user has not modified the record since the record was fetched.

[[email protected]]

Thanks for the info Plash . I will experiment with both locking mechanisms using Firebird. Hopefully everything will go well

Robert.