SQL Server发生死锁

[xieice]

从编号表中获取编号的java代码，用户并发数多的时候，SQLServer数据库端出现死锁，调查了很久查不出原因。

编号表的表定义如下：
编号ID(Varchar(10)，主键)，编号（Number，用于存储各个编号ID的最大值）。

获取编号的Class的逻辑如下：
①使用编号ID从编号表中获取编号，同时使用WITH (UPDLOCK)来锁住该数据
②如果①当中取不到数据，则插入新的数据，编号设为1
③如果①当中取得数据，则更新该数据的编号为编号+1
④更新后的编号值作为Class的返回值

一般情况下都能正常获取编号，但用户多的时候，经常在③的地方发生死锁。Log如下：
[2018-09-28 09:07:01][ERROR] CMN_TRACE - com.microsoft.sqlserver.jdbc.SQLServerException: 事务(进程 ID 137)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

按理说，①已经锁住该数据了，commit之前其他进程是无法再去锁住并且更新数据的，但死锁还是发生了。
哪位大神能够帮忙看一下。
参考信息：数据库的隔离级别是read committed snapshot,该表有一个聚集索引，就是主键项目编号ID。

非常感谢！

[我是小数位]

      
在对表的修改或删除前进行上锁判断
  /// <summary>
        /// 锁表(行级锁)
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="Id">要上锁的记录Id</param>
        /// <returns></returns>
        public bool LockTableRow(string TableName, string Id)
        {
            try
            {
                string SQL;
                SqlCommand myCommand = this._SqlConnection.CreateCommand();
                myCommand.CommandTimeout = 5;
                SQL = "select * from " + TableName + " with (holdlock updlock rowlock) where Id='" + Id + "'";
                myCommand.CommandText = SQL;
                if (_Transaction != null) myCommand.Transaction = _Transaction;
                try
                {
                    myCommand.ExecuteNonQuery();
                    //锁表成功
                    return true;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    //锁表失败
                    if (e.Message.ToUpper().IndexOf("TIME") >= 0)
                        return false;
                    else
                        throw e;
                }
                catch (System.Exception e)
                {
                    throw e;
                }
            }
            catch (System.Exception ex)
            {
                string errMsg = "锁表(行级锁)" + ex.Message + " TableName:" + TableName + " Id:" + Id;
                throw this.GetError(errMsg);
            }
        }

  /// <summary>
        /// 判断某个表是否可以上锁
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <returns></returns>
        private bool IsLock(string TableName)
        {
            try
            {
                SqlCommand myCommand = this._SqlConnection.CreateCommand();
                myCommand.CommandTimeout = 10;
                string SQL = "select top 1 *  from " + TableName + " with (updlock)";
                if (_Transaction != null) myCommand.Transaction = _Transaction;
                myCommand.CommandText = SQL;
                myCommand.ExecuteNonQuery();
                return false;
            }
            catch (System.Exception g)
            {
                if (g.Message.ToUpper().IndexOf("TIME") >= 0)
                    return true;
                else
                    throw g;
            }
        }

其他查询的地方用nolock文式查询即可

[我是小数位]

https://blog.csdn.net/CXJ0062008/article/details/77364545 上锁原因分析,请参数我的这个

[我是小数位]

开启SQL profiler，只要死锁了，他们很清楚的显示你发生死锁的两个进程各在做什么操作，这样估计你就会一看就懂了

[xieice]

代码如下。通过SQL profiler可以看到，每次都是在执行INSERT_COMMON_UPDATE语句更新SEQUENCE_VALUE的时候发生死锁了。而且看输出的log，竟然是先执行SELECT_COMMON_SEQUENCE_FOR_UPDATE查询并且锁数据的客户端发生死锁了。而发生资源竞争的另一个客户端，不管是锁同一条数据，还是锁不同数据，先锁数据的一方都会发生死锁。百思不得其解。。。 public class CommonSequenceUtility { // 共通序列编号表查询 private static String SELECT_COMMON_SEQUENCE_FOR_UPDATE = "SELECT COMPANY_CD, LOCALE_ID, SEQUENCE_ID, " + "SEQUENCE_NAME, SEQUENCE_VALUE FROM MD_COMMON_SEQUENCE " + "WITH (UPDLOCK) WHERE COMPANY_CD = ? AND LOCALE_ID = ? AND SEQUENCE_ID = ? AND SEQUENCE_NAME = ?"; // 共通序列编号表插入 private static String INSERT_COMMON_SEQUENCE = "INSERT INTO MD_COMMON_SEQUENCE" + "(COMPANY_CD,LOCALE_ID,SEQUENCE_ID,SEQUENCE_NAME,SEQUENCE_VALUE," + "CREATE_USER_CD,CREATE_DATE,RECORD_USER_CD,RECORD_DATE)VALUES (?,?,?,?,?,?,?,?,?)"; // 共通序列编号表更新 private static String INSERT_COMMON_UPDATE = "UPDATE MD_COMMON_SEQUENCE " + "SET SEQUENCE_VALUE = ?,RECORD_USER_CD = ?,RECORD_DATE = ? " + "WHERE COMPANY_CD =? AND LOCALE_ID = ? AND SEQUENCE_ID = ? AND SEQUENCE_NAME = ?"; public static String getSequenceValue(CommonSequenceInputDto inputDto) throws Exception { // 连接文字 String tmpJoinStr = inputDto.joinStr; if (CheckUtility.isNullOrEmpty(tmpJoinStr) || !inputDto.hassequenceName) { tmpJoinStr = ""; } // 变数初期化 String sequenceValue = "0"; sequenceValue = StringUtility.zeroesPadding(sequenceValue, inputDto.sequenceLength); String userCd = CommonUtility.getLoginUserCd(); Date date = DateUtility.getSqlDate(); // 获取连接 Connection con = CommonUtility.getSequenceCon(); try { // 共通序列编号表lock PreparedStatement pstmt = con.prepareStatement(SELECT_COMMON_SEQUENCE_FOR_UPDATE); pstmt.setString(1, inputDto.companyCd); pstmt.setString(2, inputDto.localeId); pstmt.setString(3, inputDto.sequenceId); pstmt.setString(4, inputDto.sequenceName); java.util.Date now = new java.util.Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssSSS"); ResultSet rs = pstmt.executeQuery(); now = new java.util.Date(); // 检索结果不存在的场合 if (!rs.next()) { try { // 共通序列编号表插入 pstmt = con.prepareStatement(INSERT_COMMON_SEQUENCE); pstmt.setString(1, inputDto.companyCd); pstmt.setString(2, inputDto.localeId); pstmt.setString(3, inputDto.sequenceId); pstmt.setString(4, inputDto.sequenceName); pstmt.setString(5, sequenceValue); pstmt.setString(6, userCd); pstmt.setDate(7, date); pstmt.setString(8, userCd); pstmt.setDate(9, date); pstmt.executeUpdate(); } catch (SQLServerException e) { // 违反了 PRIMARY KEY 约束以外的场合 if (!"23000".equals(e.getSQLState())) { // 关闭连接 con.rollback(); con.close(); throw e; } } finally { // 提交事务，关闭连接 if (!CheckUtility.isNull(con) && !con.isClosed()) { con.commit(); } } // 共通序列编号表lock pstmt = con.prepareStatement(SELECT_COMMON_SEQUENCE_FOR_UPDATE); pstmt.setString(1, inputDto.companyCd); pstmt.setString(2, inputDto.localeId); pstmt.setString(3, inputDto.sequenceId); pstmt.setString(4, inputDto.sequenceName); rs = pstmt.executeQuery(); rs.next(); } // 编号 + 1 Long sequence = Long.valueOf(rs.getString(5)) + 1; // 编号最大值判斷 if (String.valueOf(sequence).length() > inputDto.sequenceLength) { // 编码规则{0}中序列编号超過最大值！ String message = MessageUtility.getMessage( MessageID.ERR_MSG_0027, new String[] { inputDto.sequenceId }); // 关闭连接 con.commit(); con.close(); // 例外处理 throw new Exception(message); } sequenceValue = StringUtility.zeroesPadding( String.valueOf(sequence), inputDto.sequenceLength); // 共通序列编号表更新 pstmt = con.prepareStatement(INSERT_COMMON_UPDATE); pstmt.setString(1, sequenceValue); pstmt.setString(2, userCd); pstmt.setDate(3, date); pstmt.setString(4, inputDto.companyCd); pstmt.setString(5, inputDto.localeId); pstmt.setString(6, inputDto.sequenceId); pstmt.setString(7, inputDto.sequenceName); pstmt.executeUpdate(); // 提交事务，关闭连接 if (!CheckUtility.isNull(con) && !con.isClosed()) { con.commit(); con.close(); } // 编号返回值 StringBuilder rstValue = new StringBuilder(); if (inputDto.hassequenceName) { rstValue.append(inputDto.sequenceName); } if (!CheckUtility.isNullOrEmpty(tmpJoinStr)) { rstValue.append(tmpJoinStr); } rstValue.append(sequenceValue); inputDto.requestCount = 0; // 编号返回 return rstValue.toString(); } catch (SQLServerException e) { con.rollback(); inputDto.requestCount++; } finally { // 提交事务，关闭连接 if (!CheckUtility.isNull(con) && !con.isClosed()) { con.close(); con = null; } } // 判定异常结束次数，如范围内则重复采番 if(inputDto.requestCount < 10){ Thread.sleep(100); }else{ return null; } return getSequenceValue(inputDto); } }

[xieice]

先使用【WITH (UPDLOCK) 】进行锁数据的客户端，在Update的时候死锁了。锁应该是被别的客户端抢走了，这是什么情况，违背了常识。哪位大神救救我啊。

[天涯特困生]

1. 启动TF-1222 和 1204 在日志中搜集死锁更多信息 2. 最好将所有逻辑放到存储过程中 3. 试试添加PAGLOCK 到 SELECT_COMMON_SEQUENCE_FOR_UPDATE

[xieice]

SQLProfiler一直打开着，能够看到是在执行【INSERT_COMMON_UPDATE 】的时候发生的死锁。另外，编号表只有一个索引，就是主键的聚合索引，不存在非聚合索引。