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.
