January 10, 2003 at 12:19 pm
I have a simple trigger on the table that once a row is inserted it calls a sproc and process the record and delete the record eventually.
This works fine in single thread situation.. however once I introduce a second thread I get a deadlock situation. After some research it seems like the trigger from the first thread is locking the table.
I am no expert on trigger... is there some trick that I need to do to prevent this from occuring? Does anyone else have experience with this problem?
Thanks in advance...
January 10, 2003 at 1:19 pm
Until the transaction from the first trigger is committed, there will be locks on the table. You can reduce the time of execution by the trigger, change parts of your locking scenario by setting table hints, or changing the transaction isolation level, or you can redesign the process to perform its functions in a different manner.
If you just HAVE to stay with a trigger to do whatever your doing, have the trigger write something to a work table, where another process can pick it up from there, and complete whatever needs doing. DO NOT base the second table's actions off another trigger, as this will put you right back in the same boat. The idea is to isolate the actions of the trigger to a small simple action that commits quickly, so that the table can recieve the next record. This will increase your concurrency, without having to resort to changing the locking behavior.
Also, you shouldn't be getting a deadlock condition, but a blocking condition. If your actually getting a deadlock condition, your process has some concurrency issues that need to be resolved anyway. Look into that, but still look into the previous suggestions as well.
January 10, 2003 at 2:40 pm
Scorpion_66
thanks for the tip.. I am looking at a few things.. one is to redesign the flow as you mentioned... unfortuantly THIS is the working (temp) table that's having this issue.
A little more background here..
I have a sproc that handle the insert to the working table...
then the working table's trigger calls the second sproc which actually process the data from the working table.. I do this because I want to release the resource for the first calling sproc as soon as possible..
However the trigger locks the table preventing another insert into the working table until it finishes...
I know it is the trigger because if I simply have the first sproc insert into working table then have the first sproc calls the second sproc right away then multi-thread becomes no issue... this has been tested.... however it goes against the ideal design..
still trying..
January 10, 2003 at 4:26 pm
Rather than using a trigger on the work table, could you simply have a scheduled job, running every minute or whatever, check for a record in the work table and do whatever. It would become a queue, and the scheduled job would do the work, breaking the circle you have. I use a queue system on a lot of designs.
January 10, 2003 at 4:30 pm
Scorpion_66
good suggestion... that's I was just talking to someone about the same design... seriously considering using that...
Oracle seem to have an easier way to schedule a job to run from time to time.. but SQL is more of a pain...
Thanks!
quote:
Rather than using a trigger on the work table, could you simply have a scheduled job, running every minute or whatever, check for a record in the work table and do whatever. It would become a queue, and the scheduled job would do the work, breaking the circle you have. I use a queue system on a lot of designs.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply