Two Phase Commit - How to use my queue effectively?

Question

What I am about to describe is essentially a two phase commit problem between two disparate systems and I am looking for advice on how to handle it. In our web application, we offload some expensive/3rd-party operations, such as sending emails, to out-of-band background worker processes (we call it our job infrastructure.)

To send an email, for instance, we create both an email object and email job in our database. We then have to wait for our job monitor to pickup the email job and send it. The job monitor essentially works by polling the database every few seconds when it is idle.

This, however, adds a delay into email sending and adds what I view as undue load on the database with the polling. It would be much nicer if we could immediately put the email job on the queue as soon as the email is created.

However, this currently fails for two reasons. First, the queue is often much faster than the web request. The email is picked up for processing before the web request has committed its database transaction, so it can't properly generate the email. Second, if the web request fails, it rolls back its database transaction which means the the email should not be sent. However, if it's already been put on the queue, then it's no longer in the request's control.

Is there a good strategy for creating a two-phase commit between the queue and the database? For reference, we are using RabbitMQ and MySQL with InnoDB tables. One idea I head was to stick the email jobs on the queue after the database transaction has been committed, but that leaves the possibility that the email never gets queued. I'll still have to create a polling process that watches for emails that should have been sent and weren't.

Answer 1

I realise this is a couple years late :) but I wanted to add some thoughts on this for others that run into this question.

You could send the message delayed to increase the chance the DB is ready when the jobs gets it. I've never used RabbitMQ but I found this example of using a rabbitMQ queue as a delayed queue How to create a delayed queue in RabbitMQ?. Your email-job would still need to deal with any uncommented records since delaying isn't a deterministic method of dealing with distributed processing.

That said, it feel like there's room to improve your design. Generally with service architectures its a bad idea to share db tables between services. It leads to distributed transaction and scaling issues. I would try to insure that the RabbitMQ message contains all the data it needs to process the email independently of any other service. Or if it needs more data, it should request that data through a ServiceBus request instead of a DB query.