CODEWITHSUNDEEP
sqlsql-servertransactions
peter.petrov
peter.petrov

Reputation: 39477

SQL Server - Non-distributed transaction over two DBs


I am trying to localize an issue I have
with 2 DBs and their transactional behavior.

I create two DBs on a single SQL Server 2008 R2 instance.

test1
test2

test1 has one table Table_1
test2 has one table Table_2

Both tables have an ID (int) and a value (string) columns.
Both have just one row which has ID=1.

Then in SQL Server Management Studio,
in some window 1 I do

use test1

begin transaction

update test1.dbo.Table_1
set
value = 'TEST-100'
where
ID = 1 


update test2.dbo.Table_2
set
value = 'TEST-200'
where
ID = 1 

commit transaction  --- but I don’t run the commit yet ---

and then in another window 2, I do

select * From test1.dbo.Table_1
with (nolock) 

select * From test2.dbo.Table_2
with (nolock)

This way I can see the two uncommitted yet values.

But if in window 2, I do

select * From test1.dbo.Table_1

or

select * From test2.dbo.Table_2

these SELECT staments hang.

So my question is: is that transaction from
window 1, spanning two DBs? Seems so because 

select * From test2.dbo.Table_2

hangs too which means to me that test2.dbo.Table_2
is enlisted in the same transaction which I started
in window 1.

But why is this transaction acting like a distributed one?
Is that normal? What is the explanation? Is that some
undocummented feature in SQL Server? Are there any
references which would explain that behavior I am seeing?

Upvotes: 0

Views: 444

Answers (1)

bjnr
bjnr

Reputation: 3437

When a query spans over 2 databases, then your transaction is promoted to a distributed transaction handled by MSDTC. It's the normal way to be and it happens without explicitly using BEGIN DISTRIBUTED TRANSACTION statement.

Upvotes: 1

Related Questions