跨多個數據庫的事務 - 成本是多少？

我執行以下更新，這是跨 2 個數據庫的單個事務。

請注意我還沒有送出交易。

--================================================================
--RUN THE UPDATE
--================================================================

BEGIN TRANSACTION T1_radhe

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT @@TRANCOUNT

BEGIN TRY

UPDATE DEStock.DBO.ItemStock 
SET     QtyOnOrder = 0 ,
       DueDate = NULL
FROM DEStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
       ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating DEStock.DBO.ItemStock '


UPDATE USStock.DBO.ItemStock 
SET     QtyOnOrder = 0 ,
       DueDate = NULL
FROM USStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
       ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating USStock.DBO.ItemStock '



--COMMIT TRANSACTION T1

END TRY
BEGIN CATCH

   DECLARE @ErrorMessage NVARCHAR(4000);
   DECLARE @ErrorSeverity INT;
   DECLARE @ErrorState INT;

   WHILE @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

   SELECT 
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();

   RAISERROR (@ErrorMessage, -- Message text.
              @ErrorSeverity, -- Severity.
              @ErrorState -- State.
              );
END CATCH

我從上面的更新中得到以下結果：

（196 行受影響） 196 更新 DEStock.DBO.ItemStock

（196 行受影響） 196 更新 USStock.DBO.ItemStock

但是當我查看相關數據庫的事務日誌時，我對它們的日誌有相同的看法：

每個數據庫中的 spid 都是相同的，6434，但是每個數據庫中的 transaction_id 似乎不同。

怎麼會這樣？

此外，當我在上面的更新中執行 ROLLBACK 之後執行以下腳本時，我仍然得到與 ROLLBACK 之前相同的結果。

我必須添加選項重新編譯，以便我可以看到事務不再存在。

use DEStock
go
select db_name(),@@trancount
go
Select SPID, [Master DBID], [Master XDESID], Operation, Context, [Transaction ID]
FROM sys.fn_dblog(NULL,NULL) where  [Transaction Name] = 'T1_radhe'
option (recompile)

跨多個數據庫進行事務的成本是多少？我經常使用這個，因為我有很多市場；每個市場都有一套數據庫。

有沒有辦法讓這些交易執行得更快？我在考慮分佈式交易。

關節上使用的表來自這裡： 如何將 varchar 值列表插入到單列表中？

此連結解釋了函式 fn_dblog： SQL Server fn_dblog() 函式詳細資訊和範例

每個數據庫中的 spid 相同，為 6434，但是每個數據庫中的 transaction_id 似乎不同。

怎麼會這樣？

您所擁有的是錯誤的（可能是您正在查看具有相同名稱的舊交易）。

從我的測試中，我可以看到交易 ID 匹配（見下文）

使用sp_WhoIsActivewith @get_transaction_info = 1，我得到以下交易細節

test_HV: 15 (1 kB),tempdb: 0 (0 kB),AdventureWorks_2005: 0 (0 kB)

您可以使用以下方法輕鬆檢查數據庫是否是跨數據庫事務的一部分

-- check if database is involved in Cross database transactions
if exists (
       select *
       from fn_dblog(null, null)
       where Operation = 'LOP_PREP_XACT'
           and [Master DBID] <> 0
       )
   print 'Based on the active part of the transaction log read, there is evidence that this database has participated in cross-database transactions.'
else
   print 'Based on the active part of the transaction log read, there is no evidence of this database having participated in cross-database transactions.'

---- check for Distributed Transaction Coordinator involvement (below)
if exists (
       select *
       from fn_dblog(null, null)
       where Operation = 'LOP_PREP_XACT'
           and [Master DBID] = 0
       )
   print 'Based on the active part of the transaction log read, there is evidence that this database has participated in distributed transactions.'
else
   print 'Based on the active part of the transaction log read, there is no evidence of this database having participated in distributed transactions.'

跨多個數據庫進行事務的成本是多少？

交易本身沒有任何相關成本（或者更具體地說，它非常非常微不足道）引用我的回答：

需要事務將數據庫從一種一致狀態轉變為另一種一致狀態。交易沒有成本，因為沒有交易的替代品。

對數據庫的任何修改都會生成日誌記錄，使用記憶體和 CPU 以及 tempdb 空間（取決於您是否進行大排序，然後更新或刪除記錄，然後會看到溢出到 tempdb，或者您是否使用快照隔離級別，等等）。

有沒有辦法讓這些交易執行得更快？

是的，批量或分塊進行更新。首先只選擇和更新那些批次，並確保您在所涉及的表上有正確的索引，以便 sql server 只會查找行並更新它們。

我在考慮分佈式交易。

我會避免分佈式事務，因為它們在數據庫鏡像或 AlwaysON 中不受支持（SQL Server 2016 的支持有限），如果你幸運的話- 你會得到一個孤立的 DTC 事務 SPID。

作為旁注，

• 如果您sys.fn_dblog用於教育目的或在非 PROD 環境中使用，您應該很好。我會避免在 PROD 環境中使用它！
• 您正在使用SET TRANSACTION ISOLATION LEVEL REPEATABLE READ. 這意味著您將有幻像行（這可能會導致意外結果） - 所以只要確保您知道您正在使用什麼。

與已送出讀掃描不同，可重複讀掃描在其接觸的每一行上都保留鎖，直到事務結束。即使是不符合查詢結果的行也保持鎖定狀態。這些鎖確保在目前事務完成（無論是送出還是回滾）之前，並發會話不能更新或刪除查詢所觸及的行。這些鎖不會保護尚未掃描的行不被更新或刪除，也不會阻止在已鎖定的行中插入新行。

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