> >Hello,
> >
> >i m in multi user database.
> >
> >I want to clean a table of all entries from one user (rec with field id_user=xxx)
> >and insert new entries, BUT i must be sure that noone is not actually doing any
> >insert on this table for this user. i can not lock the table because this will
> >affect all the users, i just need to lock all insert with id_user=xxx
> >
> >is this possible in firebird ?
> >
> >at the end yes i will lock the table but if i can avoid it ...
>
> Why do you want to 'partially lock the table', Nathan? It could be possible for you to actually allow inserts (from other users) while you do your housekeeping, each transaction can simply appear as a steady state. Let's say you start your transaction, read what you're interested in, deletes all rows for that particular id_user and 'reinsert'. Maybe you worry that between your SELECT and DELETE, another user adds one record. That's no problem with Firebird, as long as your SELECT and DELETE happen within the same transaction (and you use concurrency isolation), inserts from other users aren't at all visible - i.e. you can be assured that the DELETE will see exactly the same rows as your SELECT and not delete the intermediate insert (both the DELETE and SELECT will see changes done within their transaction, but not changes done by other, concurrent, transactions).
>
> Maybe there are other reasons for you to want to partially lock the table, e.g. if the SELECT and DELETE for some reason has to happen in separate transactions. If so, you (or this list) might have to come up with other solutions.
>
> HTH,
> Set
>
>Hello,
>
>it's when i want to reset aggregated data.
>
>I have a temp table where i add a row with +1 and -1 as Item_count_Delta everytime someone add a new item in the product table.
>
>at the end of the days i calculate the item count of all products by aggregating all rows from the temp table
>
>Now time to time i need to reset the calculation for one particular product, so i simply launch a query that count all item and set
>the item_count of the product ... but of course during this calculation, no one must be able to add (or delete) a new item to the
>product because if yes my calculation will be false ... for this i must forbid to add any row in the temp table with item related
>to the product i update

Exactly how will it falsify your calculation? Let's assume two concurrent transactions, transaction A for your housekeeping and transaction B for adding:

Transaction A (with concurrency isolation) starts.

A:
UPDATE AggregateTable AT
SET AT.MyTotalCount = AT.MyTotalCount+(SELECT SUM(TT.Item_Count_Delta)
FROM TempTable TT
WHERE TT.id_user=AT.id_user)
WHERE AT.id_user='xxx';

Transaction B starts.

B:
INSERT INTO TempTable(id_user, Item_Count_Delta)
VALUES ('xxx', +1);

Transaction B commits.

A:
DELETE FROM TempTable
WHERE id_user = 'xxx';

Transaction A commits.

After this, TempTable will still contain the one record that transaction B inserted since that record isn't visible to transaction A and hence, wasn't deleted.

HTH,
Set