http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Maninder, Because I don't want to consume the message on error. I want to put it back on the queue and let the queue poison msg handling to take care of that. This way, I don't lose messages http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment13http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment13Wed, 03 Nov 2010 16:27:25 GMTHow about the following :- try { 1. Dequeue try { 2. Process Message } catch (Exception ) { 3. Message error, ACK and do not queue it back 4. Internal system error, retry or compensate. } try { 5. Commit message processing } catch (Exception) { 1. Error while commiting, retry or compensate. } } catch (Exception ) { //Error while Dequeue, retry or compensate. } try { ACK } catch (Exception ) { // perhaps retry ACK or compensate. } Question :- why do you need table for persisting fully processed messages ? http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment12http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment12Wed, 03 Nov 2010 16:06:19 GMTI think the code seems to work. The down side is you have to store some state (processed messages) specific to this problem on the client side but i can't think of a better way of doing it without dtc. http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment11http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment11Mon, 01 Nov 2010 09:54:18 GMTAlex, The point isn't to import the queue. The point is to process the message just once in a successful transaction http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment10http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment10Mon, 01 Nov 2010 07:04:07 GMTOren, I know you like to argue, so let's leave the talk about best practices. If your goal is to safely import the external queue to RDBMS as-is, the code seems almost absolutely safe. Btw, you don't need manual transactions at all to achieve this, because if entry isn't imported, it's always safe to re-import it. Why "almost"? Well, because there is a cleanup process that removes imported && processed entries from DB. So there is a tiny chance that entry will be imported and removed, but Ack isn't sent for it (e.g. because queue was dead for enough long time). But practically, this is hardly possible. http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment9http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment9Sun, 31 Oct 2010 19:25:03 GMTAlex, You can pretty much assume that InsertToProcessedMessages translate to the direct ADO code to call INSERT INTO ProcessedMessages There is generally no option for deadlock on insert and there are certainly no version conflicts for pure inserts. http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment8http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment8Sun, 31 Oct 2010 17:47:24 GMTAlex, If that is the case, on the next try to read the message, you will hit the DupPK error and ack it then. http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment7http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment7Sun, 31 Oct 2010 17:46:16 GMT> You can't GET a deadlock from a simple insert when the only things is an insert of a PK. First of all, there is no any exact code, so I assumed there is generally anything. I any case, I won't ASSUME there is just an INSERT even if I'd know this precisely. That's simply a bad practice: the DB-related code based on implication there can be just one kind of exception, and no deadlocks \ version conflicts might lead to completely unexpected issues in future, since the author maintaining InsertToProcessedMessages might not be aware of these implications of its callers . http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment6http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment6Sun, 31 Oct 2010 17:02:01 GMTUps, initially I didn't fully understand the logic of this code, so ignore my previous comment. IMO, it has just one lack, that will lead to an issue in this case: - SQL Server actually commits the transaction as result of tx.Commit() call - But something (e.g. network issue) prevent SQL client from returning correct completion code of this operation. In this case your code will re-process the message, that actually was already processed. http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment5http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment5Sun, 31 Oct 2010 16:53:49 GMTAlex, You can't GET a deadlock from a simple insert when the only things is an insert of a PK. http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment4http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment4Sun, 31 Oct 2010 16:51:31 GMT"catch (DuplicatePrimaryKey)" does not ensures all errors are processed. E.g. deadlock exception won't be handled by this code. As result, Ack won't be invoked at all. Another case is when tx.Commit() throws an exception. Generally, by the same reason; also, some DBs may check constraints with delay, so actually a wide range of exceptions is possible there, and everything depends on a particular case. If an exception is thrown by tx.Commit(), Ack won't be invoked again. http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment3http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment3Sun, 31 Oct 2010 16:48:57 GMTJosehph, SQL Server Service Broker is a local queue, from a conceptual model. The problem is that it is very likely that Send / Receive to the queue will fail if it is stored on remote machine. It mostly depends on the deployment mode. http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment2http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment2Sun, 31 Oct 2010 16:29:19 GMTWhat about a transactional remote queue like SQL Server Service Broker? In this case you receive a message inside a transaction, the message is dequeued, and if the transaction is rolled back for any reason the message is re-queued. The guidance is that your Service Broker database and application transaction database should be separate. Therefore you would employ the DTC to get transactional consistency between your application database and your remote queue. There's no need to keep track of which messages were processed as it will never be handled more than once (such that data is written to the application database). http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment1http://ayende.com/4679/distributed-transactions-with-remote-queuing-system#comment1Sun, 31 Oct 2010 15:21:00 GMT