November 10, 2008 at 10:37 pm
rbarryyoung (11/10/2008)
Matt Miller (11/10/2008)
Or - use a WAITFOR with a Service Broker Group conversation handle....No loop.OMG, you are killing me here Matt! I am using Service Broker with WAITFOR, that's how I got into this spot... :w00t:
Serioulsy though, if there is no easy way to do this, I can have the client handle it, I was just trying to avoid that...
(can't fault me for thinking like you, can you?) I kind of figured you had already thought of it.
I don't get it - can't we start the process with one message, and finish it with yet another? Meaning - the proc that they call just sends the message out. The message handler process then handles the transaction, stopping/pausing somewhere in the middle for more feedback before actually committing the transaction? You'll end up with one single execution scope handling the transaction, and thus no message.
What am I missing? is that tying up too many threads?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 11, 2008 at 6:52 am
Matt Miller (11/10/2008)
I don't get it - can't we start the process with one message, and finish it with yet another? Meaning - the proc that they call just sends the message out. The message handler process then handles the transaction, stopping/pausing somewhere in the middle for more feedback before actually committing the transaction? You'll end up with one single execution scope handling the transaction, and thus no message.What am I missing? is that tying up too many threads?
Well, what's missing is that the Client in this case is the receiver, not the sender.
It works like this:
1) The Client (a windows service) calls a stored procedure, say spReadNextMessage()
2) This sProc RECEIVE's the next message from the queue. Because "Store & Forward" logic does not work unless you protect all message-handling with a transaction until it is "safe" again (i.e., "stored"), we have to start a transaction here. We return the message to the Client, leave the transaction open (because we do not know what to do with it yet) and exit the sProc. {This one of the two points where we get the "Missing Commit..." error}
3) The Client, a Windows Service, attempts to transmit the message (in this case execute a proc on an Oracle Server, passing in the message data), remembering the status result of this attempt.
4) The Client then calls a disposition sProc, say spCloseMessageTransaction(), passing in a parameter that indicates what happened as a result of the transmission attempt: Success, Error, or CommFail (Communication Failure).
5) The sProc then:
A) On success, just COMMITs the transaction
B) On Error, forward the message to a "Bad Message Queue", for later review and then COMMITs.
C) On CommFail, does a ROLLBACK on the transaction and sends an Operator Alert.
And then, exits the sProc: {and this is the second place where the "Commit Missing.." error occurs.
6) The Client service, continues, calling spReadNextMessage() again, looping to (1)...
The point here is that the Client is very dumb, it does not need to concern itself with any special error-handling, reporting, state checking, etc. Well, except for CommFail's it has to stall the process until the remote server is reachable again. But other than that, it can be very dumb, lightweight and unconcerned about issues like error-handling, trnasaction handling, state handling, error reporting, etc.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 11, 2008 at 10:03 am
The problem I'm seeing is that if you simply exit the proc and term the batch, you now have an orphan, uncomittable transaction. This path then leads to the Dark side as you of all people should know (and rollbacks, too).
If the client were to simply track the conversation handle, then the SP it calls would just need to send the message out (which then triggers the previous thread to either commit or roll back). I just don't think you get away with not leaving it open (in a waitfor state). The net effect though is that this particular stored proc doesn't leave a transaction dangling; it just keeps it open for a while....
Of course - I'm probably stating what you've already figured out, since now the client has to track "who it was talking to", making it smarter than you'd like it to be....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 11, 2008 at 11:50 am
Matt Miller (11/11/2008)
The problem I'm seeing is that if you simply exit the proc and term the batch, you now have an orphan, uncomittable transaction. This path then leads to the Dark side as you of all people should know (and rollbacks, too).
Heh, right. I don't do that unless I get a Fatal Error and the batch and session are already lost there anyway.
If the client were to simply track the conversation handle, then the SP it calls would just need to send the message out (which then triggers the previous thread to either commit or roll back). [/quote]The conversation handle is not a problem: The sProc that receives the message ends the conversation right there. It should get rolled-back too, when and if a rollback is needed, so no worries there.
Of course - I'm probably stating what you've already figured out, since now the client has to track "who it was talking to", making it smarter than you'd like it to be....
All the client has to do is keep the same session. Like Temp Tables, transactions are actually "session-level" entities, so as long as the Client maintains the session, all it has to track is the outcome of the send attempt. All other context is maintained by the session, either implicitly (in the open transaction) or explicitly (in a temp table).
This is no big deal, I will just have the client catch and ignore error #266. I just thought that there might be a setting that could suppress it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 12, 2008 at 9:58 am
Just out of curiousity, could the sproc "store" the current state in a table somewhere, with enough detail to avoid having to leave the transaction open, such that subsequent calls to the sproc can then reference the "stored" state information and know what to do? It probably means introducing a potentially significant dependency and a lot of complexity, but if the client is supposed to be dumb, then the sproc has to be smart, right?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 12, 2008 at 3:03 pm
smunson (11/12/2008)
Just out of curiousity, could the sproc "store" the current state in a table somewhere, with enough detail to avoid having to leave the transaction open, such that subsequent calls to the sproc can then reference the "stored" state information and know what to do?
Sure, but that's what Transactions and the transaction Log do for us efficiently now. Adding a custom-written, high overhead, high maintenance facility of my own would be a cure that was worse than the disease.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply