SQL SERVER 使用JDBC 严重锁问题

 

    sql server 加锁范围与执行计划相关，因此错误的执行计划将导致加锁范围被扩大，进而导致严重的锁等待问题，甚至出现大量的死锁

          SQL SERVER JDBC 默认参数将导致所有prepareStatement 语句出现这一情况

 

测试结果如下：

语句：

 

Update gl_detail  set CREDITAMOUNT = 1,DEBITAMOUNT=1 where pk_detail = ?

 

默认情况下执行计划如下： 

StmtText                                                                                                  

--------

Clustered Index Update(OBJECT:([dbo].[gl_detail].[pk_gl_detail]), SET:([dbo].[gl_detail].[creditamount] = [Expr1003],[dbo].[gl_detail].[debitamount] = [Expr1004]))                                                                                                                                    

  |--Compute Scalar(DEFINE:([Expr1003]=(1.0000), [Expr1004]=(1.0000)))

  |--Top(ROWCOUNT est 0) 

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1018], [Expr1019], [Expr1017]))

  |--Compute Scalar(DEFINE:(([Expr1018],[Expr1019],[Expr1017])=GetRangeThroughConvert([@P0],[@P0],(62))))   |    |--Constant Scan

  |--Clustered Index Seek(OBJECT:([dbo].[gl_detail].[pk_gl_detail]),

      SEEK:([dbo].[gl_detail].[pk_detail] > [Expr1018] AND [dbo].[gl_detail].[pk_detail] < [Expr1019]), 

     WHERE:(CONVERT_IMPLICIT(nchar(20),[dbo].[gl_detail].[pk_detail],0)=[@P0]) ORDERED FORWARD)

 

实际上是要扫描一个范围

 

 

 

DB connection URL 里加上"sendStringParametersAsUnicode=false;"执行计划如下：

 

StmtText                                                                                     

 

Clustered Index Update(OBJECT:([dbo].[gl_detail].[pk_gl_detail]),

SET:([dbo].[gl_detail].[creditamount] = [Expr1003],

[dbo].[gl_detail].[debitamount] = [Expr1004]), DEFINE:([Expr1003]=(1.0000), [Expr1004]=(1.0000)),

WHERE:([dbo].[gl_detail].[pk_detail]=[@P0]))

 

直接按主键更新

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27378/viewspace-665135/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27378/viewspace-665135/