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
 
        Main.GetSession(Me.Page, mUserInfo, mDb)
        DT3 = mDb.MatchingRegs(RegOccID)
 
 
    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)
 
        mySQLCommand.Connection = myConn
        mySQLCommand.CommandText = SQLString
 
        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)
 
 
 

View 1 Replies


ADVERTISEMENT

Deadlock Due To Transactions Within A Single SPID(syslockinfo Table)

Jun 4, 2007

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.


rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW


1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000


1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000


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.

Thanks in advance!!

View 5 Replies View Related

Mirroring :: Email Deadlock Information When A Deadlock Occurs

Nov 10, 2015

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.

View 5 Replies View Related

Changing Connection Transactions To Database Transactions

May 22, 2005

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

View 3 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

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

Appreciate your help.

View 3 Replies View Related

Web App SQL Deadlock

Apr 26, 2008

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 abc@abc.com.<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 abc@abc.com.<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 abc@abc.com.<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 abc@abc.com.<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 abc@abc.com.<br><br>Thank you.");}finally{} }

View 5 Replies View Related

Deadlock

Jul 9, 2005

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.

View 3 Replies View Related

Deadlock

May 7, 2001

SQL 7.0
Hi All

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

View 2 Replies View Related

DEADLOCK

Jul 27, 2000

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?

All help is appreciated.
Thanks

Brett

View 4 Replies View Related

Deadlock

May 12, 2000

How can I reproduce a deadlock in 7.0?

View 1 Replies View Related

Deadlock

Nov 4, 2002

Hi,

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.

Deadlock encountered .... Printing deadlock information
2002-11-04 14:34:26.33 spid4
2002-11-04 14:34:26.33 spid4 Wait-for graph
2002-11-04 14:34:26.33 spid4
2002-11-04 14:34:26.33 spid4 Node:1

2002-11-04 14:34:26.33 spid4 KEY: 11:1205683443:11 (da00d1f77328) CleanCnt:1 Mode: X Flags: 0x0
2002-11-04 14:34:26.33 spid4 Grant List::
2002-11-04 14:34:26.33 spid4 Owner:0x2ffa89c0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:179 ECID:0

2002-11-04 14:34:26.33 spid4 SPID: 179 ECID: 0 Statement Type: SELECT INTO Line #: 44
2002-11-04 14:34:26.33 spid4 Input Buf: Language Event: EXEC dbo.PROC_RVM_CALCULATE 22567, 'centreAVashista'
2002-11-04 14:34:26.33 spid4 Requested By:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:166 ECID:0 Ec:(0x6f02568) Value:0x85146480 Cost:(0/14E0)
2002-11-04 14:34:26.33 spid4

2002-11-04 14:34:26.33 spid4 Node:2
2002-11-04 14:34:26.33 spid4 KEY: 11:1205683443:11 (df001773794a) CleanCnt:1 Mode: X Flags: 0x0

2002-11-04 14:34:26.33 spid4 Grant List::
2002-11-04 14:34:26.33 spid4 Owner:0x85147ee0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:166 ECID:0

2002-11-04 14:34:26.33 spid4 SPID: 166 ECID: 0 Statement Type: SELECT INTO Line #: 44
2002-11-04 14:34:26.33 spid4 Input Buf: Language Event: EXEC dbo.PROC_RVM_CALCULATE 22562, 'centreJTtest4'
2002-11-04 14:34:26.33 spid4 Requested By:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:179 ECID:0 Ec:(0x7dd0568) Value:0x3a24f920 Cost:(0/39BC)
2002-11-04 14:34:26.33 spid4 Victim Resource Owner:
2002-11-04 14:34:26.33 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:166 ECID:0 Ec:(0x6f02568) Value:0x85146480 Cost:(0/14E0)

Any pointer is much appreciated.

Thanks
Shailesh

View 2 Replies View Related

Deadlock

Dec 10, 1999

Help !!

How would you handle the deadlocks that happen often? Since the victim users complained about the disconnection.

Thank you ahead of time

View 2 Replies View Related

DeadLock

Apr 27, 2004

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

View 5 Replies View Related

Deadlock And MTS

Aug 4, 1999

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) ?

Herve Meftah

View 1 Replies View Related

Deadlock

Mar 30, 2001

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)

Any help will be appreciated.

View 2 Replies View Related

Deadlock !!!! :(

Apr 1, 2004

Hi

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.


James :(

View 6 Replies View Related

Deadlock

Jun 19, 2007

Can anyone help me to solve this deadlock?

2007-06-17 22:42:34.18 spid4 Wait-for graph
2007-06-17 22:42:34.18 spid4
2007-06-17 22:42:34.18 spid4 Node:1
2007-06-17 22:42:34.18 spid4 PAG: 66:1:26187 CleanCnt:1 Mode: IX Flags: 0x2
2007-06-17 22:42:34.18 spid4 Grant List 2::
2007-06-17 22:42:34.18 spid4 Owner:0x69f19520 Mode: IX Flg:0x0 Ref:2 Life:02000000 SPID:349 ECID:0
2007-06-17 22:42:34.18 spid4 SPID: 349 ECID: 0 Statement Type: UPDATE Line #: 1
2007-06-17 22:42:34.18 spid4 Input Buf: RPC Event: sp_prepexec;1
2007-06-17 22:42:34.18 spid4 Requested By:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:348 ECID:33 Ec:(0x5996C098) Value:0x2f8d2aa0 Cost:(0/0)
2007-06-17 22:42:34.18 spid4
2007-06-17 22:42:34.18 spid4 Node:2
2007-06-17 22:42:34.18 spid4 PAG: 66:1:3051 CleanCnt:1 Mode: SIU Flags: 0x2
2007-06-17 22:42:34.18 spid4 Grant List 1::
2007-06-17 22:42:34.18 spid4 Owner:0x65c57560 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:348 ECID:33
2007-06-17 22:42:34.18 spid4 SPID: 348 ECID: 33 Statement Type: UPDATE Line #: 22
2007-06-17 22:42:34.18 spid4 Input Buf: Language Event: exec [Extracts].dbo.asp_SPCUpdateDRPFieldsIR @Name = 'SPCExecStep B05-ASP'
2007-06-17 22:42:34.18 spid4 Grant List 2::
2007-06-17 22:42:34.18 spid4 Requested By:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:349 ECID:0 Ec:(0x5425B500) Value:0x674a2160 Cost:(0/5FD8)
2007-06-17 22:42:34.18 spid4 Victim Resource Owner:
2007-06-17 22:42:34.18 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:348 ECID:33 Ec:(0x5996C098) Value:0x2f8d2aa0 Cost:(0/0)

=============================
http://www.sqlserverstudy.com

View 12 Replies View Related

Why Does This Deadlock?

Jul 23, 2005

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

View 11 Replies View Related

Can You Tell Me Why This Causes A Deadlock?

Feb 4, 2006

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

View 8 Replies View Related

Deadlock

Jun 6, 2007

Hi,How we can detect deadlock from a database.Rahul

View 1 Replies View Related

Deadlock Help Please

Jun 15, 2007

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?

View 3 Replies View Related

Help With Deadlock

Jul 20, 2005

HiI have a deadlock situation and I am trying to debug my Trace Log. How do Ifind out what is the cause ?I can see from the trace I have an exclusive lock on a RID, but how can Ifind out what/where 'RID: 7:1:431830:13 ' is ?RegardsSteve:TraceLog2003-08-20 15:15:45.28 spid4Deadlock encountered .... Printing deadlock information2003-08-20 15:15:45.29 spid42003-08-20 15:15:45.29 spid4 Wait-for graph2003-08-20 15:15:45.29 spid42003-08-20 15:15:45.29 spid4 Node:12003-08-20 15:15:45.29 spid4 RID: 7:1:431830:13 CleanCnt:1Mode: X Flags: 0x22003-08-20 15:15:45.29 spid4 Grant List 0::2003-08-20 15:15:45.29 spid4 Owner:0x193e3400 Mode: X Flg:0x0Ref:0 Life:02000000 SPID:52 ECID:02003-08-20 15:15:45.31 spid4 SPID: 52 ECID: 0 Statement Type: UPDATELine #: 442003-08-20 15:15:45.31 spid4 Input Buf: RPC Event: ams_Load_Stock;12003-08-20 15:15:45.31 spid4 Requested By:2003-08-20 15:15:45.31 spid4 ResType:LockOwner Stype:'OR' Mode: USPID:55 ECID:0 Ec:(0x1A0DF5A0) Value:0x193e1800 Cost:(0/E58)2003-08-20 15:15:45.32 spid42003-08-20 15:15:45.32 spid4 Node:22003-08-20 15:15:45.32 spid4 RID: 7:1:431830:14 CleanCnt:1Mode: X Flags: 0x22003-08-20 15:15:45.32 spid4 Grant List 1::2003-08-20 15:15:45.32 spid4 Owner:0x193e3360 Mode: X Flg:0x0Ref:0 Life:02000000 SPID:55 ECID:02003-08-20 15:15:45.32 spid4 SPID: 55 ECID: 0 Statement Type: UPDATELine #: 522003-08-20 15:15:45.34 spid4 Input Buf: Language Event: DECLARE @RCintDECLARE @strResult varchar(8)DECLARE @strErrorDesc varchar(512)EXEC @RC = [msmprim].[msm].[ams_Populate_PSM_Stockrequest] @strResult OUTPUT, @strErrorDesc OUTPUTDECLARE @PrnLine nvarchar(4000)PRINT 'Stored Procedure: msmprim.msm2003-08-20 15:15:45.34 spid4 Requested By:2003-08-20 15:15:45.34 spid4 ResType:LockOwner Stype:'OR' Mode: USPID:52 ECID:0 Ec:(0x198A5558) Value:0x193e2e80 Cost:(0/94)2003-08-20 15:15:45.35 spid4 Victim Resource Owner:2003-08-20 15:15:45.35 spid4 ResType:LockOwner Stype:'OR' Mode: USPID:52 ECID:0 Ec:(0x198A5558) Value:0x193e2e80 Cost:(0/94)

View 1 Replies View Related

Deadlock Victim

Jul 10, 2006

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

View 2 Replies View Related

Tracing The Deadlock

Jul 12, 2006

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?

View 1 Replies View Related

SQL Server Deadlock?

Feb 14, 2008

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.

View 6 Replies View Related

Deadlock Troubleshooting

May 16, 2002

View 1 Replies View Related

Deadlock Alert

Sep 4, 2002

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?

View 1 Replies View Related

Deadlock....URGENT

Feb 8, 2000

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.....

Bindu

View 1 Replies View Related

Deadlock Logging

Nov 8, 2001

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?

Thx in advance.

Andy Richardson

View 2 Replies View Related

Deadlock In A Transaction

Jun 10, 2002

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.

2- Does such situation hold a deadlock.

i will apreciate your help. Thinks

View 1 Replies View Related

DeadLock Issue

Oct 15, 2004

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 .

View 10 Replies View Related

Deadlock On TAB Lock

Jan 24, 2006

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:


2006-01-18 09:51:37.87 spid4 ----------------------------------
2006-01-18 09:51:37.87 spid4 Starting deadlock search 15

Deadlock encountered .... Printing deadlock information
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Wait-for graph
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:1
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:77 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:2
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:64 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
found.
2006-01-18 09:51:37.87 spid4 ----------------------------------

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.

Hanson

View 9 Replies View Related

Deadlock With Port: 0

Apr 13, 2006

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.

Nick

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved