> >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
