All geek questions in one place

What is the best way to update a closing table?

I have a table relating records using the adjacency list method (table A), and another table refers to the same records using a closing table (table B). They both fix the same chart, so they need to be synchronized.

The question is, what is the best way to update a closing table?

As I can see, there are three alternatives:

  • Triggers On INSERT / UPDATE / DELETE, run sproc, which computes the new closures. Cons: changes result A to a long synchronous (blocking?) Operation; possible deadlocks (?).
  • Application code. Check for changes in A methods for adding / updating / deleting (for example, a repository template) and overload them with sproc calls that calculate new locks. Cons: an additional round of travel to the database; a possible integrity problem if another anad simultaneously changes A or B, vice versa; a possible integrity problem if, say, another application decides to change A, not B.
  • Background updates. Write a secondary process that is constantly looking for updates for A and makes corresponding updates to the closing table. Cons: complex (additional service for writing and managing); windows without synchronization.

Even if there is no “better” option, any thoughts on compromises will be rated most highly!

+4
source share
1 answer

If your hierarchies are like static, like most of them, I would probably go with a trigger. Actually, it depends on the refresh rate and read load.

+3
source

Source: https://habr.com/ru/post/1347318/

More articles:


All Articles