首页 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
移动开发 架构设计 编程语言 Web前端 互联网
开发杂谈 系统运维 研发管理 数据库 云计算 Android开发资料
资讯 业界资讯 软件杂谈 编程开发 网站建设 网络观查 搜索引擎 移动应用 网站运营 网络地图
开发 移动开发 Web前端 架构设计 编程语言 互联网 数据库 系统运维 云计算 开发杂谈
[架构设计] sql server中高并发情况下 同时执行select和update语句死锁问题 (一)
sql server中高并发情况下 同时执行select和update语句死锁问题 (一)


       最近在项目上线使用过程中使用SqlServer的时候发现在高并发情况下,频繁更新和频繁查询引发死锁。通常我们知道如果两个事务同时对一个表进行插入或修改数据,会发生在请求对表的X锁时,已经被对方持有了。由于得不到锁,后面的Commit无法执行,这样双方开始死锁。但是select语句和update语句同时执行,怎么会发生死锁呢?看完下面的分析,你会明白的…




首先看到代码中使用的查询的方法Select


<span style="font-size:18px;">  /// <summary>
		/// 根据学生ID查询教师信息。用于前台学生评分主页面显示
		/// </summary>
		/// <param name="enTeacherCourseStudent">教师课程学生关系实体:StudentID</param>
		public DataTable QueryTeacherByStudent(TeacherCourseStudentLinkEntity enTeacherCourseStudent){
            //TODO:QueryTeacherByStudent string strSql = "SELECT ID, CollegeTeacherID,CollegeTeacherName,TeacherID,TeacherCode," +
            //"TeacherName,CourseID,CourseName,CourseTypeID,CourseTypeName," +
            //"StudentID,StudentName,IsEvluation FROM TA_TeacherCourseStudentLink  WITH(NOLOCK) " +
            //"WHERE StudentID = @StudentID";
			//根据学生ID查询该学生对哪些教师评分的sql语句
            string strSql = "SELECT ID, CollegeTeacherID,CollegeTeacherName,TeacherID,TeacherCode," +
                            "TeacherName,CourseID,CourseName,CourseTypeID,CourseTypeName," +
                            "StudentID,StudentName,IsEvluation FROM TA_TeacherCourseStudentLink  WITH(NOLOCK) " +
                            "WHERE StudentID = @StudentID";
            //参数
            SqlParameter[] para = new SqlParameter[] { 
                new SqlParameter("@StudentID",enTeacherCourseStudent.StudentID) //学生ID
            };
            //执行带参数的sql查询语句或存储过程
            DataTable dtStuTeacher = sqlHelper.ExecuteQuery(strSql,para,CommandType.Text);
            //返回查询结果
            return dtStuTeacher;
            
		}</span>





更新方法


<span style="font-size:18px;"> /// <summary>
		/// 学生对教师评分完毕,是否评估由N变为Y
		/// </summary>
		/// <param name="enTeacherCourseStudent">教师课程学生关系实体:StudentID、TeacherID、CourseID</param>
        /// <return>是否修改成功,true成功,false失败</return>
        public Boolean EditIsEvaluation(TeacherCourseStudentLinkEntity enTeacherCourseStudent, SqlConnection sqlCon, SqlTransaction sqlTran)
        {
            //更改是否评估字段为"Y"的sql语句
            string strSql = "UPDATE TA_TeacherCourseStudentLink WITH(UPDLOCK) SET IsEvluation='Y' WHERE TeacherID=@TeacherID AND StudentID=@StudentID AND CourseID=@CourseID";
            //参数
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@TeacherID",enTeacherCourseStudent.TeacherID),  //教师ID
                new SqlParameter("@StudentID",enTeacherCourseStudent.StudentID),  //学生ID
                new SqlParameter("@CourseID",enTeacherCourseStudent.CourseID)  //课程ID
            };
            //李社河添加2014年12月29日
            Boolean flagModify = false;
            try
            {
                //执行带参数的增删改sql语句或存储过程
                flagModify = sqlHelper.ExecNoSelect(strSql, paras, CommandType.Text, sqlCon, sqlTran);
            }
            catch (Exception e)
            {
                throw e;
            }
            

            //返回修改结果
            return flagModify;
		}</span>

       现在分析,在数据库系统中,死锁是指多个用户(进程)分别锁定了一个资源,并又试图请求锁定对方已经锁定的资源,这就产生了一个锁定请求环,导致多个用户(进程)都chǔ于等待对方释放所锁定资源的状态。还有一种比较典型的死锁情况是当在一个数据库中时,有若干个长时间运行的事务执行并行的操作,当查询分析器chǔ理一种非常复杂的查询例如连接查询时,那么由于不能控制chǔ理的顺序,有可能发生死锁现象。



那么,什么导致了死锁?
   现象图




      通过查询sqlserver的事务日志视图,发生的错误日志视图知道是在高并发的情况下引发的update和select发生的死锁
,接下来我们看例子;


 
<span style="font-size:18px;">CREATE PROC p1 @p1 int AS
      SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
   GO

   CREATE PROC p2 @p1 int AS
         UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
         UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
   GO</span>


    p1没有insert,没有delete,没有update,只是一个select,p2才是update。
    那么,什么导致了死锁?




<span style="font-size:18px;">需要从事件日志中,看sql的死锁信息:
   Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):
   SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
   Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):
   UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
               
   The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.
   The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.</span>

  
     首先,我们看看p1的执行计划。怎么看呢?可以执行set statistics profile on,这句就可以了。下面是p1的执行计划

<span style="font-size:18px;">SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
        |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))
               |--Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] >= [@p1] AND [t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)
                     |--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)</span>




       我们看到了一个nested loops,第一行,利用索引t1.c2来进行seek,seek出来的那个rowid,在第二行中,用来通过聚集索引来查找整行的数据。这是什么?就是bookmark lookup啊!为什么?因为我们需要的c2、c3不能完全的被索引t1.c1带出来,所以需要书签查找。
好,我们接着看p2的执行计划。



<span style="font-size:18px;">UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
         |--Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))
               |--Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))
                     |--Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN ...
                           |--Top(ROWCOUNT est 0)
                                 |--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)</span>




        通过聚集索引的seek找到了一行,然后开始更新。这里注意的是,update的时候,它会申请一个针对clustered index的X锁的。


      实际上到这里,我们就明白了为什么update会对select产生死锁。update的时候,会申请一个针对clustered index的X锁,这样就阻塞住了(注意,不是死锁!)select里面最后的那个clustered index seek。死锁的另一半在哪里呢?注意我们的select语句,c2存在于索引idx1中,c1是一个聚集索引cidx。问题就在这里!我们在p2中更新了c2这个值,所以sqlserver会自动更新包含c2列的非聚集索引:idx1。而idx1在哪里?就在我们刚才的select语句中。而对这个索引列的更改,意味着索引集合的某个行或者某些行,需要重新排列,而重新排列,需要一个X锁。
SO………,问题就这样被发现了。



总结一下
       就是说,某个query使用非聚集索引来select数据,那么它会在非聚集索引上持有一个S锁。当有一些select的列不在该索引上,它需要根据rowid找到对应的聚集索引的那行,然后找到其他数据。而此时,第二个的查询中,update正在聚集索引上忙乎:定位、加锁、修改等。但因为正在修改的某个列,是另外一个非聚集索引的某个列,所以此时,它需要同时更改那个非聚集索引的信息,这就需要在那个非聚集索引上,加第二个X锁。select开始等待update的X锁,update开始等待select的S锁,死锁,就这样发生鸟。
     添加了针对select和update同一个表的非聚集索引解决问题



那么,为什么我们增加了一个非聚集索引,死锁就消失鸟?我们看一下,按照上文中自动增加的索引之后的执行计划:


<span style="font-size:18px;">  SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
      |--Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7_2073058421__K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] >= [@p1] AND [deadlocktest].[dbo].[t1].[c2] <= [@p1]+(1)) ORDERED FORWARD) </span>


哦,对于clustered index的需求没有了,因为增加的覆盖索引已经足够把所有的信息都select出来。就这么简单。
实际上,在sqlserver 2005中,如果用profiler来抓eventid:1222,那么会出现一个死锁的图,很直观的说。

下面的方法,有助于将死锁减至最shao(详细情况,请看SQLServer联机帮助,搜索:将死锁减至最shao即可。
·         按同一顺序访问对象。
·         避免事务中的用户jiāo互。
·         保持事务简短并chǔ于一个批chǔ理中。
·         使用较低的隔离级别。
·         使用基于行版本控制的隔离级别。
              -    将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON,使得已提jiāo读事务使用行版本控制。
              -    使用快照隔离。
·         使用绑定连接。





 此文从网络中自动搜索生成,不代表本网站赞成被搜索网站的内容或立场    查看原文
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 美食菜谱 新闻资讯 电影视频 小游戏 Chinese Culture 股票 租车
生肖星座 三丰软件 视频 开发 短信 中国文化 网文精选 搜图网 美图 阅读网 多播 租车 短信 看图 日历 万年历 2018年7日历
2018-7-20 14:23:34
 
  网站联系 软件世界网-www.sjsjw.com ©2014 蜀ICP备06016416号 三峰网旗下网站