MariaDB 10.1 如何將 SELECT 查詢相互隔離？

鑑於下表..

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;

..和連接1和2同時做（與autocommit=1）..

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

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

..導致數據1234獲取 ID 101 並5678獲取 ID 102 的事件。

以下查詢是否有可能同時在連接 2 或第三個連接上執行將返回 ID 為 102 的事件？如果是，為什麼會這樣，有什麼可以做的嗎？

(
 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

我無法在我自己的環境中重現這一點，但我相信這已經在生產中發生了。使用 Ubuntu 18.04 和它提供的 MariaDB 10.1 包。閱讀有關InnoDB 的隔離級別和自動增量處理的資訊，我找不到任何會發生這種情況的原因。

從一般的角度來看：

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

是一個 SET（即無序）。可以從此查詢返回與謂詞匹配的任何行。讓我們將此行稱為 R1

以同樣的方式：

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

是一個集合，但你有一個 ORDER BY id。因此，結果行將是滿足謂詞的行，具有最小的 if。讓我們將此行稱為 R2

在您的外部查詢中，您建構 R1 和 R2 的並集，然後選擇其中任何一個。

如果您更喜歡聯合中的一條腿而不是另一條腿（我將在這裡使用第一條腿），您可以添加一個 origin 屬性，例如：

(
   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

請注意，現在兩條腿是不相交的，因此聯合永遠不會刪除重複項（這是語義上的差異，不知道這是否重要）

我還建議您在第一個查詢中添加“排序依據”，以使其具有確定性。

如果您不希望原點成為結果的一部分，則可以選擇最外層的其他列。

“102”到“101”之前不太可能，但可能。COMMIT

引用自：https://dba.stackexchange.com/questions/299392