Subject Re: [IBO] Lock Conflict Problem
Author Metin Gönen
>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
>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
>the flexibility, concurrency and performance you are looking for.

>Jason Wharton

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.

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.


Metin Gonen

avast! Antivirus: Giden mesaj temiz.
Virus Veritabani (VPS): 0525-4, 24.06.2005
Test zamani: 25.06.2005 10:17:56
avast! - copyright (c) 1988-2004 ALWIL Software.