The deadlock victim occurred executing the line
DT3 = GetSQLTable("Select * from vw_RegsBeingMarked " & Where, cmd)
NotesDisplay is the .aspx page
You can clearly see that no transaction was in use, so the deadlock should not have occurred as far as I am concerned (3rd time I seen something like this).
This happened on MS SQL 2005 SP1.
The other part of this deadlock was caused by a delete statement (which did have a transaction) deleting rows that may have formed part of this view but that should only resulted in a normal lock, not a deadlock.
I have included the complete code that was executed along with the error message for reference.
Can anyone explain this?
PartialClass NotesDisplay
Inherits System.Web.UI.Page
Private mUserInfoAs Database.UserInfo
Private mDbAs Database
ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load
Dim RegOccIDAsInteger =CInt(Request.QueryString(("RegOccID")))
Dim DT3As Data.DataTable
Main.GetSession(Me.Page, mUserInfo, mDb)
DT3 = mDb.MatchingRegs(RegOccID)
PublicFunction MatchingRegs(ByVal RegOccIDAsInteger)As DataTable
Dim DT3As Data.DataTable
DT3 = GetSQLTable("Select * from vw_RegsBeingMarked where RegOccID = " & RegOccID)
If DT3.Rows.Count = 0Then
ThrowNew Exception("Reg occ not found")
EndIf
Dim StartsAsDate
Dim EndsAsDate
Dim WhereAsString
With DT3.Rows(0)
Starts = .Item("Starts")
Ends = .Item("Ends")
If .Item("Ends")Is DBNull.Value =FalseAnd .Item("RoomID")Is DBNull.Value =FalseAnd .Item("RegStaff")Is DBNull.Value =FalseThen
' Posible merge with other regs
Dim cmdAsNew Data.SqlClient.SqlCommand
cmd.Parameters.AddWithValue("@.Starts", Starts)
cmd.Parameters.AddWithValue("@.Ends", Ends)
Where =" Where RoomID = " &CInt(.Item("RoomID")) &" AND Starts = @.Starts AND Ends = @.Ends AND RegStaff = " &CInt(.Item("RegStaff")) &" AND RoomID2 "
If .Item("RoomID2")Is DBNull.ValueThen
Where &=" is null"
Else
Where &=" = " &CInt(.Item("RoomID2"))
EndIf
DT3.Rows.Clear()
DT3 = GetSQLTable("Select * from vw_RegsBeingMarked " & Where, cmd)
EndIf
Return DT3
EndWith
EndFunction
PublicFunction GetSQLTable(ByVal SQLStringAsString,OptionalByRef CmdAs SqlCommand =Nothing)As DataTable
Dim myConnAs SqlConnection
Dim LocalConAsBoolean =False
If CmdIsNotNothingAndAlso Cmd.ConnectionIsNotNothingThen
myConn = Cmd.Connection
If SQLString =""Then
SQLString = Cmd.CommandText
EndIf
Else
myConn = DBOpenSQLConnection(SqlConEnum.Timetables)
LocalCon =True
EndIf
Try
GetSQLTable = GetSQLTableInternal(SQLString, myConn, Cmd)
Catch exAs Exception
Throw
Finally
If LocalConThen
DBCloseSQLConnection(myConn)
EndIf
EndTry
EndFunction
PrivateFunction GetSQLTableInternal(ByVal SQLStringAsString,ByRef myConnAs SqlConnection,OptionalByRef CmdAs SqlCommand =Nothing,OptionalByRef TransAs SqlTransaction =Nothing)As DataTable
Dim mySQLCommandAs SqlCommand
Dim mySQLAdaptorAs SqlDataAdapter
If CmdIsNothingThen
mySQLCommand =New SqlCommand
Else
mySQLCommand = Cmd
EndIf
mySQLCommand.Transaction = Trans
mySQLAdaptor =New SqlDataAdapter(mySQLCommand)
mySQLCommand.Connection = myConn
mySQLCommand.CommandText = SQLString
GetSQLTableInternal =New DataTable
mySQLAdaptor.Fill(GetSQLTableInternal)
EndFunction
Screen shot of SQL profiler deadlock information (showing lock types etc)
http://img299.imageshack.us/img299/9625/deadlockmb9.jpg
System.Data.SqlClient.SqlException: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.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 System.Data.SqlClient.SqlDataReader.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Database.GetSQLTableInternal(String SQLString, SqlConnection& myConn, SqlCommand& Cmd, SqlTransaction& Trans)
at Database.GetSQLTable(String SQLString, SqlCommand& Cmd)
at Database.MatchingRegs(Int32 RegOccID)
at NotesDisplay.Page_Load(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
********************
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.notesdisplay_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
The answer can be found here
http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/a05e7d7f4eabf61e/337c7c6d75c38452?lnk=st&q=ms+sql+server+2005+deadlock+no+transaction&rnum=25&hl=en#337c7c6d75c38452
No comments:
Post a Comment