Web app SQL Deadlock

Question

Hello everyone,

Can you please help me on the following problem that i have.
I am using the microsoft web stress tools to simulate 100 concurrent users for one min, and the database will go into a deadlock for large part of the 100 concurrent users.

Please help...i've been stuck for many days already. Thanks...

Error 5: Transaction (Process ID 497) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

Here is my code.

My connection string:

<connectionStrings>
<add name="Connection_String" connectionString="Data Source=123.123.123.123;Initial Catalog=DBName;MultipleActiveResultSets=true;User ID=uname;Password=upass;connection timeout=60" providerName="System.Data.SqlClient"/>
</connectionStrings>


My web apps will call the below function. And the deadlock always occurs at the update statement (error 5). I believe it's probably the select statement above locked up the row. I've put in (UPDLOCK) for the select statement, and it still didn't work.


private void UpdateLogTables(string Station_ID, string ContentFileName, string FileSize, string IP)
{
ContentFileName = Regex.Replace(ContentFileName, "'", "''");
string AdFileName = "";
string AdFileNameClause = "";
string DownloadCount = "";
string TotalFileSize = "";
string DownloadDate = "";
string currentDate = "";
string tempDate = "";
string MyString = "";

SqlConnection MyConnection = new SqlConnection(System.Configuration.ConfigurationMa nager.ConnectionStrings["Connection_String"].ConnectionString);
SqlCommand myCommand = null;
SqlDataReader myReader = null;

try
{
MyConnection.Open();
MyString = "SELECT AdFileName FROM tblContentRef WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "'";
myCommand = new SqlCommand(MyString, MyConnection);
myCommand.CommandTimeout = 60;
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
AdFileName = myReader["AdFileName"].ToString();
}
}
catch (Exception e)
{
Response.Write("Error 2: " + e.Message + ".<br><br>Please email [email protected].<br><br>Thank you.");
}
finally
{
if (myReader != null)
{
myReader.Close();
}
}

if (AdFileName != "")
{
AdFileName = "'" + AdFileName.Replace("'", "''") + "'";
AdFileNameClause = "AdFileName = " + AdFileName;
}
else
{
AdFileName = "NULL";
AdFileNameClause = "AdFileName IS NULL";
}

InsertDetailsLogTable(Station_ID, ContentFileName, AdFileName, FileSize, IP, MyConnection);

SqlDataReader myReader2 = null;
SqlCommand myCommand2 = null;
bool Readable = false;

try
{
MyString = "SELECT DownloadCount, TotalFileSize, DownloadDate FROM tblSummaryLog (UPDLOCK) WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "' AND " + AdFileNameClause + " ORDER BY DownloadDate DESC";
myCommand2 = new SqlCommand(MyString, MyConnection);
myCommand2.CommandTimeout = 60;
myReader2 = myCommand2.ExecuteReader();

if (myReader2.Read())
{
DownloadCount = myReader2["DownloadCount"].ToString();
TotalFileSize = myReader2["TotalFileSize"].ToString();
DownloadDate = myReader2["DownloadDate"].ToString();
currentDate = DateTime.Now.ToString("M/d/yyyy");
string[] inDate = DownloadDate.Split(new char[] { ' ' });
tempDate = inDate[0];
Readable = true;
}

if (myReader2 != null)
{
myReader2.Close();
}

if (Readable)
{
if (tempDate == currentDate)
{
DownloadCount = Convert.ToString(Convert.ToInt32(DownloadCount) + 1);
TotalFileSize = Convert.ToString(Convert.ToInt32(TotalFileSize) + Convert.ToInt32(FileSize));
UpdateSummaryLogTable(DownloadCount, TotalFileSize, Station_ID, ContentFileName, AdFileNameClause, DownloadDate, MyConnection);
}
else
{
InsertSummaryLogTable(Station_ID, ContentFileName, AdFileName, FileSize, MyConnection);
}
}
else
{
InsertSummaryLogTable(Station_ID, ContentFileName, AdFileName, FileSize, MyConnection);
}
}
catch (Exception e)
{
Response.Write("Error 3: " + e.Message + ".<br><br>Please email [email protected].<br><br>Thank you.");
}
finally
{
if (MyConnection != null)
{
MyConnection.Close();
}
}
}

private void InsertDetailsLogTable(string Station_ID, string ContentFileName, string AdFileName, string FileSize, string IP, SqlConnection MyConnection)
{
string MyString = @"INSERT INTO tblDetailsLog ( Station_ID, ContentFileName, AdFileName, FileSize, IP, DateTime ) VALUES ( '" + Station_ID + "', '" + ContentFileName + "', " + AdFileName + ", '" + FileSize + "', '" + IP + "', getdate() )";

try
{
SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
MyCmd.CommandTimeout = 60;
MyCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Response.Write("Error 4: " + e.Message + ".<br><br>Please email [email protected].<br><br>Thank you.");
}
finally
{
}
}

private void UpdateSummaryLogTable(string DownloadCount, string TotalFileSize , string Station_ID , string ContentFileName , string AdFileNameClause, string DownloadDate, SqlConnection MyConnection)
{
string MyString = @"UPDATE tblSummaryLog SET DownloadCount = '" + DownloadCount + "', TotalFileSize = '" + TotalFileSize + "', DownloadDate = getdate() WHERE Station_ID = '" + Station_ID + "' AND ContentFileName = '" + ContentFileName + "' AND " + AdFileNameClause + " AND (CONVERT(CHAR(19), DownloadDate) = CONVERT(CHAR(19), CAST('" + DownloadDate + "' AS datetime)))";

try
{
SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
MyCmd.CommandTimeout = 60;
MyCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Response.Write("Error 5: " + e.Message + ".<br><br>Please email [email protected].<br><br>Thank you.");
}
finally
{
}
}

private void InsertSummaryLogTable(string Station_ID , string ContentFileName , string AdFileName , string TotalFileSize, SqlConnection MyConnection)
{
string MyString = @"INSERT INTO tblSummaryLog ( Station_ID, ContentFileName, AdFileName, DownloadCount, TotalFileSize, DownloadDate ) VALUES ( '" + Station_ID + "', '" + ContentFileName + "', " + AdFileName + ", '1', '" + TotalFileSize + "', getdate() )";

try
{
SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);
MyCmd.CommandTimeout = 60;
MyCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Response.Write("Error 6: " + e.Message + ".<br><br>Please email [email protected].<br><br>Thank you.");
}
finally
{
}
}