3

As I found, you can set a lock on your desired table within a transaction with :

begin tran
    select 1 from myTable
        with (tablock /*or tablockx to block even reads from table*/, holdlock) 
    -- do your work
commit tran

But when I run this code on a linked server table, the table still accessible from within linked server! Why? How I can set a lock on this remote table like my example?

0

1 Answer 1

6

When trying to execute a transaction across multiple servers, you need to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION.

If my understanding is correct, BEGIN TRANSACTION creates a local transaction by default. Under certain circumstances (for instances, if you're doing an UPDATE on a table on the remote server) this will be escalated into a distributed transaction. If you have remote proc trans turned on for the remote server, then things like running a remote stored procedure should also automatically escalate to distributed transactions.

Otherwise, the remote operation will follow the normal transaction rules as a standalone operation on the remote server - in this case, the SELECT would be in its own one-statement "local" transaction on the remote server, and the lock would be released when the SELECT completed.

You may also need to use SET XACT_ABORT ON.

And, (as you noted in comments), you'll need to be sure the MS Distributed Transaction Coordinator service is on.

1
  • u said : "Otherwise, the activities on the other server won't be in a transaction."? are u sure?! i think the problem is just transaction cannot acquire exclusive lock on linked server!
    – ABS
    Jul 25, 2017 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.