How can I make email transactional in PHP and MySQL?

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:

1. Insert into database
2. Send email
3. Commit

There is a chance that script times out j开发者_开发问答ust 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.

More info

http://dev.mysql.com/doc/refman/5.0/en/commit.html

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 enum or int with values representing sending, failed, sent_successfully

Now, you do this:

1. Insert the data before sending the mail. And set status to sending
2. Send the mail
3. based on the result in step (2), update the row either failed or sent_successfully.

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 status to failed_permanently or 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:

1. Insert into database
2. Send email
3. Commit

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:

1. Accept email job submission but don't send the email until the commit is performed
2. Make the commit fail if the email is not sent.
3. Cancel the submitted job if the transaction is rolled-back

I am not aware of any such email server.

Nishant, in his answer suggested following steps:

1. Insert the data before sending the mail. And set status to sending
2. Send the mail
3. 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.