I want to send email to my app users ensuring that every email is sent only once.
I will be recording the email transmission in a database.
If I use this order of operations:
There is a chance that script times out just after sending the email (step #2) and before doing the commit (step #3). In that case the change in step #1 will not be committed and email sending job won't be able to know that the email was successfully sent last time so the same email will be sent again.
Is there a way out or do I have to live with occasional duplicate emails?
To make a long story short: you can't make email transactional.
At best you know that your smtp server recieved your request to sent the message. You have however no way of knowing whether it got sent, was received or bounced.
So your best bet, as you already suggested, is to live occasional duplicate emails. It will be a very rare event anyway.
You can use MySQL transactions for this, basically it prepares all your queries and executes them when you tell it to do so.
So you prepare the queries before sending the mail, then commit it after sending it.
Alternatively you could set the mail as pending, and afterwards update it as completed. Then run a cron job that kills pending jobs that have been running for a certain amount of time, or try to reprocess them.
well you can make a column
status which can be
int with values representing
sending, failed, sent_successfully
Now, you do this:
updatethe row either
You might also like to have a column
tries and a batch process that sends
failed mails at every 30 minutes, if
tries < TRY_THRESHOLD. And then, set
sent_successfully and log the error.
Just got some time to think over my own question. Here is what I think now:
The email sending steps in question were:
Email cannot be made transactional because the step #2 (above) is not part of the transaction even though it is done while the transaction is active.
These steps can help ensure that any failed email-sending attempts is retried but cannot guarantee that an email is not sent more than once. This situation could only be improved if the email sending engine is transaction-aware. Such an engine would (atleast) do the following:
I am not aware of any such email server.
Nishant, in his answer suggested following steps:
- Insert the data before sending the mail. And set status to sending
- Send the mail
- based on the result in step (2), update the row either failed or sent_successfully.
These steps also cannot ensure duplicate sending of email due to script timeout for the same reason as noted above.
So far, I think, I will just have to live with occasional duplicate emails.