当前位置: 动力学知识库 > 问答 > 编程问答 >

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 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.

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: