begin transaction

Question

Post reply

begin transaction

Jez-448386

SSCarpal Tunnel

Points: 4570
More actions
December 18, 2007 at 2:46 am

#181385

If I run this script (in QA):

begin tran

select *

from database.dbo.tablename

select *

from remoteserverdatabase.dbo.tablename

rollback tran

the session hangs and I have to kill the process (using kill nn) - this then leaves a ghost process (as seen using sp_who2) and the only way to clean this up is to recycle the SQL server.

Is this normal for SS to hang like this?

The code is only an example - I might update a table on the local (dev) server and then select from the live server to make sure I have got the update right but it then goes pear shaped.

Jez

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Simon Liddle SSCrazy Points: 2580 More actions · Answer 1

Simon Liddle

SSCrazy

Points: 2580

December 18, 2007 at 7:21 am

#761009

Hi Jez,

Could you provide some details about the OS of both servers and the SQL version of both?

Cheers,

Simon

Jez-448386 SSCarpal Tunnel Points: 4570 More actions · Answer 2

Jez-448386

SSCarpal Tunnel

Points: 4570

December 18, 2007 at 7:31 am

#761019

They are both NT 5 and SQL 2000 (SP4).

Jez

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 3

Hello Jez,

It is very clear that you are opening the transaction on one machine and then passing on the pointer to do some operations on another server where there is no transaction opened but you are rolling back. BOL says

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.

Hope this helps you.

Lucky

Jez-448386 SSCarpal Tunnel Points: 4570 More actions · Answer 4

Lucky,

In my scenario, all I am doing on the remote server is a select - there is no insert or update or delete statement on the remote server so there would be no escalation to a distributed transaction.

Also, my statement does not fail - it just hangs and I cannot kill it (so that it does not appear in sp_who2) without stopping and starting the SQL server. That sounds different to a statement failing.

Jez