At 12:14 PM 22/06/2005 +0300, you wrote:

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

No, it is not trouble. It is transaction isolation doing exactly what it
is designed to do. It would not be a happy situation if a transaction's
view of TableA were inconsistent with its view of TableB.

However, why try to store these averages at all? You can create a
read-only view that contains the calculated value as a COMPUTED BY
field. If nobody is updating that average, then everybody can read that
view and see the latest average in his own transaction context.

Helen