How are SELECT queries isolated from each other with MariaDB 10.1?

Question

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.

Answer 1

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.

Answer 2

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