Subject Re: [IBO] Lock Conflict Problem
Author Lester Caine
Metin Gönen wrote:

> At the end I decided to use master detail tables. I deleted the triggers.
> Now the master table shows the necessary details of stocks records and the
> detail table sums up the averages and the total ins and outs. but actually
> this is not the way I want. As I told before in this way I can't show all
> the results in one line as customers get used to. Also in some accounting
> programs it is almost impossible to use master detail relation. At this time
> I use queries to get the sums and averages. Also I write some event handlers
> to show the averages when displaying the master dataset in a grid. This is
> the worst of all because it really lowers the performance.

I'm not sure we have got your sequence right here.
Changes to Table B result in the need to update the count and average
for a particular record in Table A?
So Table A ideally needs fields for Total and Count (Avr = total/count)
and the trigger on Table B just adds to or decreases the Total and
Count as required?
After updating Table B, we need a refresh to update any display based on
Table A.
At the moment I can't see why you need to be 'locking' Table A except to
write the update. So the problem may be that you need updates to TableA
in a different transaction to TableB.
What am I missing here?

> This is not a new thing. I know that most database programmers suffers
> because of this situation. What others are doing to solve this problem ?
> Especially on web database programming. if people would like to share their
> solutions it will help us to improve ourselves.

The trick in many cases is to get the 'engine' to do the work. I have a
table that carries a lot of transactional information, which the users
are continually reading - but NEVER write to. Only the triggers create
and update records. The original design prior to sorting this out had
all sorts of problems with 'dead-lock' just like you are seeing, but now
I don't have any ;)
Perhaps you need a third table with just the results in, and a primary
key of the TableA entry - that way the client never writes to the
results table, but I'm sure that things can be organised so that only
the two tables are needed.

Lester Caine
L.S.Caine Electronic Services