How to synchronize messages stored in a database, but processed by multiple services

Go To


I am wondering how to create my own SOA style messaging system, similar to JMS (Java Messaging System).

The MOM (Messaging Oriented Middleware) needs to store messages in a single database, but may be processed by more than one service for scalability and failover.

I have defined a rudimentary Messages table as follows:

  1. MessageId int
  2. CreateTimeStamp datetime
  3. DeliveredTimeStamp datetime
  4. Payload varchar(max)
  5. Expiration datetime
  6. RetryCount int
  7. CorrelationId int
  8. State int (1-Waiting, 2-Processing, 3-Sent, 4-Retry, 5-failed)
  9. *ProcessIdLock int - This is the process id of the messaging service that is processing the message

The problem is, idemptency issues aside, how to I ensure that one service processes each message at a time?

I was thinking about a scheme like this:

  1. Perform record locking: UPDATE Messages SET ProcessIdLock = MessageProcessorId, SET State = 2 -- Processing WHERE MessageId IN ( SELECT TOP 10 MessageId FROM Messages WHERE Expiration < GETTIME() + CreateTimeStamp AND State = 1 -- Waiting OR State = 3 -- Retry )

    The above step will get up to 10 messages to process by a single service at a time

  2. Get the locked records: SELECT Payload FROM Messages WHERE State = 2 -- Processing AND ProcessIdLock = MessageProcessorId

  3. Update the status for each message processed: UPDATE Messages
    SET State = (Pass, Fail, or Retry), SET DeliveredTimeStamp = GETDATE() -- Pass only WHERE MessageId = ProcessedMessageId

Note: Another problem I have is that some messaged are broadcast to several clients grouped by id (such that, there may be 100 total clients, but each client group will consist of 10 or less).

For example, what happens if there are 5 clients in a group, but 2 clients are currently disconnected? I would like to sent that message to those clients as they come back up.

One client may reconnect (and should get the message), while the other client may not reconnect (so the message will be dropped after message Expiration).

Thank you for reading this. Seems like a typical enterprise problem to me. Would MSMQ be the best solution? I am new to MSMQ, are MSMQ messages persisted in storage or kept in memory?

2012-04-04 16:39
by NickV


I know it doesn't directly answer your question, but I would very strongly encourage you not to reinvent the wheel here. Creating a reliable, durable, scalable messaging system is not something you should take on lightly. Instead I would encourage you to look into existing open source implementations, for example NServiceBus.

2012-04-04 17:46
by David Nelson
Perhaps I do not understand NServiceBus and others, including MSMQ, enough, but I need a system that allows more than one service to process a given message. When all attached and listening services have processed the message, then the message can be removed from the database.

What I have done is create Messages, Services, and ServiceMessages tables. I then use LEFT OUTER JOIN ... WHERE ServiceMessages IS NULL to check for processing.

Once a message is read and processed, I add an entry in the ServiceMessages table which indicates it has been handled - NickV 2012-04-27 15:34


I agree with David Nelson. Unless you have a really good reason for doing so I would look at existing solutions. In addition to NServiceBus you could also checkout MassTransit.

2012-04-04 22:51
by richk