mysql - 使用多个数据库结果执行 MySQL replace into select 死锁

Question

我检查过其他类似的问题，例如堆栈溢出中的“MySQL 中的死锁”，但没有找到解决方案。

REPLACE INTO db2.table2 (id, some_identifier_id, name, created_at, updated_at) (SELECT id, some_identifier_id, name, created_at, updated_at FROM db1.table1 WHERE some_identifier_id IS NOT NULL AND some_identifier_id NOT IN (SELECT some_identifier_id FROM db2.table1 WHERE some_other_identifier_id IS NOT NULL));

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

情况:

1. 所有的表都是InnoDB； db1.table1 => 排序规则:latin1_swedish_ci 和 db2 => 排序规则:utf8_unicode_ci
2. 查询在版本为服务器版本的开发服务器中运行良好:10.0.15-MariaDB
3. 假设我有 5 个数据库服务器，它们使用 Galera 集群共享多主复制。
4. 我在这 5 台服务器中的任何一台上手动执行查询并收到错误。
5. 该服务器的版本与查询执行成功的开发服务器相同，即 10.0.15-MariaDB

尝试过:

1. 包括锁定共享模式，例如REPLACE INTO...(第一个选择查询(子查询)锁定共享模式)；但它失败并显示相同的消息。
2. 插入/替换 ...(第一个选择查询(子查询锁定共享模式)锁定共享模式)；它也失败并显示相同的消息。
3. 尝试在选择查询/子选择查询中使用按 ID 排序。再次失败并显示相同的消息。
4. db1.table1 和 db2.table1 几乎都只有 50k 条记录，所以我想这不会造成任何问题。
5. 所有表均以id为主键并自增。但我以某种方式明确地使用它们 - 请观察查询。
6. 显示引擎 INNODB 状态；没有给我任何有用的提示。

最可能的原因可能是由于galera 集群背后的多主复制对其乐观锁定 ( http://www.severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads )。但是当在单个节点上执行查询时，这不应该失败吗？虽然成功后我必须在多主复制中执行相同的操作，但我想如果基本问题得到解决，那么复制的服务器将不会再产生问题。

注意:

我需要在没有任何临时表或将子查询的结果存储在代码中的情况下执行此操作。到目前为止，还有一些其他依赖项执行单个查询是最有利的方式。

Answer 1

好的，我找到了一个解决方法。根据我的研究和测试，我认为这次失败背后有两个问题。

1. replace into 查询正在同步 id 以及从 db1.table1 到 db2.table2 的其他必需字段。 Insert/Replace auto-incremental primary key 是 galera 中最可能和最明显的死锁原因。我已从该查询中删除 id 并将 some_identifier_id 保留为支持相同替换查询的唯一键。它几乎阻止了死锁错误。

Do not rely on auto-increment values to be sequential. Galera uses a mechanism based on autoincrement increment to produce unique non-conflicting sequences, so on every single node the sequence will have gaps. https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

1. 但同样的死锁消息仍然出现 1/10 次，这是 Galera 的已知行为。 Galera 使用乐观锁；很少导致死锁；在这种情况下，最建议再次重试交易。

Galera Cluster uses at the cluster-level optimistic concurrency control, which can result in transactions that issue a COMMIT aborting at that stage. http://galeracluster.com/documentation-webpages/limitations.html

在一个要点中，查询在单个服务器中成功运行，但是当它是 galera 时，失败就来了。从该查询中删除自动增量主键并处理相同的事务以在死锁时重新启动解决了这个问题。

[Edit]

1. 我写了一个 post解释架构、环境、问题以及我如何使用它。可能对面临相同问题的人有用。

2. issue向社区报告并公开