Mysql – Preventing concurrent sends of emails from the application

MySQL

I have a system that sends emails for me and my clients every day in the morning. This process is triggered automatically through access to my website, via an iframe.

Often, I receive the same email twice.

The simple process I created on a MySQL database to control this is as follows:

  • run this query: 

    SELECT nome, email
FROM clientes
WHERE DATE_FORMAT(lastsend, '%d/%m/%y') <> DATE_FORMAT(now(), '%d/%m/%y')

  • send emails

  • UPDATE clientes SET lastsend = Now();

I need a way to lock this down to prevent the same emails from being sent multiple times on the same day.

Best Answer

This should be your main two queries

STEP01 : Run this query

SELECT nome,email FROM clientes
WHERE lastsend < (DATE(NOW()) + INTERVAL 0 SECOND);

STEP02 : Run your process to send out emails

STEP03 : Run this query

UPDATE clientes SET lastsend = NOW()
WHERE lastsend < (DATE(NOW()) + INTERVAL 0 SECOND);

The expression (DATE(NOW()) + INTERVAL 0 SECOND); is always midnight today.

Look it over

  • STEP01 will check for lastsend to make sure it is before midnight today
  • STEP03 changes the lastsend to the current date and time for the emails that have not been sent out today
  • You can run STEP01 - STEP03 multiple times in the same and only new emails will go out

You should also make sure that lastsend is indexed. If it is not, then run this

ALTER TABLE clientes ADD INDEX (lastsend);

This will help speed up the query in STEP01

As for locking the table during this process, I would need to see the table structure. I have an idea concerning using the DB Connection ID. It goes something like this:

Add a column called sender to clientes table and index it

ALTER TABLE clientes ADD COLUMN sender TINYINT DEFAULT 0;

Once you create that column and index it as I specified, you can perform the following:

STEP01 : Mark everything before midnight in your DB Connection as midnight

UPDATE clientes SET
    sender   = CONNECTION_ID(),
    lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)
WHERE
    lastsend < (DATE(NOW()) + INTERVAL 0 SECOND)
;

STEP02 : Run your process to send out emails where sender=CONNECTION_ID() and lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)

STEP03 : Mark your stuff as sent

UPDATE clientes SET
    lastsend = NOW()
WHERE
    sender   = CONNECTION_ID() AND
    lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)
;

This will restrict which DB Connection send which batch of email

Give it a Try !!!

Related Question