>我开发了一个监控应用程序。因此，我使用了一个计时器函数来检查SQL表中的某些值。
尽管有这么多函数，但它为一个名为getLogEntry（）的函数给出了以下错误
message>Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.</message>
<innerMessage>
</innerMessage>
<source>.Net SqlClient Data Provider</source>
<stackTrace>at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at ShiftAlertSystem.DBAccess.getLogEntry(Int32 nEventLogIdn, connections cn)</stackTrace>
<createdAt>2012/06/18 13:10:47</createdAt>
这是函数的实现
public LogEntry getLogEntry(int nEventLogIdn, connections cn)
{
lock (_objLock)
{
LogEntry lgEntObj = new LogEntry();
SqlConnection NewCon3 = new SqlConnection();
SqlCommand newCmd2 = null;
SqlDataReader dr = null;
try
{
string connectString;
// Configuration config = ConfigurationManager.u
string DataSource = cryptIT.Decrypt(cn.DataSource_bio);
string initialCatalog = cryptIT.Decrypt(cn.InitialCatalog_bio);
string user = cryptIT.Decrypt(cn.user_bio);
string password = cryptIT.Decrypt(cn.password_bio);
bool intergratedSecurity = cn.IntegratedSecurity_bio;
if (intergratedSecurity)
{
connectString = "Data Source=" + DataSource + ";Initial Catalog=" + initialCatalog + ";Integrated Security=True";
}
else
{
connectString = "Data Source=" + DataSource + ";Initial Catalog=" + initialCatalog + ";User ID=" + user + ";Password=" + password;
}
NewCon3 = new SqlConnection(connectString);
NewCon3.Open();
newCmd2 = NewCon3.CreateCommand();
newCmd2.Connection = NewCon3;
newCmd2.CommandType = CommandType.Text;
newCmd2.CommandText = @"
SELECT [nUserID]
,[sUserName]
,dateadd(s,[nDateTime],'1970/1/1') AS LogDateTime
,[nEventIdn]
,[nTNAEvent]
,[TB_READER].[nReaderIdn]
,[sName]
FROM
[TB_EVENT_LOG]
,[TB_USER]
,[TB_READER]
WHERE
[nEventLogIdn] = " + nEventLogIdn +
@" AND
[TB_EVENT_LOG].[nUserID] = [TB_USER].[sUserID]
AND
[nFlag]= 1
AND
[TB_EVENT_LOG].[nReaderIdn]=[TB_READER].[nReaderIdn]"
;
dr = newCmd2.ExecuteReader();
if (dr != null && dr.Read())
{
lgEntObj.nUserID = dr.GetInt32(0);
lgEntObj.nUserName = dr.GetString(1);
lgEntObj.LogDateTime = dr.GetDateTime(2);
lgEntObj.nEventIdn = dr.GetInt32(3);
lgEntObj.nTNAEvent = dr.GetInt16(4);
lgEntObj.nReaderIdn = dr.GetInt32(5);
lgEntObj.sName = dr.GetString(6);
}
dr.Close();
newCmd2.Dispose();
// NewCon.Close();
NewCon3.Close();
return lgEntObj;
}
catch (Exception exc)
{
CenUtility.ErrorLog.CreateLog(exc);
return null;
}
finally
{
if (dr != null)
dr.Close();
if(newCmd2 != null)
newCmd2.Dispose();
NewCon3.Close();
}
}
}
提前致谢
您可能希望参考此问题以获取一些更有用的建议。
我使用以下模式进行数据库重试;在本例中，我们返回一个 DataTable，但无论如何，该模式都是相同的;您根据 SqlException 编号检测 SqlDeadlock 或超时，然后重试，最多 n 次。
public DataTable DoSomeSql(int retryCount = 1)
{
try
{
//Run Stored Proc/Adhoc SQL here
}
catch (SqlException sqlEx)
{
if (retryCount == MAX_RETRY_COUNT) //5, 7, Whatever
{
log.Error("Unable to DoSomeSql, reached maximum number of retries.");
throw;
}
switch (sqlEx.Number)
{
case DBConstants.SQL_DEADLOCK_ERROR_CODE: //1205
log.Warn("DoSomeSql was deadlocked, will try again.");
break;
case DBConstants.SQL_TIMEOUT_ERROR_CODE: //-2
log.Warn("DoSomeSql was timedout, will try again.");
break;
default:
log.WarnFormat(buf.ToString(), sqlEx);
break;
}
System.Threading.Thread.Sleep(1000); //Can also use Math.Rand for a random interval of time
return DoSomeSql(asOfDate, ++retryCount);
}
}
您的查询与另一个查询陷入僵局。 另一个查询很可能是insert、update或delete查询，因为select本身不会死锁。
如果你不太在乎一致性，你可以使用with (nolock)提示：
FROM
[TB_EVENT_LOG] with (nolock)
,[TB_USER] with (nolock)
,[TB_READER] with (nolock)
这将导致您的查询不放置锁。 没有锁的查询不会导致死锁。 缺点是，当它与修改查询同时运行时，它可能会返回不一致的数据。