Send mail as transactionally as possible

Question

Hi all,

I have a simple program that polls a DB table every so often and sends any mail that the table indicates is pending (using javax.mail). When the mail is sent I delete the DB entry.

I have noticed two potential problems

• There's the potential to send a mail and then something crashes so the DB entry is still there. The next time the mail will be sent again.
• There may be lots of mail to send so I load all the pending entries, send them all, then delete all the DB entries. If some/all the mails get sent, something fails, and the job runs again then it spams many people twice.

This isn't a spam application so I want to try and avoid sending anything twice plus make sure I can never "lose" a message.

This is a general problem I'm sure where there are two physical resources that can't participate in a transaction (DB + something else) so I was wondering what approaches people use to reduce/eliminate the problems for this case and/or the general case.

Answer 1

I would definitely read each mail from the database individually, to avoid the second problem you describe.

Place a lock on the database row (e.g. select for update) it in case multiple processes run at once (even if you don't intend this to happen, it might happen by accident due to incorrect deployment, some fail-over activating when the original service is still running, ..)

Either send the mail just before you do the commit on the transaction, or send a message to some asynchronous system e.g. via JMS. But the simpler solution is just to send the email just before the commit if possible. If something goes wrong with the mail you can rollback. If nothing goes wrong, it's unlikely something will go wrong with the commit so you are probably fine.

Otherwise, if that doesn't fit in to the software design you are using (e.g. email being sent from some business logic method, and transactions managed by a higher-level process) then you could create Email objects and put them in a list. Just before the commit you could check this list and send the emails. In this way, emails become a bit more transactional, i.e. they are sent only when you do a commit.

Answer 2

I have a simplistic solution to your problem. Add a field in the DB called 'Pulled'

Check Pulled = 0 then Pull the data, update 'Pulled' = 1 and send the email. After the email is sent delete the entry.

Depending on the method you are utilizing to send the email you could have catches in place to ensure the email is sent.

If 'Pulled' = 1 on initial check then you know there was an error and you must make a decision whether to risk resending the email or deleting it and risking losing the email.

You know two things for certain.

(1) If 'Pulled' = 0 then email has not been sent.

(2) If 'Pulled' = 1 then an error occurred in the process of sending or deleting.