在SQL Server上启用快照隔离减少死锁的错误

Version 8

    描述

     

     

    当多个到数据库的连接同时读写数据时会出现死锁,相互会阻止请求并且强制数据库杀掉一个请求作为死锁牺牲者。这个现象发生在汇总核心服务器对用户数据库进行复杂操作。 

     

     

    通过减少系统复杂性等措施可以减少一些死锁,另外一个选项就是改变SQL数据库的设置,SQL 2005更高的版本增加了快照隔离,推荐使用这个设置应用于rollup数据库的设置。基于快照隔离更详细的信息请参考微软官网:http://msdn.microsoft.com/en-us/library/ms189050.aspx.

     

    Tempdb数据库的使用

     

    一个快照隔离的应用是:它依赖于Tempdb数据库。因此值得注意的是磁盘输入/输出

     

    如何检查快照隔离是否启用

     

    为了检查快照隔离是否在sql数据库启用,请用你的核心的数据库名称替换DatabaseName

     

    SELECT snapshot_isolation_state_desc from sys.databases where name='DatabaseName'

     

    这将返回单独一列结果,结果将是OFF或者ON

     

    启用快照隔离

     

    • 如果你使用的SQL 2000,这将不能使用快照隔离,需要升级到最新版本的数据库。
    • 你可以根据如下步骤启用快照隔离:

      

     

      1. 确定没有任何用户连接到核心数据库(如果有用户连接到当前数据库第3步将会无限期进行),可以使用如下脚本强制停止当前用户对数据库的连接:

     

    SET ANSI_NULLS ON 

     

    GO 

     

    SET QUOTED_IDENTIFIER ON 

     

    GO 

     

    DECLARE @execSql NVARCHAR(1000) 

     

    DECLARE @databaseName VARCHAR(100) 

     

    DECLARE @NoKilled INT

     

    -- *** CHANGE THE NAME OF THE DATABASE *** --- 

     

    SET @databaseName = ' [DatabaseName] '  

     

    PRINT 'START: Killing active connections to the "' + @databaseName + '" database' 

     

    -- Count Connections 

     

    select @NoKilled = COUNT(*)  

     

    from master.dbo.sysprocesses  

     

    where db_name(dbid) = @databaseName 

     

    and

     

    DBID <> 

     

    and

     

    spid <> @@spid 

     

    -- Create the sql to kill the active database connections   

     

    set @execSql = ''  

     

    select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '

      

    from master.dbo.sysprocesses

      

    where db_name(dbid) = @databaseName

     

    and

     

    DBID <>

     

    and

     

    spid <> @@spid

      

    exec (@execSql)

     

    PRINT 'END: Killed "' + CAST(@NoKilled AS VARCHAR(4))

     

    + '" active connections to the "' + @databaseName + '" database'

     

    GO

     

      2. 完全备份数据库

     

     

       3. 运行如下SQL语句,用当前数据库名称替换DatabaseName:

       

    ALTER DATABASE [DatabaseName] SET ALLOW_SNAPSHOT_ISOLATION ON
    ALTER DATABASE [DatabaseName] SET READ_COMMITTED_SNAPSHOT ON

     

    如果上述语句执行超过2分钟,需要检查是否有用户连接到当前数据库,需要强制杀掉这些连接,否则这些语句会无限期执行。

      

    注意:我们推荐这些操作由非常有经验的数据库管理员进行操作。

     

    禁用快照隔离

     

    如果你需要关闭快照隔离,请参考如下步骤:

     

      ALTER DATABSE [DatabaseName] SET ALLOW_SNAPSHOT_ISOLATION OFF

     

    启用快照隔离时候一定要确保没有用户连接到当前数据库。

     

     

    英文原文https://community.landesk.com/support/docs/DOC-33503