## My Confusion I have been reading up about the Try Confirm Cancel (TCC) Protocol, and I understand the main idea behind it (for the happy flow). My confusion comes in how do we actually implement or manage this in the edge cases (when a service unexpectedly crashes). Suppose we have 3 microservices: S1, S2, S3. Suppose S1 and S2 provide the TCC interface as APIs, while S3 is the service which actually calls S1 and S2 and manages the transaction. Assuming T to represent time, I have listed some scenarios below which seem to be problematic:

Scenario 1
T0: Client calls S3 to trigger a flow which uses TCC.
T1: S3 calls TRY for S1. This succeeds.
T2: S3 crashes

In Scenario 1 above, when S3 recovers from the crash, how will it know to call CANCEL for S1? Another Scenario which faces the same issue:

Scenario 2
T0: Client calls S3 to trigger a flow which uses TCC.
T1: S3 calls TRY for S1 and S2. This succeeds.
T2: S3 calls CONFIRM for S1
T2: S3 crashes

In Scenario 2 above, when S3 recovers from the crash, how will it know to call CONFIRM for S2? The articles I've read on TCC never seem to address the issues highlighted above. ## My Proposed Solution I was thinking the only way to solve this is to somehow store a representation of the transaction that is supposed to take place. Taking the same example above, suppose S3 is a microservice trying to coordinate the transfer of money between 2 different banks S1 and S2. The flow would be something like this: - T0: Client calls S3 to trigger transfer of money between 2 bank accounts. - T1: S3 stores a record in DB first before making any calls to S1 and S2 (the status field will change according to which stage the TCC is in): | from_user_id | to_user_id | amount | status | | -------------- | ------------ | -------- | -------- | | 100 | 101 | 56 | TO_TRY | - T2: S3 calls TRY for S1 and S2. - T3: If both TRY succeeds, S3 should update the status field of the DB record to TO_CONFIRM. If either TRY fails, S3 should update the status field of the DB record to TO_CANCEL. Lets assume the cancellation scenario: | from_user_id | to_user_id | amount | status | | -------------- | ------------ | -------- | -------- | | 100 | 101 | 56 | TO_CANCEL | - T4: S3 calls CANCEL for S1 and S2. - T5: If both CANCEL succeeds, S3 should update the status field of the DB record to COMPLETED. Otherwise, just leave it in TO_CANCEL. The idea behind this solution is that if S3 crashes at some point, we can have a cronjob to pick up the records in the DB that are stuck in non-COMPLETED statuses, and try to push them to the COMPLETED status (making sure that either the TRY, CANCEL or CONFIRM phase eventually follows through). Is my proposed solution a legitimate solution? I'm assuming there are already existing solutions to this, but I can't seem to find anything on this issue. Any useful articles or resources would be greatly appreciated!

I've come across a bug using the TransactionScope from the .NET library, where we're getting a TransactionInDoubt exception. After doing a lot of research into something I don't know much about, I've figured this happens when there is a communication error between the database, and we're unsure if the transaction completed. Trying to handle this error quickly became a pain however, there seem to be a lot of possible problems that arise. Basically, I want to know if there is a guarantee that a distributed transaction across multiple databases will be committed or rolled-back on ALL databases, not committed on one and not the another. From my knowledge, the MSDTC controls a distributed transaction and handles this all for me. However reading up on it a little, it seems if there is a communication problem while trying to confirm the commit to one of the databases, then the commit won't be performed for that database, but the others will be committed. There are plenty of resources online that discuss this, and it's often a lot of conceptual talk, my simple question is: Can I rely on the MSDTC, or do we have to come up with a separate solution to the problem?

A Tale As Old As Time... - I have a Linked Server setup to a 3rd party database which I have no ownership or access to otherwise. Lets call it ServerB. I have a stored procedure (SomeStoredProcedure) that selects from Linked Server ServerB. If I explicitly set the isolation level to SERIALIZABLE and then try to insert the results of SomeStoredProcedure into a local temp table, I get the following error: > OLE DB provider "MSOLEDBSQL" for linked server "ServerB" returned message "The parameter is incorrect.". > > Msg 7399, Level 16, State 1, Line 1 > > The OLE DB provider "MSOLEDBSQL" for linked server "ServerB" reported an error. One or more arguments were reported invalid by the provider. > > Msg 7391, Level 16, State 2, Line 1 > > The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "ServerB" was unable to begin a distributed transaction. If I just execute the procedure directly (without inserting the results into a local temp table) it works. If I don't use the SERIALIZABLE isolation level, it also works. (Other explicit isolation levels work as well.) I have tried disabling Enable Promotion of Distributed Transactions for RPC as mentioned in other answers: But no dice, same error: I understand that the query wants to promote to a distributed transaction for the above scenario since a Linked Server is involved (I assume enforcing SERIALIZABLE isolation is more involved across a remote server). But is it possible to prevent it from promoting to a distributed transaction under these circumstances? The same issue is reproducible using sp_executesql to select from the Linked Server as well. Repro code for example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

DROP TABLE IF EXISTS #LocalTempTable;
CREATE TABLE #LocalTempTable (ID INT);

INSERT INTO #LocalTempTable (ID)
EXEC sp_executesql N'SELECT ID FROM ServerB.DatabaseName.SchemaName.SomeTable;';

*Reminder: I don't own this 3rd party server, and can't change any settings on it such as actually enabling the MSDTC.

I've a distributed transaction between two bases (let's call them *data* and *history*) (on the same server, so no MSDTC). I can't find any doc about how the transaction will be saved on both trn files and how it will link them forever. Can I restore *data* DB without *history*? Will it fail? What are the impacts?

I'm studying distributed systems/DBs and I'm struggling understanding isolation levels when we talk about distributed systems. Avoiding problems like dirty read, non-repeatable read, phantom reads, write-skew, etc. when we have a single DB is pretty straightforward with the introduction of optimistic / pessimistic concurrency control algorithms. Nevertheless, I'm really not understanding how the same problems are avoided when we deal with distributed systems. ## Example Let's say that we have three total nodes (*N = 3*) for our DB and we want strong consistency for some reason (*R = 2* and *W = 2*, so *R + W > N*). Let' say now that we have two transactions: T1, T2. - T1:

SELECT * FROM X WHERE X.field = 'something'

   ... DO WORK ...

   SELECT * FROM X WHERE X.field = 'something'

- T2:

INSERT INTO X VALUES(..,..,..)   -- impact T1 search criteria

T2 will commit while T1 is in "DO WORK" phase, so we will have a *phantom read* problem. ## Question How is this situation handled in the illustrated system above? Do systems like this use 2PC-like algorithm and rely on the fact that one transaction will fail in one node due to the R+W>N constraint? If yes, is it a used solution? I would say that this is complex (when we have to rollback the committed transaction in Node_X) and it is also slow probably. Do you have any useful material that I can check to continue studying this topic? I really cannot find much about this, there is very few material that discusses isolation level in distributed systems. Feel free to correct the above if I made a mistake. Thank you.

Per this article , a Publisher can only have one remote Distributor in doing SQL Server Transactional Replication. > Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor. If this is the case, I am wondering how to scale out the publication of a database to a large number of Subscribers. Supposing a Publisher had 200 Subscribers with a large number of transactions, what would be a workable distribution architecture that could handle that load? Scenario is 'one source of truth' - up to 200 read-only slaves in different geographical areas that each get a slice of the central source, based on data pertinent to their local area (filtered rows). All writes are made to the central master, which then need to propagate down to the local slave in as close to instant as possible. I've done a bit of research and transactional replication seems to be the best bet.

If I have done functional partitioning in my database and have for example a database Transactions and a database Products and these are different database servers and there are references from tables in the Transactions schema to tables in the products: If I have a transaction that involves/updates/deletes rows from tables both databases is that executed within a two phase commit protocol and is that done automatically under the hood by MySQL or some how the client program has to be involved? In my understanding the client program has to be involved in XA transactions but my scenario is not of that kind. So how would that work?

I want to know limitations or disadvantages of enabling 'Require distributed transactions for server-to-server communication' on SQL SERVER 2012 please? My SQL database Server 2012 have a subscriber database for Transnational replication config and there are some other production databases as well.

I'm doing data exchange and Insert update operations over the linked server between two database servers. To allow distributed transaction, I've changed settings in Windows to enable MS DTC services. I want to make sure there is not security risk after enabling it for distributed transaction. https://technet.microsoft.com/en-us/library/cc753620(v=ws.10).aspx https://serverfault.com/questions/61269/security-risk-of-enabling-msdtc

With SQL Server 2017 and higher, can using "Per Database DTC Support" help mitigate disruptions to connected applications or will the queries running on Node1 at time of failover to Node2 error out? Is there any way to gracefully "hand off" running transactions from node to node?

I would like to know more about what happens behind the scenes with an auto-commit transaction when performing a cross-server query on a linked server. I naïvely think that when executing an auto-commit transaction the compiler/SQL Server/something else just prepends all statements with a BEGIN TRANSACTION and appends all statements with a COMMIT TRANSACTION since everything is technically enclosed in a transaction (https://dba.stackexchange.com/questions/43254/is-it-a-bad-practice-to-always-create-a-transaction?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) . I'm sure this is incorrect and the source of my confusion as to why performing a cross-server UPDATE without explicitly stating BEGIN TRANSACTION works but explicitly stating one does not. According to Microsoft (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017) , an explicit BEGIN TRANSACTION on an UPDATE query that references a table on a linked server gets escalated to a distributed transaction; and since distributed transactions aren't configured on the linked server, I receive an error. How does the auto-commit setting avoid this? How does it not get escalated to a distributed transaction? Does the auto-commit setting send the data to the linked server but doesn't "listen" for a response from the linked server via Microsoft Distributed Transaction Coordinator (MS DTC); thus if an error occurs, it "silently" fails? Auto-commit doesn't escalate to a distributed transaction: UPDATE l SET l.RecordKey = s.RecordKey FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s ON l.Value1 = s.Value1; Explicit transaction does escalate to a distributed transaction (and errors in my case): BEGIN TRANSACTION UPDATE l SET l.RecordKey = s.RecordKey FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s ON l.Value1 = s.Value1; COMMIT TRANSACTION; I should add that the statements were run in SQL Server Management Studio.

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

Can someone help me to troubleshoot this error? > Error:OLE DB provider "SQLNCLI11" for linked server "TESTSERV" > returned message "No transaction is active.". Msg 7391, Level 16, > State 2, Line 7 The operation could not be performed because OLE DB > provider "SQLNCLI11" for linked server "TESTSERV" was unable to begin > a distributed transaction. I am getting the error executing the code below. CREATE TABLE #Drive ( drive char(1) Primary Key, freespace int null ) Insert into #Drive EXEC TESTSERV.MASTER.xp_fixedDrives I've already done the following set up: Allow Remote Clients Allow Outbound On Security tab of Local DTC Properties in Component Services: 1. Go To Run, type comexp.msc. 2. Double click "Console Root". 3. Double click "Component Services. 4. Double click "Computers". 5. Double click "My Computer". 6. Double click "Distributed Transaction Coordinator". 7. Right click "Local DTC" under "Distributed Transaction Coordinator", and click properties. 8. Click the "Security" tab. 9. Put tick marks on the checkboxes "Allow Remote Clients" and "Allow Outbound". Still not fixed.
Different clone is used here for remote machine.

I've a a linked server and I need to run the following statement: INSERT INTO...EXEC linkedserver.sp @parameter Both servers SQL Server 2008R2 SP1. Once I run it I get this error: > Msg 7391, Level 16, State 2, Line 6 The operation could not be > performed because OLE DB provider "SQLNCLI10" for linked server > "MY.LINKED.SERVER" was unable to begin a distributed > transaction. After searching for the error I saw that lot recommend running: EXEC master.dbo.sp_serveroption @server = N'[mylinkedserver]', @optname = N'remote proc transaction promotion', @optvalue = N'false' Is there any security implications on this action I should be aware of? Other option is to use advanced properties of the linked server object on main server:

I need to configure a MySQL cluster in Azure with region HA that supports XA transactions. MySQL does not support XA transactions when replication is in place for our current setup with Percona XTradb (managed by us), so we are exploring some options with Azure's managed services. From what I gather, with a MySQL flexible server, zonal HA can be configured, which essentially recoveres data at the storage level, so no replication, so XA should work, but we need region-level failover capabilities. Is there a way to configure a Azure MySQL service with region HA/failover that supports XA transactions?

For this question lets assume there are two servers, **ServerA** and **ServerB**, and there is a linked server setup on **ServerA** to **ServerB**. Let's also assume I have [REMOTE_PROC_TRANSACTIONS](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-remote-proc-transactions-transact-sql?view=sql-server-ver15) set to ON for both servers. According to Microsoft's BOL for [BEGIN TRANSACTION](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver15) : > The local transaction started by the BEGIN TRANSACTION statement is **escalated to a distributed transaction** if the following actions are performed before the statement is committed or rolled back: > - An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE > statement fails if the OLE DB provider used to access the linked > server does not support the ITransactionJoin interface. > > - A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON. Given the above, are there any cases where it makes sense or is mandatory to explicitly use BEGIN DISTRIBUTED TRANSACTION? Specifically, I'm planning to call a stored procedure (that executes DML queries) on **ServerB** via the linked server from **ServerA** with the same **transaction** wrapped around some DML statements executed on **ServerA** as well. My goal is to achieve transactional consistency for both sets of DML queries happening between the two servers.

as you can see on my screenshot, I have a DTC transaction in error on my server. I know, I can Right -> Resolve -> Forget/Abort However, I want to do it with a command line (batch or Powershell). Is there any way to do that? Thank you

A company is having a Microsoft SQL Server 2017 Enterprise cluster (RTM-GDR, 64-bit, 14.0.2027.2, KB4505224) consisting of one Availability Group (AG) with two servers, a primary and a secondary. A closed source application used by this company seems to work fine with the exception of one piece of functionality. When this functionality is used, the following error can be found in its log file:

Cannot use SAVE TRANSACTION within a distributed transaction

A possible workaround, according to an unrelated website, is to disable distributed transactions by:

ALTER AVAILABILITY GROUP MyaAG
   SET ( DTC_SUPPORT = NONE );

After testing, this seems to solve the issue. However, I am uncertain about the impact of this change. - What impact does this change have? - Is the cluster still active? - Is the data still replicated to the secondary server? - Does it have a positive or negative impact on the performance? - Has a safety feature just been disabled?

*In a relation to https://dba.stackexchange.com/q/286437/5203 , which is a similar but different question.* We have an SQL Server 2016 for which we are considering creating an Always On availability group for purposes of disaster recovery only. Currently the server is not a part of any cluster or availability group, and: - The server hosts multiple databases - Most of the operations are cross-database transactions - Many of the operations involve actions not supported in distributed transactions (e.g. save transaction) From the fact that currently save transactions etc execute successfully, one can conclude that the server is *not using distributed transactions*. Now, let us consider the following two quotes from Configure distributed transactions for an Always On availability group : - > A transaction with two or more databases in a single instance of the database engine is actually a distributed transaction. The instance manages the distributed transaction internally; to the user, it operates as a local transaction. SQL Server 2017 (14.x) promotes all cross-database transactions to DTC when databases are in an availability group configured with DTC_SUPPORT = PER_DB - even within a single instance of SQL Server. (That *seemingly* suggests that our cross-database transactions are technically distributed because they involve multiple databases. Which contradicts the observation that save transaction currently works, so let us call them "kind-of" distributed, not really "true" distributed.) - > SQL Server does not prevent distributed transactions for databases in an availability group - even when the availability group is not configured for distributed transactions. However when an availability group is not configured for distributed transactions, failover may not succeed in some situations. Specifically the new primary replica SQL Server instance may not be able to get the transaction outcome from DTC. To enable the SQL Server instance to get the outcome of in-doubt transactions from the DTC after failover, configure the availability group for distributed transactions. (This promises woes for distributed transactions executed when no DT protection is enabled in an AG. But does that include both the "kind-of" and the "true" distributed transactions, or only the "true" ones?) **Does the second quote address only "true" distributed transactions, or also the "kind of" distributed transactions that are "distributed" simply because they involve multiple databases, like the first quote explains?** That is, if the only "distributed" transactions that happen are the single-instance ones that just involve several databases from the same server instance, then is it or is it not the case that such "kind-of" distributed transactions are fully protected within an AG as if they were local transactions, even when the AG has DTC_SUPPORT = NONE?

In SQL Server 2019, can anyone definitively tell me the functional difference between xp_sqljdbc_xa_commit & xp_sqljdbc_xa_end? My guess is that commit is attempting to commit the transaction (thank you Mr. Obvious ;-) ) and end is telling DTC "hey, all done here. Please clean up." But, the code appears to live in SQLJDBC_XA.dll (as extended sp) so I can't view it, and I can't find a good reference to confirm my assumptions. Thanks!