与环回链接服务器进行事务处理-锁定问题 [英] Transaction with loopback linked server - locking issues

问题描述

我有两个数据库A和B.它们都存储在一个数据库实例上.我在实例上创建了一个环回链接服务器.

I have two databases A and B. They are both stored on one database instance. I created a loopback linked server on the instance.

数据库A包含一个表dbo.Users和一个存储过程更新dbo.Users表.在数据库B中，我有一个查询可完成两件事:

Database A contains one table dbo.Users and one stored procedure updating dbo.Users table. In database B I have a query which does two things:

1. 从数据库A执行存储过程，该数据库将更新dbo.Users表.
2. 通过链接服务器从dbo.Users选择数据.

1. Execute the stored procedure from database A which updates the dbo.Users table.
2. Select data from dbo.Users through the linked server.

BEGIN TRANSACTION
EXEC [LinkedServer].A.dbo.UpdateUser
select * from [LinkedServer].A.dbo.Users 
ROLLBACK TRANSACTION

当我尝试执行此存储过程时，只有在链接服务器上设置超时时，才会出现以下异常；在其他情况下，查询无法完成:

When I try to execute this stored procedure I get the following exception only when I set a timeout on the linked server; in other cases the query doesn’t finish:

Msg 3971, Level 16, State 1, Line 1
The server failed to resume the transaction. Desc:3900000002.

此问题的原因是[LinkedServer].A.dbo.UpdateUser存储过程的执行创建了不允许进行选择语句的事务.

The reason of this problem is that execution of the [LinkedServer].A.dbo.UpdateUser stored procedure creates a transaction which does not allow to make a select statement.

所以我决定添加WITH (NOLOCK)如下:

BEGIN TRANSACTION
EXEC [LinkedServer].A.dbo.UpdateUser
select * from [LinkedServer].A.dbo.Users WITH (NOLOCK)
ROLLBACK TRANSACTION

然后我得到这个异常:

链接服务器"LinkedServer"的OLE DB提供程序"SQLNCLI11" 返回消息未指定的错误".
的OLE DB访问接口"SQLNCLI11" 链接服务器"LinkedServer"返回消息查询超时" 已过期".
消息7311，级别16，状态2，第4行无法获取架构 OLE DB提供程序"SQLNCLI11"的行集"DBSCHEMA_TABLES_INFO" 链接服务器"LinkedServer".提供程序支持该接口，但是 使用时返回失败代码.

OLE DB provider "SQLNCLI11" for linked server " LinkedServer " returned message "Unspecified error".
OLE DB provider "SQLNCLI11" for linked server " LinkedServer " returned message "Query timeout expired".
Msg 7311, Level 16, State 2, Line 4 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI11" for linked server "LinkedServer". The provider supports the interface, but returns a failure code when it is used.

我在 Microsoft支持页面上找到了有关此异常的信息.当您尝试运行从64位SQL Server客户端到链接的32位SQL Server的分布式查询时，会出现此错误的信息.就我而言，这没有意义，因为我有一台环回链接服务器.

I found information about this exception on microsoft support page. There is information that this error occurs when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server. In my case it doesn’t make sense because I have a loopback linked server.

将数据库部署在单独的SQL Server实例上时，不会发生上述错误.有什么想法在使用回送链接服务器时如何省略锁或更改T-SQL以免获得异常?

The above errors don't occur when the databases are deployed on separate SQL server instances. Any ideas how to omit locks or change the T-SQL to not get exceptions when using a loopback linked server?

推荐答案

链接服务器"LinkedServer"的OLE DB提供程序"SQLNCLI11" 返回消息未指定的错误".

OLE DB provider "SQLNCLI11" for linked server " LinkedServer " returned message "Unspecified error".

链接服务器"LinkedServer"的OLE DB提供程序"SQLNCLI11" 返回消息查询超时已过期".

OLE DB provider "SQLNCLI11" for linked server " LinkedServer " returned message "Query timeout expired".

消息7311，级别16，状态2，第4行无法获取架构行集 用于链接的OLE DB提供程序"SQLNCLI11"的"DBSCHEMA_TABLES_INFO" 服务器"LinkedServer".提供程序支持该接口，但是 使用时返回失败代码.

Msg 7311, Level 16, State 2, Line 4 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI11" for linked server "LinkedServer". The provider supports the interface, but returns a failure code when it is used.

我也遇到了上述错误.

I have also faced the above mentioned error.

如果您正在做回送链接服务器，并且收到相同的错误，请检查是否在脚本末尾写了任何使用回送服务器的SP(创建过程).

If you are doing loop back linked server and receive the same error then check that any SP using loop back server should be written (Create Procedure)at the end of the script.

如果使用TFS中的代码，请在后期部署中使用回送链接服务器编写该SP的创建过程语句.

If using code from TFS, write create procedure statement of that SP, using loop back linked server in the post deployment.

只需遵循以下任一步骤:-

Simply follow any one of the below mentioned steps:-

1. 复制将SP粘贴到脚本末尾. OR
2. 不要使用回送服务器. OR
3. 如果您使用的是从TFS中发布脚本，请在后"部署脚本中写入SP(在将数据插入所有表之后).

1. Copy paste the SP in the end of script. OR
2. Don't use loop back server. OR
3. If you are using publish script from TFS write SP in the Post deployment script (after Data insert into all table).

这些解决方案对我有用.

这篇关于与环回链接服务器进行事务处理-锁定问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！