September 23, 2005 at 2:47 pm
When I write my stored procedures I use transactions within the stored procedure.
A programmer recently opined that doing so interfers with their transactions when using MSDTC or MSMQ.
I would have thought that all that these would do would be to nest the transactions so as far as they were concerned the transactions that I use would not be visible.
I rarely nest transactions in my procedures so I can't imagine that some limit is being breached.
I don't know enough about MSDTC or MSMQ to know if the programmers are correct or not, can anyone shine a light on this?
September 26, 2005 at 7:20 am
I don't know what exact problems your programer meant. If the applicaiton is proper coded, there should be no prblem.
For a distributed tran, MSDTC uses two phase commits. If all involved servers are succeeded for a tran, the MSDTC commits the tran; If any server is failed, MSDTC rolls back the whole transaction on all servers, including the servers that already committed in the server scope.
For MSMQ, the queue processing application has to control/synchronize the transactions between SQL server and the queue. We have apps that reads a queue message, starts DB transaction and purge the message from the queue if the tran is succeeded. The reason is that there is more chances database transaction fails than queue transactions (purge the message from queue). So if you use queue call back, the message is removed from the queue when your app is called. But the database tran may be failed. You either have to retry the db tran or log the failed message somewhere becuase the message has been removed from the queue. Use "read queue" method is more easy to control.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply