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