Avoiding SQl Server 2008 Deadlocks, Pt 1-Avoiding Cursors
I am having lots of trouble with deadlocking on my classic asp/sql server 2008 application. I am trying several things to optimize my query to avoid this. I am working on avoiding looping through the data and using rs.GetRows() instead. I also am researching other deadlock causes. I need some help on avoiding cursors. Please look at the following and let me know what I can do to further avoid deadlocks:
Thank you!
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT t.TeamsID, t.TeamName, t.Gender FROM Teams t INNER JOIN MeetTeams mt ON t.TeamsID = mt.TeamsID WHERE mt.MeetsID = " & lMeetID & " ORDER BY t.TeamName"
rs.Open sql, conn2, 1, 2
MeetTeams = rs.GetRows()
Set rs = NothingThank you!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BTW, I tried the defaults for cursor and lock types and it works well in some cases but in some cases the recordset returns no records. Do you know if any references I can use to determine when to use the defaults and what they do so I know when I will have to use something else?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's what I am doing but it seems like joins are different? Here is one that did not return any records with the default settings even though they are definitely there:
sql = "SELECT pr.Bib, p.LastName, p.FirstName, p.Gender, pr.Age, ir.FnlTime, p.ParticipantID, ir.ChipStart, p.City, p.St "
sql = sql & "FROM Participant p INNER JOIN IndResults ir ON p.ParticipantID = ir.ParticipantID "
sql = sql & "INNER JOIN PartRace pr ON pr.ParticipantID = p.ParticipantID "
sql = sql & "INNER JOIN RaceData rd ON rd.RaceID = pr.RaceID AND rd.RaceID = ir.RaceID "
sql = sql & "WHERE ir.RaceID = " & lRaceID & " AND ir.EventPl >= " & iFirstRcd & " AND ir.FnlTime <> '00:00:00.000' ORDER BY ir.EventPl"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 2
If rs.RecordCount > 0 Then
IndRslts = rs.GetRows()
Else
ReDim IndRslts(9, 0)
End If
rs.Close
Set rs = NothingSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't know what this means: "Could you please nolock hint to avid the deadlock situation."
ASKER
If I used the the default lock/cursor, this page, using the query above, would generate no results: http://www.gopherstateevents.com/results/fitness_events/results.asp?event_id=0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very helpful. Thanks!
What ended up working? Did we ever figure out what, "nolock hint to avid the deadlock situation"means?
ASKER
I believe that this is a work in progress that will involve me learning more about locks and cursors before I every get to the bottom of it. I am hoping that, even though I closed this and assigned points, the request for more info on the nolock hint will be supplied.
When I post race results I get hundreds of error messages regarding deadlocking (for CC Meets/Nordic but not for Fitness Events). I am hoping that eliminating looping and populating arrays with GetRows() will have the biggest effect.
As always, thanks so much for all your knowledge and assistance on things classic asp/sql server
When I post race results I get hundreds of error messages regarding deadlocking (for CC Meets/Nordic but not for Fitness Events). I am hoping that eliminating looping and populating arrays with GetRows() will have the biggest effect.
As always, thanks so much for all your knowledge and assistance on things classic asp/sql server
ASKER