Transaction management work under Instance or database

Question

I remember from university that:

By Default, doing Insert, Delete and update on 2 or more data sources can't be participant under a transaction

Please consider this this code:

begin tran Insert2

begin try
    Insert into Northwind.dbo.Categories ([CategoryName], [Description]) 
    values ('New Category', 'Some Desc')
    
    Insert into [TestDB].[dbo].[tblRate]([Year], [Month], [Rate])
    values(1111, 1, null) <-- Failed because of null value

    commit tran Insert2
end try
begin catch
    rollback tran Insert2;
    throw;
End catch

I can't create a script that an Insert perform on Northwind database and another Insert doesn't perform on TestDB but rollback can't delete inserted row on Northwind database.

Question 1) Does transactions manage under a Instance or manage under a Database? I mean multiple database can that exist in a given instance can participant in one transaction?

Question 2) If TestDB exists in another instance of SQL Server (For example MyServer2), Is it possible that include it in one transaction with current instance (For example MyServer1).

Could you please show me a sample code?

Thanks

Answer 1

I remember from university that:

By Default, doing Insert, Delete and update on 2 or more data sources can't be participant under a transaction

This sounds like it was taken out of context, because as it's currently written, without any other details, it's an incorrect statement.

You can apply multiple DML statements (INSERT, UPDATE, DELETE) to multiple data sources under the same transaction. In SQL Server, such transaction just needs to be explicitly defined around all of the DML statements that you want to partake within the same transaction. For example:

BEGIN TRANSACTION

UPDATE TableA
SET Column1 = 0;

INSERT INTO TableB (Column2)
VALUES (123);

ROLLBACK;

In the above example, two different statements are affecting two different tables, and both get rolled back at the end of the transaction.

Without explicitly specifying a transaction around them, then each statement is executed atomically as its own transaction, individually, per statement instead.

I can't create a script that an Insert perform on Northwind database and another Insert doesn't perform on TestDB but rollback can't delete inserted row on Northwind database.

Sorry, I don't understand what you're trying to say here. Can you please provide an example with dbfiddle.uk perhaps?

Question 1) Does transactions manage under a Instance or manage under a Database? I mean multiple database can that exist in a given instance can participant in one transaction?

Yes, multiple databases can partake within the same single transaction. A transaction can be defined to be as granular as you want, or as macroscopic as you want, and include any number of data sources, whether within the same database or not.

Question 2) If TestDB exists in another instance of SQL Server (For example MyServer2), Is it possible that include it in one transaction with current instance (For example MyServer1).

Yes, this is called a distributed transaction.

Could you please show me a sample code?

Sure, here's some code from an example in the aforementioned docs on distributed transactions:

USE AdventureWorks2022;
GO

BEGIN DISTRIBUTED TRANSACTION;

-- Delete candidate from local instance.
DELETE AdventureWorks2022.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;

-- Delete candidate from remote instance.
DELETE RemoteServer.AdventureWorks2022.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;

COMMIT TRANSACTION;

In this example, a deletion happens on the local copy of the table JobCandidate and also on the remote copy of it from another SQL Server instance, all within the same transaction. If an error occurred and a rollback happened, both changes would be undone as well.

Please note, in order to use distributed transactions, the Microsoft Distributed Transaction Coordinator (MS DTC) needs to be enabled and properly configured on the remote server.

Answer 2

If you want to do two changes and the second one should not touch the first - just separate them.

Insert into Northwind.dbo.Categories ([CategoryName], [Description]) 
    values ('New Category', 'Some Desc')

commit -- and the changes to Northwind are permanent

Insert into [TestDB].[dbo].[tblRate]([Year], [Month], [Rate])
    values(1111, 1, null) <-- Failed because of null value
-- if command failed - it rolled back automatically, no need for rollback

Transaction is managed under "session". You connect to the server and until you disconnect - that is a session. Any changes to any number of databases on that server are one single transaction. Technically: you make a change, it is written into dirty pages of the log, you commit - all changes applied to all storage devices managed by that server, you rollback - all the changes written in dirty pages by you in this transaction are abandoned. Does not matter how many databases are included in transaction.

---

If one of your table is actually a linked table to a different server, then each individual update of such table is actually a new session on that secondary server and outside of transaction mechanism.

begin tran
update tbl set f1=1 -- success
update [srv2].db.dbo.tbl2 set primary_key=1 -- failed and autorolled back
commit -- change to local tbl commited

begin tran
update tbl set f1=1 -- success
update [srv2].db.dbo.tbl2 set f2=1 -- success and autocommit
rollback -- change to tbl rolled back, but change to tbl2 on secondary server stays

---

And yes, if you have two "data sources" they cannot be in the same transaction. The "data source" is usually define a connection from a client to a server. And if you have two "data sources" that implies two connections and therefore two independent transactions.