Microsoft – SQL Server – Transact SQL – Linked Server – Error – “Transaction context in use by another session (Msg 3910, Level 16, State 2, Line 1)”

Introduction

Playing around with Transact SQL today and found a limitation that was not obvious till today.

It concerns Linked Servers and explicit transactions.

Background

Background – Linked Server

So if you create a linked Server Connection; i.e.


USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'dbremote'
          , @srvproduct=N'DEVDB\MSSQL2012'
          , @provider=N'SQLOLEDB'
          , @datasrc=N'DEVDB\MSSQL2012'
GO
EXEC master.dbo.sp_serveroption
               @server=N'dbremote'
             , @optname=N'collation compatible'
             , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
              @server=N'dbremote'
            , @optname=N'data access'
            , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'rpc'
           , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'rpc out'
           , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
             @server=N'dbremote'
           , @optname=N'connect timeout'
           , @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption
              @server=N'dbremote'
            , @optname=N'collation name'
            , @optvalue=null
GO
EXEC master.dbo.sp_serveroption
               @server=N'dbremote'
             , @optname=N'lazy schema validation'
             , @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'query timeout'
              , @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'use remote collation'
              , @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption
                @server=N'dbremote'
              , @optname=N'remote proc transaction promotion'
              , @optvalue=N'true'
GO

Background – Linked Server – Queries

And, you issue queries against it:

Query – Select

   select *
   from   dbRemote.hr.dbo.employee

Query – Delete

   delete
   from   dbRemote.hr.dbo.employee
   where  employeeID = 10101

everything is good!

Background – Linked Server – Queries – Fail

… until you decide to follow best practices and loop your SQL into an explicit transaction. Additionally, you reference a local resource via 4 part names:

Query – Delete

    begin tran tranCancellation

      --reference a local resource (dbRemote) via 4 part name
      delete
      from   dbRemote.hr.dbo.employee
      where  employeeID = 10101

   commit tran tranCancellation

Result


Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Please keep in mind that you ‘re OK as long as SQL code that references local resources do not refer to them via 4 part names.

Query – Delete


    begin tran tranCancellation

      --notice that we are using 3-part and not 4-part names
      delete
      from   hr.dbo.employee
      where  employeeID = 10101

      --OK to use 4 part names for remote resource
      insert into dbConsolidated.hr.dbo.employee
      (employeeID, employeeName)
      values (10191, 'Sallie');

   commit tran tranCancellation

Reason

The reason for the failure is that “Distributed Transactions”, the underlying infrastructure that co-ordinates transaction management only works when objects referenced via 4 part names are actually remote.

Crediting

Crediting Microsoft’s Piort Rodak for his well written blog posting:

Piotr Rodak – DATA ACCESS setting on local server

http://sqlblogcasts.com/blogs/piotr_rodak/archive/2009/11/22/data-access-setting-on-local-server.aspx

The limitation of a loopback linked server is that it cannot take part in an explicit or implicit transaction because this would require loopback distributed transaction, something that MSDTC apparently does not support.

In such scenario, you will get error 3910.

Implications

I reached the same conclusion has Piort Rodak, but habitually I lean towards pushing ahead .. that is, until Goggle lands me where I can actually follow a nicely published and creditable source.

I normally would not face this problem, but did so this time while provisioning a LAB environment and I was trying to use the same machine for multiple SQL Server Instances.

And, also I was trying to use the same code base as the original SQL.  That is, use 4 part names.

 

References

References – Linked Server – Use-case scenario

References – Distributed Transactions on Local Server

References – Transact SQL – Conventions

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s