如何减轻查询跨数据库视图的存储过程中的锁定

I have a view that joins SQL Server tables on linked servers. There are certain restrictions on linked servers that mean I can't create an index on the view and there is a stored procedure that last night locked the database, even though it is only doing a select. The stored procedure is generated by .NetTiers (I've elided a dozen columns from both select statements):

我有一个视图，它连接链接服务器上的SQL Server表。链接服务器存在某些限制，这意味着我无法在视图上创建索引，并且存在昨晚锁定数据库的存储过程，即使它只执行选择。存储过程由.NetTiers生成（我从两个select语句中删除了十几列）：

CREATE PROCEDURE [AbnormalReadingSource_Get]
(
    @WhereClause varchar (2000)  ,
    @OrderBy varchar (2000)  ,
    @PageIndex int   ,
    @PageSize int   
)
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int

    -- Set the page bounds
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize

    IF (@OrderBy is null or LEN(@OrderBy) < 1)
    BEGIN
        -- default order by to first column
        SET @OrderBy = '[Result]'
    END

    -- SQL Server 2005 Paging
    declare @SQL as nvarchar(4000)
    SET @SQL = 'WITH PageIndex AS ('
    SET @SQL = @SQL + ' SELECT'
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
    END
    SET @SQL = @SQL + ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') as RowIndex'
    SET @SQL = @SQL + ', [Result]'
    SET @SQL = @SQL + ' FROM [AbnormalReadingSource]'
    IF LEN(@WhereClause) > 0
    BEGIN
        SET @SQL = @SQL + ' WHERE ' + @WhereClause
    END
    SET @SQL = @SQL + ' ) SELECT'
    SET @SQL = @SQL + ' [Result],'
    SET @SQL = @SQL + ' FROM PageIndex'
    SET @SQL = @SQL + ' WHERE RowIndex > ' + convert(nvarchar, @PageLowerBound)
    IF @PageSize > 0
    BEGIN
        SET @SQL = @SQL + ' AND RowIndex <= ' + convert(nvarchar, @PageUpperBound)
    END
    exec sp_executesql @SQL

    -- get row count
    SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
    SET @SQL = @SQL + ' FROM [AbnormalReadingSource]'
    IF LEN(@WhereClause) > 0
    BEGIN
        SET @SQL = @SQL + ' WHERE ' + @WhereClause
    END
    exec sp_executesql @SQL

END

The database transaction isolation level is READ COMMITTED so setting the isolation level in the stored procedure isn't necessary but is in the .NetTiers templates used to generate the stored procedure. Can I use the readpast hint to avoid the locking that occurred? Do I need to provide any other hints when using readpast?

数据库事务隔离级别为READ COMMITTED，因此不必在存储过程中设置隔离级别，而是在用于生成存储过程的.NetTiers模板中。我可以使用readpast提示来避免发生锁定吗？使用readpast时是否需要提供任何其他提示？

3 个解决方案

#1

You could try READPAST but remember it will not allow "dirty" reads so your result set could be missing some records if something is momentarily locked.

您可以尝试READPAST但请记住它不会允许“脏”读取，因此如果某些内容暂时被锁定，您的结果集可能会丢失一些记录。

Similarly you could try your select statement with: WITH(NOLOCK)

类似地，您可以尝试使用以下语句选择语句：WITH（NOLOCK）

NOLOCK will read the "dirty" data but then you run the risk of it being rolled back.

NOLOCK将读取“脏”数据，但之后您将面临回滚的风险。

Both will avoid blocking, both have their pros and cons.

两者都会避免阻塞，两者都有其优缺点。

This article should help clarify:
http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492

本文应该有助于澄清：http：//www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492

#2

Run SQL Profiler in conjunction with this query, and then feed it into the Database Engine Tuning Advisor. Do not change your isolation levels if you can either break this up, or improve your indices.

与此查询一起运行SQL事件探查器，然后将其提供给数据库引擎优化顾问。如果您可以解决此问题或改进索引，请不要更改隔离级别。

You may create views on linked servers but those linked servers must both be SQL Server and Cross Database Queries Not Allowed in an Indexed View.

您可以在链接的服务器上创建视图，但这些链接的服务器必须都是SQL Server和索引视图中不允许的跨数据库查询。

Perhaps this procedure should not be running via a view at all.

也许这个程序不应该通过视图运行。

http://msdn.microsoft.com/en-us/library/ms191432.aspx

Great locking articles

伟大的锁定文章

http://support.microsoft.com/kb/75722 http://support.microsoft.com/kb/323630

Do not use the word sp in your stored procedures, those are reserved for system stored procedures will cause minor overhead.

不要在存储过程中使用单词sp，那些为系统存储过程保留会导致较小的开销。

Cursors are bad, but better indexing is the primarily solution, which of course the Tuning Advisor will scrip for you automatically, which means you need to circumvent views altogether to leverage.

游标很糟糕，但更好的索引是主要的解决方案，当然Tuning Advisor会自动为您编写脚本，这意味着您需要完全绕过视图来利用。

Create fault alerts regarding this process so both you and your co-manager is aware if this task fails when you are out-of-office via SSIS SMTP tasks.

创建有关此过程的故障警报，以便当您通过SSIS SMTP任务离开办公室时，您和您的联合经理都知道此任务是否失败。

"To repeatedly get data from another server, create a linked server and then use the OPENQUERY function or use 4-part naming. If you are not adhered with T-SQL, then it is better to use import/export wizard, and you can save it as a SSIS package for future use." - SQL Server MVP Pinal Dave

“要从其他服务器重复获取数据，创建一个链接服务器，然后使用OPENQUERY函数或使用4部分命名。如果你不遵守T-SQL，那么最好使用导入/导出向导，你可以将其保存为SSIS包以备将来使用。“ - SQL Server MVP Pinal Dave

Regarding the locking and potential deadlocking

关于锁定和潜在的死锁

Typical methods you can use to resolve deadlocks include:

可用于解决死锁的典型方法包括：

Adding and dropping indexes.
添加和删除索引。
Adding index hints.
添加索引提示。
Modifying the application to access resources in a similar pattern.
修改应用程序以类似模式访问资源。
Removing activity from the transaction like triggers.
从触发器中删除事务中的活动。
Keeping transactions as short as possible.

保持交易尽可能短。

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

在SQL Server中，您还可以使用以下任一方法最小化锁定争用，同时保护事务免受未提交数据修改的脏读取：
- The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
- READ COMMITTED隔离级别，READ_COMMITTED_SNAPSHOT数据库选项设置为ON。
- The SNAPSHOT isolation level.
- SNAPSHOT隔离级别。

Load SQL Profiler with a focus on locking, initiate the scripts which deadlock specifically for that DB and feed it into the Database Engine Tuning Adviser, and review the suggestions.

加载SQL事件探查器，重点关注锁定，启动专门针对该数据库的死锁脚本并将其提供给数据库引擎优化顾问，并查看建议。

Footnotes: http://support.microsoft.com/kb/832524

脚注：http：//support.microsoft.com/kb/832524

[[1]: http://support.microsoft.com/kb/832524

[[1]：http：//support.microsoft.com/kb/832524

http://www.codeproject.com/Articles/42547/SQL-SERVER-How-To-Handle-Deadlock

Prior * locking question how to solve deadlock problem?

之前的*锁定问题如何解决死锁问题？

#3

why don't you just set the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED you will pretty much have the same result as using READPAST or with NOLOCK

你为什么不把TRANSACTION ISOLATION LEVEL设置为READ UNCOMMITTED，你几乎可以得到与使用READPAST或NOLOCK相同的结果

I know you said that the procedure is generated by .NetTiers but since you said you can add a query hint (readpast) I assume you can change the isolation level too.

我知道你说过程是由.NetTiers生成的，但既然你说你可以添加一个查询提示（readpast），我假设你也可以改变隔离级别。

#1