Search code examples
mysqlmariadbconcurrentmodification

Can my UPDATE statements will work if run concurrently in MySQL?

I saw many similar questions but still I am not fully sure if I am correct.

We have an application which launches a job to mass send many messages. The messages delivery status are received later in batches and in no particular order.

The table structure is like:-

CREATE TABLE `message` (
  `pk` char(32) NOT NULL DEFAULT '',
  `job_id` varchar(40) DEFAULT NULL,
  `status` varchar(40) DEFAULT NULL,
  `update_date` datetime DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `job_id` (`job_id`),
  KEY `status` (`status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The records are initially created with status set to null. Its value is expected to change from null to sent and then to delivered. Below statements are used to update the records.

When status to be set is - delivered

Update message SET status = 'delivered', update_date = Now()
    WHERE job_id = :someId

When status to be set is - sent

Update message SET status = 'sent', update_date = Now()
    WHERE job_id = :someId AND status IS NULL

Problem is, that it is possible that there could be two threads concurrently trying to set the status of same record to 'sent' and 'delivered'. In this case 'delivered' is the final status so we would want that to win eventually.

Will the above statements ensure this in MySql or MariaDB?

Solution

  • Yes, in both MySQL and MariaDB (and probably any SQL database). updates to the same row are atomic.

    • 'sent' will overwrite NULL, but not 'delivered'
    • 'delivered' will overwrite NULL and 'sent'

    This is what you want. Just make sure message with given job_id exists before updating it :)