--- In firebird-support@yahoogroups.com,
franciscojose.ruedacastanon@t... wrote:

> Let me explain what I'm trying to achieve; I'm new to Firebird and
> I've not very clear this point.
>
> I have several tables that gather information from the main

tables of

> database when a report is launched. In this process, first all the

records

> are deleted, then the new ones are inserted and finally several

fields are

> updated from other tables. Each of these actions is performed inside a
> transaction (so, there are three transactions). My doubt is: if

during this

> whole process another user launches the same report with other

parameters,

> what will be the final state of the table? Maybe there will be a

"mix" of

> records of the several requests? So the idea is to isolate the whole
> process of generating the report when the first user launches it and to
> "lock" the other users while the process is running.

In general this is not good approach for FB - if there are many
records deleted/inserted you can have problems with garbage
collection. Have'nt you tried to build report "on the fly", without
this intermediate table, using selectable stored procedures? But,
don't knowing exactly what you are doing, I will not insist this is
the only solution, sometimes such a "temporary" table is the only way.
So, what you can do to isolate users.

Each application should perform delete not before, but after work is
done, to avoid growth of this table.

1. Include Current_User or Current_Connection into Primary Key of this
table and use it in all statements - inserting records and in Where
when selecting and updating, this will efficiently isolate users.

2. Are you sure you need separate transaction for each step? If you
will perform all in one transaction, each transaction will see only
own data in the table. But if this lead to very long transactions
lifetime and very large volume of changes in transaction, this can
lead to performance degradation. What is "long" and "large" - you
should determine experimentally for your application.

3. If you still want to provide only one-at-time report building, you
should study consistency isolation level in API Guide. Again, this
is'nt natural for FB mode. I would recommend it only for periodical
profilactic table cleanup from possible old records which can remain
in the table if you will use separate transactions for inserts and
deletes and application will occasianally be aborted before cleanup
own records.

Best regards,
Alexander.