I run the following update, which is one single transaction across 2 databases.
Please note I have not committed the transaction.
--================================================================
--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
I get the following result from the update above:
(196 row(s) affected) 196 updating DEStock.DBO.ItemStock
(196 row(s) affected) 196 updating USStock.DBO.ItemStock
But when I look at the transaction logs of the databases in question I get the same view on their logs:
The spid in each database is the same, 6434, however the transaction_id seems to be different in each database.
How is it so?
Also, when running the following script AFTER I did the ROLLBACK on the updtes above I was still getting the same results as before the ROLLBACK.
I had to add the option recompile so that I could see the transaction was not there anymore.
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)
What is the overhead of having transactions across multiple databases? I work with this quite often because I have many markets; each market has a set of databases.
Is there a way to make these transactions run quicker? I was thinking about DISTRIBUTED TRANSACTIONS.
The table used on the joint comes from here: how to insert a list of varchar values, into a single column table?
This link explains about the function fn_dblog: SQL Server fn_dblog() Function Details and Example