以事務方式創建/編輯許多相互關聯的記錄時要採取的方法？

所以我認為我有一個相當複雜的系統，它正在開始出現。寫出所有表格太複雜了，但這裡是一個簡短的描述。

基本上我正在為文章創建一個像 StackOverflow 這樣的徽章系統。你有這些類型的表：

• 使用者
• 文章
• 事件（保存到數據庫，以便您知道每個重要事件何時發生）
• user_statistics（徽章計數匯總等）
• post_statistics（文章上的操作計數匯總，“已編輯 20 次”）
• user_badges（授予使用者徽章）
• 徽章類型

然後假設您“更新文章”。這是發生的事情：

• 文章記錄已更新
• 接下來會創建事件記錄，上面寫著“更新操作”，只有在它不存在時才會創建，否則它會重用相同的更新事件（以免防止向系統發送垃圾郵件）。它與文章 ID 和使用者 ID 相關聯。
• 更新使用者統計資訊以計算新事件（如果已創建）。可能有多個統計資訊需要更新，因為這些統計資訊可能僅限於某些類別（例如特定語言的所有文章）
• 如果統計數據達到門檻值，則檢查我們是否需要創建一個徽章或潛在的多個徽章，然後創建徽章。
• 可能會創建通知記錄。
• 可能還有其他一些事情，例如提升站點上的權限，因為他們有更多的聲譽，等等。
• 所有這一切都需要成功，所以沒有什麼可以做的（所有計數都是正確的，並且徽章被正確授予）。

您如何在 PostgreSQL 中適當地完成此任務？在我的具體情況下，似乎有大約 10 個表被查詢，至少 5 個表被修改（創建或更新記錄）。所有這些在理論上應該是原子的，在一個事務中，但是打包到一個事務中似乎很多，特別是如果你有這些“事件”在一秒鐘內多次出現。

我能想到的唯一可能解決這個問題的初始方法是 使用隊列和後台作業。上述每個步驟都將在事務之外按順序完成，步驟之間可能存在時間間隔。所以會有一個中間狀態，事情是不一致的。但最終（理論上似乎），隊列將執行並重試直到成功，並進入正確的狀態。這應該怎麼做？

如果不是，是否可以在每個事件上進行單一交易的複雜性？我不知道，我不認為實施徽章和這些計數器的解決方案會變得如此復雜，但每個事件都有很多需要考慮和做的事情。基於您建構可擴展數據庫系統的專業知識，我正在尋找任何指向正確方向的指針。

假設這個系統一定是這麼複雜，因為我在理論上真的是在問如何處理複雜的事務需求。也就是說，如果您知道一種對徽章系統進行建模的理想方法，那會很高興知道，但不會真正解決問題的主要部分。感謝您的幫助！

目前，就我的目的而言，一切都可以被認為適合單台機器，而不是分佈在多個數據庫中。

同意 Charlieface 在評論中的觀點，原因如下：

1. “特別是如果你有這些“事件”在每秒多次出現“ - 如果整個事務只需要 10 毫秒的執行時間，你可以讓這些事件每秒發生 100 次，並且 0 阻塞爭用。大多數查詢在針對正確索引的表進行正確架構時，執行時間不應超過幾毫秒到幾百毫秒。
2. “可以在每個事件上進行這種複雜的單一交易嗎？ ” - 是的。但是，當有第三種選擇 - 多個事務時，您似乎只提到了涉及全部或全部事務的解決方案。您應該只將需要立即在事務上保持一致的對象放在同一個事務中。因此，根據您描述的工作流程，我會說事務可以包裝記錄的更新Post和創建Event。第二個事務可以只包裝不同統計表的更新以及這些統計資訊的相關操作，例如創建Badge和創建Notification給使用者。第三個事務可以處理確保所有相關表更新的特權更改是原子的。
3. 此外，就 Charlieface 而言，匯總和統計數據通常不需要在 100% 的時間內 100% 準確。如果您遵循我之前的交易模式，並且在極少數情況下更新統計表的交易失敗，您仍然應該每晚（或任何有意義的節奏 - 可以是每小時，可以是每周等）重新計算這些統計數據以解決任何後果的工作。這樣您就可以兩全其美：大多數情況下數據是原子準確的，在極少數情況下並非如此，最終會如此，而且還可以通過將單個事務分解為多個較小的事務來提高工作流程性能，這些事務將鎖定所有當程序執行時，這些數據庫對象的時間範圍甚至更短。

引用自：https://dba.stackexchange.com/questions/313863