Menu
Would transaction work across multiple DB in the same SQL server?
If so, is this a Distributed transaction? or would basic BEGIN TRANSACTION work?
210
If your data is distributed across multiple databases, you may wish to update one database while reading from one or more other databases. This type of access can be performed within a single unit of work (transaction). This type of database access is called multisite update or two-phase commit.
A distributed transaction spans two or more databases. As the transaction manager, DTC coordinates the transaction between SQL Server instances, and other data sources. Each instance of the SQL Server database engine can operate as a resource manager.
Starting with SQL Server 2014 (12. x), memory-optimized tables do not support cross-database transactions. You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table.
A distributed transaction is a set of operations on data that is performed across two or more data repositories (especially databases). It is typically coordinated across separate nodes connected by a network, but may also span multiple databases on a single server.
A transaction across multiple DBs in the same instance is a local transaction. BEGIN TRANSACTION will work just fine.
190
Just tested it. BEGIN TRANSACTION works as expected across two databases on the same server.
9
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
