--- In firebird-support@yahoogroups.com, Jacob Havkrog <jacobh@...> wrote:

>
> Hi â€" I’m new to Firebird and transactions. I’m migrating from BDE/Paradox where the transaction support is very basic. I have a question about how to use transaction in a special situation. What kinds of transaction to use etc…
>
> I need to keep 2 versions of my code running for a while, both a BDE version and a FB version. So I need to find solutions to migration problems that don’t involve major redesigns. After the migration is fully completed and we can retire our last BDE installation I’ll be able to make redesigns and to do things the correct Firebird way.
>
> OK â€" my problem is like this:
>
> It’s a multi-user application where multiple clients might work on the same data at the same time. The kind of data involved in this situation is (simplified) person data like name, address, phone etc store in a table I call Person.
>
> After making changes to some Person data, upon closing a form, changes in Person data are reported in a kind of log table I call History data.
>
> The user might make several changes to a Person record, but for each changed person, only one new History record is created with a time stamp and some status fields about the person.
>
> In order to prevent several clients to update the History table at the same time, I’ve implemented a Semaphore, which BDE actually enables one to do: Just open a dedicated table with exclusive access. If this fails, the semaphore is raised/signaled. Otherwise you know you’ve got exclusive access until you close the table or the application quits.
>
> Now â€" there is no way to emulate this kind of thing (semaphore or critical section) using Firebird? I guess not as I haven’t been able to find any. And I understand, that I need to change my thinking and use Transactions instead!
>
> So my question is â€" how do I set this up, so updating my History table is serialized. Only one client at a time doing it?
>
> During the updating of the History table, the Person table is written to too in order to remove flags on changed person records that indicate that there is a change that needs to be written to the History table.
>
> So updating history must be contained in a Transaction. I wouldn’t mind if a second user trying to update history at the same time is simply denied access. In this situation she can just wait. But the Person table shouldn’t be locked so normal work on it, like updating person records is prevented while the history updating is taking place.
>
>
> I guess a better approach would be to use triggers so a change to a person field instantly causes a new record in the history table. But isn’t there a quick way to get my old approach to work?
>
> Thanks a lot!
> Jacob
>
>
>
> [Non-text portions of this message have been removed]
>

I am doing it the following way.

1) Add a field in the person table I called "INUSEBY". Before editing check the field, if field is null edit is allowed and set "INUSEBY" to the user wanting to edit with a procedure that looks like this. "UPDATE PERSON SET INUSEBY = "USERANME" where ...... AND INUSEBY IS NULL. Then check if INSUSEBY field has changed to "USERNAME". If so allow editing, else ... repeat

2) After editing, start a transaction, post the changes, post the updates history in "after insert / update triggers" and set INUSEBY back to NULL. Commit transaction, if fail then do error handling, but finally set INUSEBY to null.

3) The disadvantages is the user program or computer fail, the INUSEBY is never reset to null. (I have a manual procedure to do this and also when my user exit or logoff my app, i run a procedure that set INUSEBY to null WHERE INUSEBY = USERNAME), but on the plus side other users know who is editing and locking the record. And you have no transaction running for long period because the user start editing but forgot to post the changes.

Regards



Cornie