更新闭包表的最佳方法是什么？

I have a table relating records using the adjacency list method (table A), and another table relating the same records using a closure table (table B). They both capture the same graph, so they both need to be kept in sync.

我有一个表使用邻接列表方法(表A)关联记录,另一个表使用闭包表(表B)关联相同的记录。它们都捕获相同的图形,因此它们都需要保持同步。

The question is, what's the best way to update the closure table?

问题是,更新闭包表的最佳方法是什么?

As I see it, there are three alternatives:

在我看来,有三种选择:

1. Triggers. On INSERT/UPDATE/DELETE at A, run a sproc that calculates the new closures. Cons: changes to A result in a long, synchronous (locking?) operation; possible deadlocks (?).

触发。在A上的INSERT / UPDATE / DELETE中,运行一个计算新闭包的sproc。缺点:更改为A会导致长时间的同步(锁定?)操作;可能的死锁(?)。

2. Application code. Narrow down changes in A to Add/Update/Delete methods (eg. a repository pattern), and overload them with calls to a sproc that calculates the new closures. Cons: extra round trip to the DB; possible integrity issue if another thead modifies A or B in a contrary way at the same time; possible integrity issue if, say, another application decides to modify A and not B.

应用代码。缩小A中的更改以添加/更新/删除方法(例如,存储库模式),并通过调用计算新闭包的sproc来重载它们。缺点:DB的额外往返;如果另一个thead同时以相反的方式修改A或B,则可能存在完整性问题;例如,如果另一个应用程序决定修改A而不是B.

3. Background updater. Write a secondary process that continually looks for updates to A and makes the corresponding updates to the closure table. Cons: complex (extra service to write and manage); windows without synchronization.

背景更新程序。编写一个辅助进程,不断查找A的更新并对闭包表进行相应的更新。缺点:复杂(写作和管理的额外服务);没有同步的窗口。

Even if there is no "best" option, any thoughts on the trade-offs would be most appreciated!

即使没有“最佳”选项,任何关于权衡的想法都将受到最大的赞赏!

1 个解决方案

#1

If your hierarchies are anything like as mostly static as most I've dealt with, I would probably go with the trigger. Really depends on the update frequency and the read load.

如果您的层次结构与我所处理的大部分内容完全一样,那么我可能会使用触发器。真的取决于更新频率和读取负载。