Trigger and deadlocks
-
... and now everyone knows why I asked four times what the heck the EXE was doing!
First, I gotta say, it sounds like you have an app that's adding one row to the table and then you're calling some sort of API to send that data real time... perhaps its credit card or ACH info... dunno. If you DO have an app doing this, why don't you just have the app pop the API instead of getting all balled up with SQL Triggers?
Also, if you're real sure that the data won't be rolled back, why not interogate the INSERTED table instead of the target table for the XML?
If it's NOT a real time app... what's a couple of seconds or even minutes gonna matter... have the trigger gen the data to a staging table and have a CMD line job (using XML) run once a minute to create the XML, pop the EXE to process the data, and then delete those rows from the staging table (or move them to the permanent table using INSERT/DELETE)???
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I agree with Jeff. Since this "app" needs the Xml, IT should generate from the table that's being inserted into. All you really need is a "Status" column that the Application queries against (Select From QueuedEvents Where Status = "Awaiting"). Once the app is done with the record, it can then update the status to "Complete" or whatever. This app should probably just be a Windows Service or maybe just call it from the Task Scheduler every minute. If you can't have that kind of latency, have SQL server generate the Xml and save it out to a directory. The Application can hook up a "FileWatcher" (if it's .NET) and just watch the directory for new files and process them - tada, no latency! At which point when it finishes with a file, it can go back to the database and update the Status to "Complete" because you'll have specified an Xml Element with the database ID to update!
Another scenario is to save everything into a QueuedEvents table which gets processed every minute, 5 minutes or whatever. Same as above, only you're not generating Xml, the app is just reading from a table.
-
My former manager insisted to use a trigger to load a table from multiple text files. It created a dead lock because the insert transaction of one file may take a long time and the other files waiting and eventually it created a dead lock. His solution was put with (NOLOCK) in every single SELECT statement that used that table. I told him it would not work because the trigger was the problem. He told the director I was not co-operate and refused to listen to him, I was not a team player...blah blah blah
-
I hope you asked for 1 day shipping... that's gonna make more difference in your life than you can imagine (ya you'll have to do something for that... but it's worth it).
-
I already got the DVD. I bought it at FYE at the mall. I could not find the movie so I asked one of the employee, he asked me what kind of movie it was, I said it was about the earth......no idea. He took 10 minutes to find this movie, it was under documentary. Even he was curious about what movie it was.
-
Let me put it this way : The movie is great, if you don't anything about it, then you just wasted 20$. If you do something about it, then you just made your life.
-
Bingo, Scott... took the words right out of my mouth... don't need the EXE to gen decent XML... it can all be done in T-SQL...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I missed what you're talking about... which movie, Remi?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The Secret
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply