On Thu, 20 Oct 2011 11:04:33 -0000, "canacourse" <canacourse@...>
wrote:

> I'm using a firebird embedded 2.5.0.26074 database via latest ado

provider

> which contains a simple table
>
> CREATE TABLE INVENTORY (
> ID ID NOT NULL /* ID = VARCHAR(36) NOT NULL */,
> EXPIRYTIME EXPIRYTIME NOT NULL /* EXPIRYTIME = BIGINT NOT NULL

*/,

> ITEMSIZE ITEMSIZE /* ITEMSIZE = BIGINT NOT NULL */,
> ACCESSCOUNT ACCESSCOUNT DEFAULT 1 NOT NULL /* ACCESSCOUNT =
> INTEGER DEFAULT 1 NOT NULL */,
> LASTACCESSTIME LASTACCESSTIME /* LASTACCESSTIME = TIMESTAMP NOT

NULL

> */
> );
>
> To tally the sum of all ITEMSIZE rows two triggers where created on the
> INVENTORY table to add or subtract the value in ITEMSIZE to a TOTALSIZE
> Column (Single row) in table STATS as a row was added or removed.
>
> CREATE TABLE STATS (
> INSTANCE SMALLINT,
> SIZE BIGINT DEFAULT 0);
>
> This did not work as deadlock exceptions kept occurring in the triggers.

> Someone suggested using a view instead to get the sum of all ITEMSIZE
> records.
>
> CREATE OR ALTER VIEW SIZEVIEW(ITEMSIZE) AS SELECT
> CAST(COALESCE(SUM(inventory.itemsize), 0) AS BIGINT) FROM INVENTORY;
>
> This did not work either. The deadlock errors still occur when using

this

> view as follows "SELECT * FROM INVENTORY". Lastly I tried different
> isolation levels IsolationLevel.ReadCommitted, ReadUncommitted,
> IsolationLevel.Snapshot but still not joy.
>
> this is the calling code

...

> Have also tried IsolationLevel.ReadCommitted, ReadUncommitted
>
> How can I read the sum of itemsize while items are being removed & and
> added concurrently? It is not critical that this value is 100% correct

but

> I do need to be able to read it reliably.

These problems are usually easier solved by using a record per change with
a +1 or -1, and occassionaly (daily, weekly) processing the entire table to
sum everything up and have only one record again. Next change will then
again a +1 or -1 record and you query the sum total.

So you would have something like:
ITEM COUNT
item1 10
item2 10
item1 1
item2 -1
item2 -1
item1 -1

and after the scheduled merge you get:
ITEM COUNT
item1 10
item2 8

You can then add a view which simply sums the records per item.

PS I also added this to your Stack Exchange post