How can I avoid sending duplicate emails when I commit the “email sent” action after sending the email?

Question

When a user signs up on my website I create a user record in the database with email_sent = 'NO'. I then have a cron job that sends welcome emails every few minutes. It selects all user records where email_sent = 'NO' and sends the welcome email.

If the email is sent successfully it then updates the user record to email_sent='YES'. My question is, how can I avoid the scenario where the email sends successfully but the update of the user record fails and the next time the cron job runs it sends a duplicate email. Even if I used a database transaction and I updated the record before sending the email but committed afterwards, if the commit fails I would have the same situation with duplicate emails being sent.

The alternative is to update the record to email_sent='YES' and commit immediately, and then attempt to send the email. But if the email send fails then no email is sent.

Is there any way to guarantee exactly once sending of an email? I realize the email delivery could ultimately fail due to a number of external factors, but I just want to ensure that my SMTP server successfully shoves it off exactly once.

Why is the update of the user record failing? Solve that, and you've solved your problem. — Robert Harvey
What RDBMS are you using, Oracle, SQL-Server, MySQL etc? It's likely that there will be something database specific that can help. — Ben
Further to Robert Harvey's comment - if your commits aren't successful when updating the sent record, there is little that can be done to prevent duplicates. If the cron relies on a value in the database, that value must be updated reliably. Otherwise you will have to develop ever-more-complex ways to handle the inconsistent data. — Farray
It's not failing currently, but I'm paranoid and worry that it could. — william
@user1566921 Can you set up a log to see how often duplicate emails get sent out? Then you can see if the actual duplicate rate is higher than {AcceptableRate}. — Farray

Farray Farray · Accepted Answer · 2012-07-31T21:24:24

Your comment states that the commits aren't failing so you may be worrying about the wind.

Keep the transaction as small as possible and have it fail whichever way is more acceptable in your system...

BeginTransaction;
LookupRecord;

SendMail;

if(SentSuccessfully)
{
    UpdateRecord(Sent=True);
    CommitTransaction;
}
else
{
    RollbackTransaction;
}

Alternatively you could do it with more complexity and without the transaction around the email function, but I don't see this as better:

BeginTransaction;
LookupRecord;
UpdateRecord(Sent=True);
CommitTransaction;

SendMail;

if(!SentSuccessfully)
{
    UpdateRecord(Sent=False);
}

Either way, if you have errors and the correct value is not committed to the database, there isn't much you can do to avoid it without introducing (potentially unnecessary) complexity. You really should focus on making the update & transaction rock-solid so it doesn't fail to update.

How can I avoid sending duplicate emails when I commit the “email sent” action after sending the email?

1 Answers