首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何减少查询跨数据库视图的存储过程的锁定

问如何减少查询跨数据库视图的存储过程的锁定
EN

Stack Overflow用户
提问于 2012-02-22 02:54:59
回答 3查看 2.3K关注 0票数 0

我有一个连接链接服务器上的Server表的视图。链接服务器上有某些限制,这意味着我无法在视图上创建索引,而且昨晚有一个存储过程锁定了数据库,尽管它只是做了一个选择。存储过程由.NetTiers生成(我从两个select语句中省略了十几个列):

代码语言:javascript
复制
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

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

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2012-02-22 03:01:49

您可以尝试使用READPAST,但请记住,它将不允许“脏”读取,这样,如果某些东西被瞬间锁定,结果集可能会丢失一些记录。

类似地,您可以使用:WITH(NOLOCK)尝试select语句。

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

两者都会避免阻塞,两者都有各自的优缺点。

这一条应有助于澄清:

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

票数 3
EN

Stack Overflow用户

发布于 2012-06-27 13:46:02

与此查询一起运行SQL ,然后将其输入数据库引擎优化顾问。如果可以拆分或改进索引,请不要更改隔离级别。

您可以在链接服务器上创建视图,但这些链接服务器必须同时是Server、和交叉数据库查询,这在索引视图中是不允许的。

,也许这个过程根本不应该通过一个视图运行.

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

大锁件

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

不要在存储过程中使用“sp”一词,这些都是为系统存储过程保留的,会造成较小的开销。

游标是不好的,但是更好的索引是主要的解决方案,当然优化顾问会自动为您编写文档,这意味着您需要完全绕过视图来利用。

创建有关此过程的错误警报,以便您和您的联合经理都知道,当您通过SSIS任务外出时,此任务是否会失败。

“若要从另一台服务器重复获取数据,请创建一个链接服务器,然后使用OPENQUERY函数或使用4部分命名。如果您没有遵守T,那么最好使用导入/导出向导,您可以将其保存为SSIS包供以后使用。”- SQL Server MVP Pinal

关于锁定和潜在死锁的

可以用来解决死锁的典型方法包括:

  • 添加和删除索引。
  • 添加索引提示。
  • 修改应用程序以以类似的模式访问资源。
  • 像触发器一样从事务中删除活动。
  • 尽可能缩短交易时间。 在Server中,还可以尽量减少锁定争用,同时使用以下两种方法保护事务不受未提交数据修改的脏读取:
代码语言:javascript
复制
- The READ COMMITTED isolation level **with the READ\_COMMITTED\_SNAPSHOT database option set to ON.**
- The SNAPSHOT isolation level.

​

以锁定为重点加载SQL,启动专门用于该DB的死锁脚本,并将其输入数据库引擎优化顾问,并检查建议。

脚注: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

先前的堆栈溢出锁定问题如何解决死锁问题?

票数 2
EN

Stack Overflow用户

发布于 2012-02-22 17:23:02

为什么不将事务隔离级别设置为READ UNCOMMITTED呢?您将得到与使用READPAST或NOLOCK几乎相同的结果

我知道您已经说过这个过程是由.NetTiers生成的,但是既然您说您可以添加一个查询提示(readpast),我想您也可以更改隔离级别。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/9388473

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档