首页 文章

使用多个DB结果执行MySQL替换为select以使其死锁

提问于
浏览 次
4

我检查了其他类似的问题，例如堆栈溢出中的“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

情况：

  • 所有表格均为 InnoDB ; db1.table1 => Collation ：latin1_swedish_ci和db2 => Collation ：utf8_unicode_ci

  • query works fine in development server where version is Server version: 10.0.15-MariaDB

  • 假设我有 5 DB servers which shares multi-master replication using Galera cluster .

  • 我正在这5台服务器中的任何一台手动执行查询并收到错误 .

  • That server's version is same as the dev server 查询执行成功，即10.0.15-MariaDB

尝试：

  • 包括 LOCK IN SHARE MODE ，例如REPLACE INTO ...（第一次选择查询（子查询）LOCK IN SHARE MODE）;但它失败了同样的消息 .

  • Insert / REPLACE ...（第一选择查询（子查询LOCK IN SHARE MODE）锁定共享模式）;它也失败了同样的消息 .

  • 在选择查询/子选择查询中尝试使用 ordering by id . 再次失败了相同的消息 .

  • db1.table1和db2.table1都有几乎只有50k的记录，所以这不应该导致任何问题我猜 .

  • 所有表格都有 id as the primary key and auto increament . 但我明确地使用它们 - 请观察查询 .

  • SHOW ENGINE INNODB STATUS ;不会给我添加任何有用的提示 .

最可能的原因可能是 multi-master replication behind the galera cluster for its optimistic locking （http://www.severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads） . 但是 executing the query on an individual node 时不应该失败？虽然取得了成功，但我已经不再创造了问题 .

注意：

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

mysql database deadlock mariadb galera

1 回答

  • 2

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

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

    不要依赖自动增量值来连续 . Galera使用基于自动增量增量的机制来产生唯一的非冲突序列，因此在每个节点上序列都会有间隙 . https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

    • 但是同样的死锁信息仍然是1/10次，这是加莱拉的已知行为 . Galera使用乐观锁定;很少导致死锁;在这种情况下，大多数人建议再次重试该事务 .

    Galera Cluster在集群级别的乐观并发控制中使用，这可能导致在该阶段发出COMMIT中止的事务 . http://galeracluster.com/documentation-webpages/limitations.html

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

    [编辑]

    • 我写了一个post来解释架构，环境，问题以及我如何使用它 . 对面临同样问题的人可能有用 .

    • issue被报告给社区并开放

    回复于

相关问题