Skip to main content

Questions tagged [distributed-transactions]

A distributed transaction is a unit of work in which two or more network hosts are involved. Usually hosts provide transactional resources while the transaction manager is responsible for creating and managing a global transaction that encompasses all the operations. Distributed transactions must have all four ACID (atomicity, consistency, isolation, durability) properties, where atomicity guarantees all-or-nothing outcomes for the unit of work.

Filter by
Sorted by
Tagged with
1 vote
2 answers
94 views

Transaction management work under Instance or database

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 ...
DooDoo's user avatar
  • 203
0 votes
1 answer
2k views

unable to begin a distributed transaction database linked server

I have a problem with my Linked Server. I have a Linked Server set up and when I select or insert it works great. The problem starts when I run a transaction to overwrite simple data in tables. begin ...
Jan Kůst's user avatar
0 votes
1 answer
156 views

How repeatable read isolation level and others are implemented in distributed/replicated databases?

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, ...
Dev's user avatar
  • 1
0 votes
1 answer
365 views

Try Confirm Cancel (TCC) Protocol

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 ...
Ryn's user avatar
  • 101
0 votes
1 answer
88 views

Does Azure MySQL 8 service with region HA support XA transactions?

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 ...
robert.baboi's user avatar
2 votes
1 answer
2k views

When to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION in SQL Server?

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 set to ON for both ...
J.D.'s user avatar
  • 38k
0 votes
0 answers
178 views

Is there a way to purge a DTC transaction from command line?

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)....
Adeel ASIF's user avatar
3 votes
0 answers
199 views

Are single-instance cross-DB transactions fully protected in Availability Groups as is, or do they require DTC_SUPPORT = PER_DB?

In a relation to MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group, which is a similar but different question. We have an SQL Server 2016 for which we are ...
GSerg's user avatar
  • 1,352
4 votes
2 answers
491 views

MS SQL 2017 cluster: impact of disabling distributed transactions for an availability group

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 ...
Arthur Borsboom's user avatar
0 votes
1 answer
355 views

SQL Server Distributed Transactions: xp_sqljdbc_xa_commit vs xp_sqljdbc_xa_end

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 (...
Joe Hayes's user avatar
  • 325
4 votes
1 answer
2k views

How can I log which queries are in a distributed transaction on an MS SQL server?

I am looking at migrating a database from a self-hosted cluster to Microsoft Azure SQL. I am aware that there are a few distributed transactions involved, which isn't supported on Azure SQL. Is there ...
ilivewithian's user avatar
0 votes
1 answer
50 views

Enforcing lowest transaction isolation level across concurrent sessions?

I am trying to learn transactions better in PostgreSQL and came across this observation. I have a situation where I am creating multiple tables using an SQL statement across multiple sessions. I am ...
Zeruno's user avatar
  • 497
1 vote
1 answer
119 views

Can I rely on the MSDTC to guarantee a distributed transaction was completed?

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 ...
DubDub's user avatar
  • 111
1 vote
0 answers
267 views

MariaDB Spider sharding: automatic XA commit?

I'm planning to use Spider to shard my huge table across multiple backend nodes. What I'd like to do, is inserting on the Spider node(s) on the virtual big table, and Spider have it distribute it for ...
gertvdijk's user avatar
  • 227
2 votes
0 answers
2k views

Database stuck in recovery pending but can't set to ONLINE or EMERGENCY

We have a database server who's data drive filled up see error log: Error: 17053, Severity: 16, State: 1. D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDb_log.ldf: Operating ...
SE1986's user avatar
  • 1,990
2 votes
1 answer
2k views

Auto-commit UPDATE Transaction On Linked Server

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-...
philomathic_life's user avatar
1 vote
0 answers
56 views

new transaction started on exception

We are running sql server 2016 enterprise edition. We have a linked server configured to db2. We do a distributed transaction between mssql 2016 and db2 using MS DTC. SQL Server controls the ...
Kelvin Wayne's user avatar
6 votes
1 answer
22k views

Insert into linked server fails with "The transaction manager has disabled its support for remote/network transactions"

Two servers: sq009 and sq202, both running SQL Server 2012 SP3. sq202 is the SQL Server hosting the DB for our ERP system, Sage 500. sq009 is the SQL Server hosting the DB for our IBM Maximo instance....
Brie's user avatar
  • 161
1 vote
1 answer
221 views

Microsoft Distributed Transactions from Informix with Linux

I need to create an extraction job from an Informix database, the requirement is that I need to enable transactions in SSIS. All of the MSDTC settings of the target machine and the SQL Server database ...
Rafael Lima's user avatar
0 votes
1 answer
13k views

Unable to begin a Distributed transaction

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 ...
Rashmita Shanti's user avatar
8 votes
1 answer
2k views

Where do Linked Server Queries get executed?

I have two instances ServerA and ServerB, and I have created a linked server in ServerA for ServerB as Linksrv_B. I can execute a query on ServerA using the four part naming convention: SELECT * ...
info.sqldba's user avatar
1 vote
0 answers
2k views

Oracle after run procedure returned ORA-02046

I made small changes in a procedure to calculate value for statistic monitoring. In procedure calculate_1 on remote DB, I added check null or no null - PARAMETER_ID OPEN CALCULATE_PARAMETER; ...
Nikolay Baranenko's user avatar
1 vote
0 answers
78 views

Is it possible to have a partition to distribute transaction load horizontally in SQL Server 2016?

In Cassandra, you can have a partition key to align partitions to particular server nodes. My understanding is that you can distribute transactional responsibility horizontally in SQL Server 2016 (...
hawkeye's user avatar
  • 167
3 votes
1 answer
2k views

Security risk of Enabling MS DTC Distributed transaction?

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. ...
Vishwanath Dalvi's user avatar
1 vote
1 answer
773 views

SQL Server Rollback nested Stored procedures and linked servers

I have read THIS StackOverflow article, and found it enlightening. However, I am having trouble assimilating that knowledge with my limited knowledge of transactions and linked servers. I am trying to ...
Jeff.Clark's user avatar
15 votes
4 answers
6k views

Blockchain (Bitcoin) as a database?

I was reading this BBC News article and the following excerpt, caught my attention. It sounds like Always On Availability Groups or High Availability Mirroring, maybe with security automatically ...
James Jenkins's user avatar
3 votes
2 answers
880 views

Do SQLCLR objects support Distributed Transactions on regular / external connections?

The most common usage of regular / external database connections in SQLCLR objects, it would seem, is to do SELECT-only queries (i.e. no changes: DML, DDL, DCL, D*%$#!L, etc). It is also fairly common ...
Solomon Rutzky's user avatar
4 votes
2 answers
2k views

Why are transactions running as distributed (DTCXact)?

I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask. I repetitively run the following query ...
Magier's user avatar
  • 4,797
15 votes
1 answer
9k views

Problem creating a transaction in an SSIS package

I'm working on a package that needs to use a transaction but I'm currently getting the following error: SSIS package "CATS-Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS....
Kenneth Fisher's user avatar
0 votes
1 answer
84 views

What's the best solution for distribution of one database over two servers?

I have a MS SQL Server 2014 database with large scale of data an lots of users; and the RAID level is equal to 10. Now due to the rapid growth of data amount, I'm going to distribute this one database ...
Hamed Heidari's user avatar
1 vote
0 answers
503 views

Can we Link sql windows server with oracle unix server?

while inserting data through linked server it is happening but when i execute the stored procedure in getting error like OLE DB provider "OraOLEDB.Oracle" for linked server "server ip" returned ...
Dhivya's user avatar
  • 11
0 votes
1 answer
2k views

Orphaned transaction (SPID -2) with UOW ID 00000000-0000-0000-0000-000000000000

SYSTEM: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor) PROBLEM: Today I encountered an "orphaned ...
Joe Hayes's user avatar
  • 325
2 votes
1 answer
2k views

Four-part distributed Query issue from 2014 to 2008R2

From a SQL Server 2014 RTM in our DEV environement, I am executing this query using a SQL login. select t.noshipid, y.nofolder from distantserver.distantdatabase.dbo....
Craig Efrein's user avatar
  • 9,640
4 votes
1 answer
8k views

transaction across multiple databases - what is the overhead?

I run the following update, which is one single transaction across 2 databases. Please note I have not committed the transaction. --================================================================ --...
Marcello Miorelli's user avatar
3 votes
2 answers
616 views

DTC and AlwaysOn

We're heading into a new infrastructure: HyperV, Violin storage & dualsite setup We're looking into AlwaysOn for dual site, but we are using DTC on many of our systems. Do you know if the ...
Morten Vinther's user avatar
1 vote
0 answers
2k views

Linked Server vs. Ad Hoc (OpenRowset / OpenDatasource) Distributed Queries

I have an application which needs to grab data from different clients' databases that reside at the client's location. So alongside their normal details such as company name, address etc, I also store ...
volume one's user avatar
0 votes
1 answer
3k views

Require distributed transactions for server-to-server communication

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 ...
Jared's user avatar
  • 55
0 votes
1 answer
3k views

Multiple Distributors in SQL Server Replication

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 ...
dthree's user avatar
  • 261
8 votes
2 answers
12k views

How to query readonly replica in Always On cluster

We have an Always On "cluster" that consists of 2 servers (there will be more), so one is PRIMARY and other(s) is(are) SECONDARY. The idea was to dedicate SECONDARY as a readonly replica, so it would ...
retif's user avatar
  • 427
1 vote
0 answers
165 views

What changes to Oracle installation are needed to work with Microsoft MSDTC

We have windows 2008 and 2003 servers that use COM PLUS-MSDTC and distributed transactions with 11g(11.2.0.3) running on SunOS and SPARC hardware. The oracle server was installed years ago by a DBA ...
Colin's user avatar
  • 11
1 vote
1 answer
9k views

routine to backup ssas databases fails with: The XML for Analysis request timed out before it was completed

I have developed my own stored procedure, that through a LINKED SERVER, backups the SSAS databases according to a table value parameter. the code for the stored procedure is below. the problem: it ...
Marcello Miorelli's user avatar
2 votes
2 answers
14k views

ORA-02049 and ORA-02063 Errors

Select For UPDATE NOWAIT is performed on a table by 20 processes in parallel The error below is returned by the 4 processes out of 20 (16 processes are successful) SQL_UPDATE_ERROR- : SQL update ...
Avnish Garg's user avatar
0 votes
1 answer
2k views

UOW's: Relating UOW's listed in MSDTC to SQL Server info

We have some active transactions showing up in MSDTC that have been there for a very long time and don't go away. They're not orphaned or in-doubt, just normal-looking active transactions. We're ...
Joe Hayes's user avatar
  • 325
0 votes
1 answer
1k views

Distributed transaction coordinator between SQL Server and Oracle

I'm not sure about distributed transaction coordinator so please bare with me on this, I'm probably asking this question wrong. I know distributed transaction coordinator is used in SQL Server and can ...
Coder92's user avatar
4 votes
2 answers
199 views

Simultaneous money withdrawls from a distributed/replicated database

Lets say I have $10 in my bank account. The database is replicated in North America and Asia. At the exact same time I do a withdrawl of $10 in N. America and in Asia, meaning that my withdraw would ...
DbQuestioner88's user avatar
8 votes
1 answer
14k views

Security implications disabling promotion of distributed transaction for linked server

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 ...
Yaroslav's user avatar
  • 2,828
2 votes
1 answer
2k views

Automatically rollback two-phase transactions on session disconnect

I'm using two-phase commit in Postgres to reliably work with an external system (Amazon EC2). I.e. I first do a PREPARE call, then call the external system. If the external system call fails for some ...
Cyberax's user avatar
  • 141
1 vote
1 answer
1k views

Oracle temporary table usage with XA

I'm confused about the compatibility of Oracle XA with global temporary tables. I've read several places on the internet and in the oracle documentation that say "Distributed transactions are not ...
takteek's user avatar
  • 113
8 votes
2 answers
86k views

How can I enable distributed transactions for a linked server?

I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver. I'm trying to enable distributed transactions. I ...
Mansfield's user avatar
  • 1,031
6 votes
1 answer
401 views

Is there a way to implement a cross-database task on SQL Server 2012 with the Availability Groups feature?

We use SQL Server 2012 and its new Availability Groups (AG) feature. There is a task for moving old data of some tables from one database to another database. Both databases are included into ...
metravl's user avatar
  • 61