Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nathans
Aged Yak Warrior
938 Posts |
 Posted - 2005-02-11 : 19:31:21
|
| Guys,I am working with BizTalk, and need to write a stored proc that can read in a number of rows and process them. This procedure is going to be called from BizTalk, which is configured to open several threads all executing this same procedure.How can I ensure that one thread will not pick up a record currently being processed by another? I can use a TRAN to lock the row, but I was curious as to how others are approaching this issue with BizTalk / similar applications.Thanks!! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-11 : 21:16:06
|
| Can you describe the process in some more detail? What would BizTalk be doing with the data? Could that process not be done in the database? |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-11 : 21:54:52
|
| I did a project that might be aplicable. I wrote a windows service who's job it was to take queued up records from a newly developed enterprise system and write records back to a legacy system. It was crucial that all records inserted,updated,or deleted from the new system had to have the same processes performed on the legacy system. At high volume times there were enough records being queued that we needed 3 instances of this service (running on 3 different servers) to keep up.I solved the contention issue of 2 instances trying to process the same record by having the service call an SP that first "flagged" the next n records that weren't already flagged or processed with an identifier unique to that instance. Then 1 by 1, each of the flagged records would be processed by the service that flagged it. All interactions with sql server was performed via stored procedures. That queuing table also served as an audit of every change written back to legacy, when it was processes and which machine did the work. It included a status field which allowed for multiple attempts if there were failures. A sql job would kick off periodically to check (and alert if necessary) when the unprocessed records built up beyond n rercords or if there were records with a failure status. That could happen if a service was shut down or the legacy system was down or whatever. It worked extremely well until the legacy systems could finally be turned off. We never missed a single transaction.Be One with the OptimizerTG |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-02-15 : 20:17:23
|
| basically, the stored proc is going to take a type_id and row_count as input.Lets say we pass type_id = 1 and row_count = 10The procedure will grad the first 10 rows of type_id = 1 and "process" them. Its not relevant what the process will be doing, but when it is done, upon success it will update a status column in the same table.I can contrsuct the procedure okay, but I am concerned that BizTalk will be opening several threads all executing this same procedure, and I need to ensure that only one thread picks up each row. I dont want to process rows while other threads are processing the same rows.So far (with TGs suggestions) I have added a GUID column on the table, which I write to from the proc to bind the row to a thread. If I error while processing, I update the GUID back to NULL so that another thread can pick it up. On success, I update the status and move on. Any ideas? I am obviously new to these multi threading tactics.Thanks all!- Nathan |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-15 : 20:56:41
|
quote:
Its not relevant what the process will be doing
Why is it not relevant?If you're pushing data off to BizTalk that could be processed on SQL Server, you're wasting network cycles. If you're going to process 100,000+ rows 10 rows at a time, it will almost certainly be done faster as a set-based operation in SQL Server. SQL Server is a multi-threaded application, depending on what you're doing you may get zero benefit by doing the work in BizTalk. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-15 : 21:11:36
|
| Robvolk makes a good point. However, if the process NEEDS to take place outside of SqlServer for some reason and you are going to pursue what you've outlined above:Couple suggestions. If one of your records can't be processed for some reason, don't keep trying indefinately. Maybe you can make your status column a tinyint. 0=unprocessed, 1-3=failed attemps, 10=Succeeded. So your proc will "grab" the first 10 rows (type_id=1, GUID is NULL, status < 4). "Grab" should really mean "Reserve" the rows for this thread/process. Reserve them by setting the GUID. Failed attempts will increment the status and null-out the GUID, success will leave GUID as is and set status to 10. That way a row can't be tried more than 3 times. If it can't be processed after 3 attempts there is probably a problem someone needs to look at. After 1 or 2 failed attempts the record is "available" to be reserved by another process.By having the proc set the GUID for all 10 rows at once (implicit transaction) you are assured that those rows can't be "grabbed" by a simultaneous call from a different process. Then it's free to work through those 10 rows and process them safely.Be One with the OptimizerTG |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-02-16 : 12:34:47
|
| Robvolk,I totally agree... but using BizTalk is not a decision of mine. That system is already in place and out of my hands, I am just passing xml to it. They will throttle the processing via the row parameter.Thanks TG for the suggestions. |
|
|
|
|
|
|
|
|
sp_ best practices with multi threading app