镜像表:触发器、死锁和隐式提交

I'm have 2 similar tables, for example A and B. I want to replicate insertions in A to B, and insertions in B to A to integrate two users systems . I configured "after insert triggers" on each one. Example:

我有两个类似的表，例如A和B，我想复制A到B的插入，以及B到A的插入来集成两个用户系统。我在每个触发器上配置了“后插入触发器”。例子:

DELIMITER $$
CREATE DEFINER = `root`@`localhost` TRIGGER
`after_A_INSERT`
AFTER INSERT ON `A`
FOR EACH ROW BEGIN 
INSERT INTO `B`
SET `id` = NEW.`id`,`name` = NEW.`name`;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER = `root`@`localhost` TRIGGER
`after_B_INSERT`
AFTER INSERT ON `B`
FOR EACH ROW BEGIN 
INSERT INTO `A`
SET `id` = NEW.`id`,`name` = NEW.`name`;
END$$
DELIMITER ;

If I insert in A, the triggers calls an insert in B, but this insert executes the trigger in B and a deadlock occurs avoiding a infinite loop.

如果我插入A，触发器调用B中的插入，但是这个插入在B中执行触发器，死锁发生，避免了无限循环。

I've tried to edit triggers to DROP the another table trigger before do the INSERT and then CREATE it again after it. Example:

我尝试过编辑触发器，在插入之前删除另一个表触发器，然后在插入之后再创建它。例子:

DELIMITER $$
CREATE DEFINER = `root`@`localhost` TRIGGER
`after_B_INSERT`
AFTER INSERT ON `B`
FOR EACH ROW BEGIN 
DROP TRIGGER IF EXISTS `after_A_INSERT`;
INSERT INTO `A`
SET `id` = NEW.`id`, `name` = NEW.`name`;
/* And CREATE again here */
END$$
DELIMITER ;

However CREATE is a Data Definition Language (DDL) statement that makes an implicit commit. Thus, this can't be done.

然而，CREATE是一个数据定义语言(DDL)语句，可以进行隐式提交。因此，这是不可能的。

I've tried to call a PROCEDURE with the DROP inside to handle explicitly the commit, but isn't possible too.

我尝试调用一个内部带有DROP的过程来显式地处理提交，但也不可能。

Any suggestion to mirror this 2 tables?

有什么建议吗?

---

UPDATE: Using Bill Karwin suggestion, I added a origin field to each table with a respective default vale A or B. Then, I alter (DROP and reCREATE) the triggers as follows:

更新:使用Bill Karwin的建议，我在每个表中添加了一个origin字段，每个表对应一个默认的vale a或b。

Trigger in A:

在一个触发器:

...
BEGIN 
IF NEW.`origin`='A' THEN
    INSERT INTO `B`
        SET `id` = NEW.`id`, `name` = NEW.`name`,  `origin` = NEW.`origin`;
    END IF;
END

Trigger in B:

在B触发:

...
BEGIN 
IF NEW.`origin`='B' THEN
    INSERT INTO `A`
        SET `id` = NEW.`id`, `name` = NEW.`name`, `origin` = NEW.`origin`;
    END IF;
END

1 个解决方案

#1

You need some way to avoid creating a cycle.

您需要某种方法来避免创建循环。

I'd suggest adding a column origin in both tables. In table A, make the DEFAULT 'A'. In table B, make the DEFAULT 'B'.

我建议在两个表中添加一个列原点。在表A中，设置默认的“A”。在表B中，设置默认的“B”。

When inserting to either table in your application, always omit the origin column, allowing it to take its default value.

当插入到应用程序中的任意一个表时，总是忽略origin列，允许它取其默认值。

In both triggers, replicate to the other table only if the NEW.origin is equal to the respective table's default.

在这两个触发器中，仅当新表时才复制到另一个表。原点等于各自的表的默认值。

---

Re your comment and new error:

你的评论和新的错误:

Sorry, I forgot to mention that in the trigger when inserting to the other table, you must also copy the value of NEW.origin. Just in your application when you do the original insert do you omit origin.

对不起，我忘了在向另一个表插入时在触发器中提到，您还必须复制NEW.origin的值。在你的应用程序中，当你做原始插入时，你是否省略了原点。

Trigger in A:

在一个触发器:

...
BEGIN 
IF NEW.`origin`='A' THEN
    INSERT INTO `B`
        SET `id` = NEW.`id`, `name` = NEW.`name`, `origin` = NEW.`origin`;
    END IF;
END

Trigger in B:

在B触发:

...
BEGIN 
IF NEW.`origin`='B' THEN
    INSERT INTO `A`
        SET `id` = NEW.`id`, `name` = NEW.`name`, `origin` = NEW.`origin`;
    END IF;
END

I created these triggers and then tested:

我创建了这些触发器，然后测试:

mysql> insert into A set name = 'bill';
Query OK, 1 row affected (0.00 sec)

mysql> insert into B set name = 'john';
Query OK, 1 row affected (0.01 sec)

mysql> select * from A;
+----+------+--------+
| id | name | origin |
+----+------+--------+
|  1 | bill | A      |
|  2 | john | B      |
+----+------+--------+
2 rows in set (0.00 sec)

mysql> select * from B;
+----+------+--------+
| id | name | origin |
+----+------+--------+
|  1 | bill | A      |
|  2 | john | B      |
+----+------+--------+