1

Given the following tables..

CREATE TABLE `event` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `data` longblob NOT NULL,
  `acknowledged` tinyint(4) NOT NULL DEFAULT '0'
  `delivery_failed` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `acknowledged_delivery_failed_type` (`acknowledged`,`delivery_failed`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `delayed_event` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `data` longblob NOT NULL,
  `delivery_time` datetime NOT NULL,
  `last_value_key` varchar(255) COLLATE utf8_general_ci DEFAULT NULL,
  `acknowledged` tinyint(4) NOT NULL DEFAULT '0',
  `delivery_failed` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `type_last_value_key` (`type`,`last_value_key`),
  KEY `acknowledged_delivery_failed_type_delivery_time` (`acknowledged`,`delivery_failed`,`type`,`delivery_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

..and connection 1 and 2 concurrently doing (with autocommit=1)..

INSERT INTO event (type, data) VALUES ('foo', 1234);
INSERT INTO event (type, data) VALUES ('foo', 5678);

..resulting in the event with data 1234 getting ID 101, and 5678 getting ID 102.

Is there any chance that the following query run at about the same time on either connection 2 or on a 3rd connection will return the event with ID 102? If yes, why would that be, and is there anything that can be done about it?

(
  SELECT id, type, data, 1 AS delayed
  FROM delayed_event
  WHERE acknowledged = 0 AND delivery_failed = 0
    AND type = 'foo' AND delivery_time <= UTC_TIMESTAMP()
  ORDER BY id
  LIMIT 1
)
UNION
(
  SELECT id, type, data, 0 AS delayed
  FROM event
  WHERE acknowledged = 0 AND delivery_failed = 0
    AND type = 'foo' AND id > 100
  ORDER BY id
  LIMIT 1
)
ORDER BY id
LIMIT 1

I am unable to reproduce this in my own environment, but I believe this has happened in production. Using Ubuntu 18.04 and the MariaDB 10.1 package it provides. Reading about isolation levels and auto increment handling for InnoDB, I can't find any reason why this would happen.

6
  • Could there be a "102" in delayed_event?
    – Rick James
    Sep 12, 2021 at 6:41
  • In this case, it doesn't matter, because the application knows the difference. I realize I left out one critical part in that regard, the first subquery has an additonal constant 1 in the SELECT, and the second subquery sets the same field to 0. SELECT id, type, data, 1 AS delayed_event ....
    – Sebastian
    Sep 13, 2021 at 5:07
  • Add three ORDER BYs so that it can be deterministic, then I will look at it again.
    – Rick James
    Sep 13, 2021 at 6:17
  • Ok, added ORDER BY in the first subquery and for the UNION.
    – Sebastian
    Sep 13, 2021 at 6:44
  • It is unlikely, but possible, for "102" to COMMIT before "101".
    – Rick James
    Sep 13, 2021 at 7:07

2 Answers 2

0

From a general point of view:

 (
     SELECT id, type, data
     FROM delayed_event
     WHERE acknowledged = 0 AND delivery_failed = 0 AND type = 'foo' AND delivery_time <= UTC_TIMESTAMP()
     LIMIT 1
 )

is a SET (i.e. unordered). Any row that matches the predicates may be returned from this query. Let's call this row R1

In the same way:

(
    SELECT id, type, data
    FROM event
    WHERE acknowledged = 0 AND delivery_failed = 0 AND type = 'foo' AND id > 100
    ORDER BY id
    LIMIT 1
)

is a set, but you have an ORDER BY id. So the resulting row will be the row that satisfies the predicates, with the smallest if. Let's call this row R2

In your outer query, you construct the union of R1 and R2 and then pick any one of them.

If you prefer one leg over the other (I'll use the first one here) in the union, you can add an origin attribute like:

(
    SELECT id, type, data, 1 as origin
    FROM delayed_event
    WHERE acknowledged = 0 AND delivery_failed = 0 AND type = 'foo' AND delivery_time <= UTC_TIMESTAMP()
    LIMIT 1
)
UNION
(
    SELECT id, type, data, 2 as origin
    FROM event
    WHERE acknowledged = 0 AND delivery_failed = 0 AND type = 'foo' AND id > 100
    ORDER BY id
    LIMIT 1
)
ORDER BY origin
LIMIT 1

Note that the two legs now are disjoint so union will never remove duplicates (which is a semantic difference, don't know if that matters)

I would also advise you to add an "order by" to your first query for it to become deterministic.

If you don't want the origin to be part of the result, you can select the other columns at the outermost level.

6
  • Thanks for the insights, but I'm not sure if this answers the question. For the sake of the question, assume that the delayed_event table is always empty. I just included it to make sure that the example reflects reality as much as possible.
    – Sebastian
    Sep 10, 2021 at 12:10
  • 1
    I see. Here is one possible explanation, don't know if it holds for MariaDB though. Even though you are using auto commit there might still be a chance that 102 is committed before 101 in a race condition. 101_INSERT, 102_INSERT, 102_COMMIT, 101_COMMIT Sep 10, 2021 at 12:21
  • Interesting, so I think that's the million dollar question then, does that hold for MariaDB (10.1)? Eagerly awaiting someone with deep MariaDB expertise :)
    – Sebastian
    Sep 10, 2021 at 12:35
  • I stumbled upon stackoverflow.com/a/14758690/1283519, which seems to say something along the same line. But I'd like to also have some references to actual MariaDB (or MySQL) documentation that would clearly state the same thing.
    – Sebastian
    Sep 10, 2021 at 17:27
  • 3
    There is absolutely nothing that prevents transaction 1 to be committed after transaction 2, in MySQL, MariaDB, or any other SQL database. commit is a separate call issued by the client driver, and there is any number of reasons why it might have a longer delay in connection 1.
    – mustaccio
    Sep 10, 2021 at 18:25
0

It is unlikely, but possible, for "102" to COMMIT before "101".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.