You have created what I call a "hot spot" in your database. It may help
that in your trigger you put code around things so that you only actually
update those columns when necessary. If you do an update and nothing has
changed in the sum it will still put all the new record version entries and
produce potential lock conflicts. However, I still think you are prone to
have problems even if you trim off unnecessary updates. You may want to
separate this over into a new table and link it so that your whole record is
not locked just by updating this average.

This isn't an easy problem to solve so don't get too up tight if this takes
a fair amount of experimentation and even a fairly elaborate solution to get
the flexibility, concurrency and performance you are looking for.

Jason Wharton
www.ibobjects.com

> -----Original Message-----
> From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]On
> Behalf Of Metin Gönen
> Sent: Wednesday, June 22, 2005 2:14 AM
> To: IBObjects@yahoogroups.com
> Subject: [IBO] Lock Conflict Problem
>
>
> Hi ,
>
> I will try to explain the situation simply as below ;
>
> There are two tables
>
> 1) Table A Fields (Stock_otonumber,
> StockNo,Average_of_prices_on_tableB)
> 2) Table B Fields ( otonumber,stock_otonumber,price)
>
> Also there is an after_insert trigger for table B which gets
> the average of
> the prices of all table B records and then updates Table A
> (Average_of_prices_on_tableB) Field
>
> If Multipl computers try to enter data on table B then lock
> conflict occurs
> ,I am assuming this is because of the update on Table A.
>
> Both tables have TiCommited transaction and LockWait= False
>
> When I set LockWait to true on Table B then this time deadlock occurs.
>
> If I drop the trigger and use a joined sql statement on
> Table A to get the
> averages on TableB ; This time the access is slow.
>
> How can I find a way to get rid of this lock conflict ?. or
> if I don't use
> trigger here , can you show me another wat to get averages or
> some totals of
> tableB while browsing on Table A
>
> This is really a big trouble for me . I need urgent help.
>
> Metin Gönen