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.
57
questions
0
votes
1
answer
24
views
Seamless AOAG failover with DTC?
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 ...
- 21
1
vote
2
answers
97
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
...
- 203
0
votes
1
answer
3k
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 ...
- 35
0
votes
1
answer
178
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, ...
- 1
0
votes
1
answer
387
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 ...
- 101
0
votes
1
answer
94
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 ...
- 101
2
votes
1
answer
3k
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 ...
- 38.6k
0
votes
0
answers
190
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)....
- 131
3
votes
0
answers
208
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 ...
- 1,353
4
votes
2
answers
521
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 ...
- 143
0
votes
1
answer
381
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 (...
- 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 ...
- 143
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 ...
- 507
1
vote
1
answer
120
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 ...
- 111
1
vote
0
answers
273
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 ...
- 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 ...
- 2,033
2
votes
2
answers
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-...
- 451
1
vote
0
answers
57
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 ...
- 165
6
votes
1
answer
23k
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....
- 161
1
vote
1
answer
223
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 ...
- 523
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 ...
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 * ...
- 327
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;
...
1
vote
0
answers
83
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 (...
- 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.
...
- 229
1
vote
1
answer
794
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 ...
- 607
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 ...
- 6,297
3
votes
2
answers
883
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 ...
- 69.4k
4
votes
2
answers
3k
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 ...
- 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....
- 24.2k
0
votes
1
answer
89
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 ...
1
vote
0
answers
504
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 ...
- 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 ...
- 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....
- 9,670
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.
--================================================================
--...
- 16.4k
3
votes
2
answers
621
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 ...
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 ...
- 225
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 ...
- 55
0
votes
1
answer
4k
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 ...
- 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 ...
- 427
1
vote
0
answers
166
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 ...
- 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 ...
- 16.4k
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 ...
- 31
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 ...
- 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 ...
- 3
4
votes
2
answers
208
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 ...
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 ...
- 2,838
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 ...
- 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 ...
- 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 ...
- 1,031