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.
Questions tagged [distributed-transactions]
59 questions
15
votes
4 answers
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 included.
Is blockchain a potentially viable database…
James Jenkins
- 6,318
- 6
- 49
- 88
15
votes
1 answer
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.Pipeline: Validation phase is beginning.
Information:…
Kenneth Fisher
- 24,307
- 13
- 63
- 116
8
votes
2 answers
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 be a somewhat search-server.
I do know how to set…
retif
- 427
- 1
- 4
- 9
8
votes
1 answer
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 16, State 2, Line 6 The operation could not be
…
Yaroslav
- 2,837
- 4
- 29
- 41
8
votes
2 answers
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 believe this provider does support them as per IBM…
Mansfield
- 1,061
- 8
- 20
- 33
8
votes
1 answer
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 * FROM Linksrv_B.master.sys.databases
or…
info.sqldba
- 327
- 4
- 15
7
votes
1 answer
How to insert in table from remote stored procedure without creating a distributed transaction?
How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an insert into a local table.
Guillermo Gutiérrez
- 667
- 2
- 7
- 13
6
votes
1 answer
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 different availability groups.
Previously (before using…
metravl
- 61
- 1
6
votes
1 answer
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.
The situation: a trigger exists on a table in…
Brie
- 161
- 1
- 1
- 3
5
votes
1 answer
Distributed transaction and Read Committed Snapshot
Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?
Rolf
- 153
- 2
4
votes
2 answers
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 do work on two different copies of the data. How…
DbQuestioner88
- 41
- 1
4
votes
2 answers
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 closed source application used by this company…
Arthur Borsboom
- 143
- 3
4
votes
1 answer
Two-phase commit for distributed transactions: what if a commit failed?
I have 2 servers participating in 2PC scenario:
* Srv1: votes OK
* Srv2: votes OK
* Coordinator runs "commit"
* Srv1: commits
* Srv2: does not accept "commit" command because of network failure
From this moment my distributed DB is not consistence…
user996142
- 151
- 4
4
votes
1 answer
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 a way that I can log all distributed transactions…
ilivewithian
- 143
- 4
4
votes
2 answers
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 against production servers:
SELECT DTAT.transaction_id…
Magier
- 4,827
- 8
- 48
- 91