SQL Server – How to Set an Exclusive Lock on a Remote Table

linked-serversql servert-sql

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?

Best Answer

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.

Related Question