On Thu, 20 Oct 2011 11:04:33 -0000, "canacourse" <[email protected]> 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
