跨多种技术的ACID事务

Question

我有一个应用程序，它使用本地数据库和远程数据库进行同步。本地数据库使用SQLite，对于远程数据库，我使用postgres。我需要将数据从一个数据库移动到另一个数据库，并避免重复信息。

我现在要做的大致如下：

BEGIN;                                       //remote database  (start transaction)
SELECT * FROM local.queued TOP 1;            //local database   (select first queued element)
INSERT INTO remote.queued VALUES ( element ) //remote database  (insert first queued element on remote database)
BEGIN;                                       //local database   (start transaction)
DELETE * FROM local.queued LIMIT 1;          //local database   (delete first queued element on local database)
END;                                         //local database   (finalize transaction local database)
END;                                         //remote database  (finalize transaction remote database)

这在大多数情况下工作得相对较好，但顺便说一句，在对程序进行硬重置后，我注意到数据记录被复制了。我相信这与交易的最终完成有关。因为我使用了两种不同的技术，所以不可能使用WAL归档创建单个原子提交。

任何想法，我可以改进这个概念，以避免重复输入。

Answer 1

执行此操作的规范方法是使用两阶段提交协议的分布式事务。

不幸的是，SQLite似乎不支持它，但由于PostgreSQL支持，如果只涉及两个数据库，您仍然可以使用它：

BEGIN;  -- on PostgreSQL

              BEGIN;  -- on SQLite

   /*
    * Do work on both databases.
    * On error, ROLLBACK both transactions.
    */

PREPARE TRANSACTION 'somename';  -- PostgreSQL

              COMMIT;  -- SQLite

COMMIT PREPARED 'somename';  -- PostgreSQL

现在，如果在SQLite COMMIT期间发生错误，您可以在PostgreSQL上运行ROLLBACK PREPARED 'sonename'。其思想是，在提交期间可能失败的所有事情都在PREPARE TRANSACTION期间完成，事务的状态被持久化，以便它保持打开状态，但在服务器重新启动后仍然可以存活。

这是安全的，但有一个警告。准备好的事务是危险的，因为它们将持有锁并阻止VACUUM清理(就像所有其他事务一样)，但它们是持久的，并且会一直存在，直到您显式地删除它们。因此，您需要一些软件，一个分布式事务管理器，它是崩溃安全的，并跟踪所有分布式事务。此事务管理器可以在一些中断后清理所有准备好的事务。

Answer 2

我认为让您的DML操作幂等是有意义的-也就是说，如果您多次调用它们，它们具有相同的整体效果。例如，如果数据存在，我们可以将INSERT设为无操作：

INSERT INTO x(id, name)
SELECT nu.id, nu.name
FROM
  (SELECT 1 as id, 'a' as name) as nu
  LEFT JOIN x ON nu.id = x.id
WHERE
  x.id IS NULL

您可以任意多次运行此命令，并且它只会插入一条记录

https://www.db-fiddle.com/f/nbHmy3PVDQ3RrGMqLni1su/0

你需要决定如果记录以改变的状态存在时该怎么做--例如，你想让它保持原样，还是将它重置为传入的值--这是另一次的问题