MS SQL Deadlock But No SQL Transactions Exists - Help
Mar 8, 2007
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?
Partial Class NotesDisplay
Inherits System.Web.UI.Page
Private mUserInfo As Database.UserInfo
Private mDb As Database
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim RegOccID As Integer = CInt(Request.QueryString(("RegOccID")))
Dim DT3 As Data.DataTable
Public Function MatchingRegs(ByVal RegOccID As Integer) As DataTable
Dim DT3 As Data.DataTable
DT3 = GetSQLTable("Select * from vw_RegsBeingMarked where RegOccID = " & RegOccID)
If DT3.Rows.Count = 0 Then
Throw New Exception("Reg occ not found")
End If
Dim Starts As Date
Dim Ends As Date
Dim Where As String
With DT3.Rows(0)
Starts = .Item("Starts")
Ends = .Item("Ends")
If .Item("Ends") Is DBNull.Value = False And .Item("RoomID") Is DBNull.Value = False And .Item("RegStaff") Is DBNull.Value = False Then
' Posible merge with other regs
Dim cmd As New 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.Value Then
Where &= " is null"
Else
Where &= " = " & CInt(.Item("RoomID2"))
End If
DT3.Rows.Clear()
DT3 = GetSQLTable("Select * from vw_RegsBeingMarked " & Where, cmd)
End If
Return DT3
End With
End Function
Public Function GetSQLTable(ByVal SQLString As String, Optional ByRef Cmd As SqlCommand = Nothing) As DataTable
Dim myConn As SqlConnection
Dim LocalCon As Boolean = False
If Cmd IsNot Nothing AndAlso Cmd.Connection IsNot Nothing Then
myConn = Cmd.Connection
If SQLString = "" Then
SQLString = Cmd.CommandText
End If
Else
myConn = DBOpenSQLConnection(SqlConEnum.Timetables)
LocalCon = True
End If
Try
GetSQLTable = GetSQLTableInternal(SQLString, myConn, Cmd)
Catch ex As Exception
Throw
Finally
If LocalCon Then
DBCloseSQLConnection(myConn)
End If
End Try
End Function
Private Function GetSQLTableInternal(ByVal SQLString As String, ByRef myConn As SqlConnection, Optional ByRef Cmd As SqlCommand = Nothing, Optional ByRef Trans As SqlTransaction = Nothing) As DataTable
Dim mySQLCommand As SqlCommand
Dim mySQLAdaptor As SqlDataAdapter
If Cmd Is Nothing Then
mySQLCommand = New SqlCommand
Else
mySQLCommand = Cmd
End If
mySQLCommand.Transaction = Trans
mySQLAdaptor = New SqlDataAdapter(mySQLCommand)
GetSQLTableInternal = New DataTable
mySQLAdaptor.Fill(GetSQLTableInternal)
End Function
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)
I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.
For the first one the lock was granted(transactionID:96462284) but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
I just wanted some clarifications. 1) what is the significance of req_transactionID column in syslockinfo table? 2) what is the relationship between req_transactionId column and spid column? 3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario? 4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level). 5) Will there be contention for locks between transactions within a single spid.
it is something like this: sp_cache sp_cache1 while sp_cache2 end while
the deadlock occurred when executing sp_cache 2. At that time there were only two transactionId values in the syslockinfo table for this spid,They were: 1) 96462284 2)96594607
If some body could please help me it would be really helpful for me.
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
Hi there, I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following: <code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction? Any help is greatly appreciated
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1) begin select @errno = @errno | 1 end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE dbo.t1 EXISTS TABLE : nested iteration. Table Scan. Forward scan. Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1 SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01) WHERE f1 = @p1 ) SET ROWCOUNT 0
In a high traffic environment, deadlocks eventually occur as number of data processes increase. How can deadlocks be avoided, minimized and resolved. Please kindly provide scenario examples and samples of T-SQL code. Thanks much.
Iam trying to bcp a table(residing on my prod server to my local machine from command prompt) .Actually the table iam trying to bcp has heavy updates and selects, from users (70 users). The users complain that system becomes slow.Is it got anything to do with my trying to bcp the mentioned table(table has 170,000 records).Also whenever i try to bcp this table, only after being chosen as the deadlock victim by Sql server,for 3 or 4 times that iam able to bcp the table.
Any help regarding this will be very much appreciated TIA kinnu
I am unable to control the granularity of locks in our queries. We are running queries through MTS and are getting deadlocks.
The batch includes two inserts and one select query - all are hitting on only one table. This table has a unique clustered and a unique nonclustered index as well as a primary key.
Within the batch, I have given a table hint to set transaction isolation level to READCOMMITTED, ROWLOCK for the insert statements, like this
INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........
and the same for the Select statement.
SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)
When I run sp_lock on the spid, I get output indicating that SS7 is placing a IX lock on the table. I'm pretty sure this is a big contributor to the deadlock.
I get the deadlock when I try to run more than one client with similar insert parameters.
How can I control the granularity to just rowlocks?
I am getting deadlock running a stored procedure from two machines. Looking at the error log (generated using trace flag 1204 and 3605), it seems the deadlock is on a key. But what I fail to understand is how come sql server granted exclusive lock on the key to both connections. The grant list shows that lock with Mode X is granted to both connection.
Can anyone help me resolve deadlock with following text
Parallel Query worker thread Involved in deadlock.
I am particularly interested in resolving details of above mentioned line,as I started getting dead lock more frequently now and when I look into query involved blocking and victim I see nothing that can cause deadlock they are update insert and select statement which were fine for long and all of sudden started giving problem.
Thanks in advance, for any knowledge share my mail id scraval@hotmail.com
Hi, When many users run some stored procedures I 've got some deadlocks. How to avoid that? We run large stored procedures code which are using sometime the same table. What is the best way for using the transaction isolation level, fillfactor indexes, procedure cache configuration ...etc to avoid that.
In addition, I am using MTS and sometimes the Tempdb is also locked, is it a Microsoft bug (again) ?
We had a dead lock every night 9:00pm. I found out Server/Current Activity --Object Locks : The error log showing error 17824, severity:10, state 0 DNCC TRACEON 208, SPID 28 DBCCTRACEOFF 208, SPID 28 In current activity --object locks and reapetedly showing "tempdb.dbo.sysobjects/sysindexes/syscolumns" 28:sa.master.dbo /INSERT /SQL_servername (MS SQLEW)
Sorry for bombading the forum with all these questions, but i am relatively new to sql 2000.
I am getting dead lock on the following procedure.
important background information 1. this is a multi user web-based call centre application 2. this procedure loads up a new contact based on priority
I see no reason how a dead lock could occur. does any one have any idea. could it be something else that is locking up resource used by this procedure?
CREATE PROCEDURE topcat.getNewContactInfo ( @contact_id int ) AS BEGIN begin transaction
declare @id int
set @id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)
UPDATE class_contact SET checked_in = 1 WHERE _id = @id SELECT TOP 1 * FROM class_contact WHERE _id = @id
commit END GO
wat i dont' get is that, this procedure only has one update statement, this is the only statement that could possibly hold a lock on another resource (i think) , i can't see how a dead lock can happen in this case since this procedure doesn't hold up 2 resources at a time.
Using SQL Server 2000 SP3a, I run the following in 2 query analizerwindows on the Northwind database, the second one always gets thedeadlock Msg 1205:Window 1:declare @cnt intselect @cnt = 5while @cnt > 0beginbegin transactionselect * from orders (updlock) where employeeid = 1update orders set employeeid = 1 where employeeid = 1waitfor delay '00:00:03'commitselect @cnt = @cnt -1endWindow 2:declare @cnt intselect @cnt = 5while @cnt > 0beginbegin transactionselect * from orders (updlock) where employeeid = 1 and customerid ='ERNSH'waitfor delay '00:00:02'commitselect @cnt = @cnt -1endThe query in the first window gets 123 rows and places update locks onthem, then updates them and commits. The query in the second windowgets a subset (about 5) of the results that window 1 gets also tryingto place update locks on the same rows. Shouldn't the query in window 2just wait for the transaction in window 1 to finish? why would itdeadlock?you can also get rid of the delay in the second window and it willdeadlock faster.thanks in advance.Eugene
Hi.create table joe(c1 integer not null, c2 integer not null)Two sessions:Session 1:BEGIN TRANinsert into joe (c1,c2) values (1,2)Session 2:BEGIN TRANinsert into joe (c1,c2) values (3,4)Session 1:select * from joeSession 2:select * from joeOne of the sessions gets a deadlock victim message.thanks,Joe
I have a table that every 30 minutes needs to be repopulated fromanother table that is recreated from scratch just before.What I did was this:CREATE PROCEDURE BatchUpdProducts ASbegin transactiondelete productsinsert into productsselect * from productsTempcommit transactionGOThis takes about 30 seconds to run. I tried it doing it with a cursor,row by row, but it took like 30 minutes to run instead. The problemis with the fast approach is, once in a while I get a deadlock errorin different areas trying to access the products table. Using SQLServer 2000 by the way.Any ideas?
Here is the exception I am getting, any idea why this might be happening:
Exception: ThisMethod is done: let this go: Exception: Transaction (Process ID 74) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Stack Trace: at
Hi, I need to trace deadlock, one of article was mentioning “QL Server Profiler's Create Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
Hi Guys. I just want to ask some insights on SQL Server Deadlock and what is the best way to handle deadlock in asp.net? Or something like a Try... Catch.. statement to handle the error? Please advice. Thanks in advance.
I have set up an alert to detect when Page Deadlocks rise above 0. Overnight I have DTS packages populating SQL Server and various other jobs (Cognos Cube Builds etc.). My alert detected a Deadlock during the night but all of my processes completed fine. My problem/misunderstanding is that my alert is still popping up every 5 mins saying there is a Deadlock yet there is nothing running or no-one accessing SQL server and I cannot see any trace of the Deadlock in the Current Activity. Is this normal or is it a bug?
Hi, Can I use the Create Trace Wizard to trace the cause for a deadlock....I have created a new Trace , so in the couse of today if I encounter a deadlock will I know....how is this indicated....Please do let me know as I am using this for the first time.....
I have (amongst many) an overnight SQL stored procedure that occasionally takes over an hour to run compared to its usual 2 minutes. It does the same each night, as does everything around it. (N.B. The job truncates and reinserts data into a table.)
I'm just wondering if a user request during the day that reads the table perhaps leave the PID open and thus a lock open with it. Then, perhaps my SQL job comes along and waits for the lock to release before it performs the truncate.
Couple of questions: 1. Could this happen? 2. If so, is there a log anywhere to suggest that a deadlock situation arose and PID x was the loser?
Hi guys; i use a transaction that takes a long time to execute.(Different updates on different tables) I want to use in my transaction a TABLOCKX after each update in order to Lock the table within the Transaction (Until the transaction will finish). This will normaly force another transaction using the same tables within the first transaction to wait until the lock will release.
1- Because there is no time out set . Does the second transaction wait until the first transaction be commited even if it takes more long time.
I serveral triggers in a table that is accessed by mutilple users in the application I am writing. I have come across a deadlock issue and have tried to resolve the issue by breaking down the triggers into many much smaller trans with no success. In general terms, can some one suggest some technique I am missing that I can try to avoid this issue .
I have a small database and a smalll table ( Table ID=565577053,with two indexes on this table). when more than one user connected, I got the deadlock on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I got this TAB lock situation instead as following:
How can I get rid of this deadlock without changing the application code(without using set the isolation level or NOLOCK hint). When I load more data, will this problem goes away? Any kind of help will be appreciate.
I've been diagnosing deadlocks for a last couple weeks at a client site and I understand the KEY: and TAB: locks issues. However, the client recently experienced a series of multi-branch deadlocks for which I can find no information. Attached is the deadlock text from the ERRORLOG. Any help would be much appreciated.
