select查询造成的数据库死锁

最近给一个客户更新了一个模块，查询过程中老是出现查询结果不一致的情况，有时多有时少，通过调试发现sql语句都一样，返回的结果却不一样，跟踪SQL语句发现，在查询结果少的时候，会报事务被作为牺牲品的死锁错误，正常情况下，如果报错会返回null值，为什么会出现一部分数据，难道是脏读？（关于这个脏读的问题到现在都没明白怎么回事。

事情过了几天，情况也不是很严重，就一直没管他，后来一个老开发过来，在说到这一块问题的时候，他说可能是我排序的问题，我的数据排序是在程序中完成的，当时为了优化SQL性能，把sql的order by 语句都通过程序进行排序了，排序代码如下，

于是就把程序排序屏蔽掉，把排序加到SQL语句里面去执行，结果出来的结果要么是0 要么是全部结果，结果为0行时同样报死锁错误，因为有其它项目要忙，这个问题又搁置了几天。

 /// <summary>
        ///  对DataTable进行排序
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="SortCols"></param>
        /// <returns></returns>
        public DataTable SortTable(DataTable dt, string SortCols)
        {
            if (dt == null)
            {
                return null;
            }
            if (dt.Rows.Count == 0)
            {
                return dt;
            }
            DataTable dtSort = new DataTable();
            dtSort = dt.Clone();
            DataRow[] RowArr = dt.Select(" 1=1", SortCols);
            foreach (DataRow row in RowArr)
            {
                dtSort.Rows.Add(row.ItemArray);
            }
            return dtSort;
        }

周一的时候，由于客户的操作数据特别多，这种情况越来越严重，直接影响到了客户的正常工作，没办法，老板下命令一定要把这一块搞好，于是开始在百度上各种搜索，我是搞程序的，对数据库还算了解，因为没有DBA，这些数据库维护工作只能自己做，通过sql profiler提取记录，发现所有死锁基本上发生在这条语句和一个存储过程上，我存储过程是执行的n条sql语句，当时这样做而没有用事务去做的原因，是考虑到如果客户机突然断网，事务不会提交，就会造成阻塞或死锁，所以就把要执行的语句传入一个存储过程，让存储过程来执行，不知道这种做法对不对，我感觉应该会好点，存储过程如下，从昨天晚上一直在研究这个跟踪记录，发现有一个现象在存储过程执行到插入A表时，这个时候另外一个查询的SQL语句可以运行，但是死锁往往是发生在这个时候，查询语句是对A表的关联查询，A表中有两个索引一个聚集索引，一个非聚集索引。昨天查了一天无果，上午开始考虑如果做读写分离这种问题是不是不会再发生，上午测试了一下SQL的复制功能，虽然延迟在秒级别，但效果还不是太理想，把情况给老板大致说了一下，老板的意思还是找出来死锁的原因，下午又查了一下午，刚开始怀疑是不是A表数据太大了（5000多万行），在插入的时候消耗过大引起的，后来用DBCC ShowContig扫描了一下密度达到了99%多，于是开始怀疑是填充因子的问题，是不是太大了，导致插入的时候分页，后来想想我的聚集索引是加在时间上的，而时间列是用getdate()获取的默认值，不应该消耗太大，于是就先把A表清出去了一部分，准备明天再看结果。

临睡觉前准备再看下跟踪语句，发现所有的死锁都是发生在上述所产的A表插入上和那个select语句上，于是开始搜索select 造成的死锁，查到了下面引用的这篇文章，发现里面讲述的和我这个状况非常相似，A表上的两个索引造成的死锁

存储过程执行插入时要用到A表的聚集索引的X锁，插入完成之后会更新A表的非聚集索引

查询的语句中由于查询列不包含在索引中所以先使用了A表的非聚集索引又请求A表的聚集索引查询不包含的字段

这样就发生了互斥，造成了死锁，文中给出了两种解决方法

一个是索引覆盖（把查询的输出列include现在的索引里面）这种方法感觉不可行不可能把这么多列加到索引里面
二种方法是用SQL Server行隔离级别控制明天测试一下这个方法只要对其它业务流程不影响就用这种方法

就到这了洗洗睡了希望对你们有帮助

ALTER PROCEDURE [dbo].[ExcuteTrans]
(
@SqlArr varchar(8000)
)
 AS


declare @nSql nvarchar(4000)
declare  @error int
declare  @rtnerror int

set @nSql=@SqlArr
set @error=0
set @rtnerror=0
set @nSql=Replace(@nSql,'#',' set @error=@@error+@error ')
set @nSql=@nSql+' set @error=@@error+@error select @rtnerror=@error'

begin tran

exec sp_executesql @nSql ,N'@rtnerror int  out,@error int',@rtnerror out,@error

if(@rtnerror>0)
 begin 
 rollback tran
 return 
end

commit tran

参考文章：sql server中高并发情况下同时执行select和update语句死锁问题（一）

http://blog.csdn.net/lishehe/article/details/42279147

秒客网

select查询造成的数据库死锁

相关文章