Cross-Database Transactions

 Cross-Database Transactions (Within the Same SQL Server Instance)

Definition:

A Cross-Database Transaction is a transaction that spans multiple databases on the same SQL Server instance.

Key Characteristics:

Aspect	Description
Scope	Limited to multiple databases on the same SQL Server instance.
Transaction Control	Managed within a single instance using local transactions (e.g., BEGIN TRANSACTION, COMMIT, ROLLBACK).
Two-Phase Commit (2PC)	NOT required because the SQL Server transaction log ensures atomicity within the same instance.
Performance Impact	Low impact, generally efficient because it operates within a single instance.
DTC (Distributed Transaction Coordinator)	NOT required.
Typical Use Case	Updating multiple databases within the same instance (e.g., Financial Database + Inventory Database).
Rollback Consistency	Fully supported across databases within the same instance.
Example

 

BEGIN TRANSACTION;

UPDATE SalesDB.dbo.Orders SET Status = 'Shipped' WHERE OrderID = 101;

UPDATE InventoryDB.dbo.Stock SET Quantity = Quantity - 1 WHERE ProductID = 50;

COMMIT;