February 20, 2009 at 3:59 pm
All-
I could really use some help with a 1222 Locking Error. I found the SPID that was causing the deadlock and used the DBCC inputbuffer command to determine the last transaction ran by the app. The statement from the app was a select top statement (below).
The developers apps is setting time_out to -1, and the database is read committed. I have a fairly basic level of knowledge with how SQL handles locking, but was under the assumption that a select statement cannot be the culprit of a deadlock. I would love to be able to install profiler to use the deadlock chain event, but we don't have the available licenses at the moment for standard. Any help would be awesome! I've stopped the program for now, but this is only a short term solution. Thanks!
Edit: This is SQL Express 2005 (sorry posted this in the wrong section)
(@OldestDate datetime)select Top 20 CustNotes.NoteID as UniqueId, 'CustNotes' as TableName,'NoteId' as UniqueFieldName,'DispositionSent' as UpdateFieldName,CustNotes.CID, WebLead.CentralServerLeadId,'5' as LeadStatusSourceId,'NOTE' as EventCode, CreateDate as EventDateTime, Customers.Salesperson as EmpId, Employees.FullName as EmpName, CustNotes.Detail as Note from WebLead JOIN customers ON Customers.CID = Weblead.CID JOIN CustNotes ON CustNotes.CID = WebLead.CID Left JOIN Employees ON Employees.EmpId = Customers.Salesperson Where (WebLead.CentralServerLeadId > 0 AND WebLead.CentralServerLeadId is not null) AND (CustNotes.DispositionSent <> 1 OR CustNotes.DispositionSent is NULL) AND CustNotes.CreateDate >= @OldestDate AND (Convert(varchar,WebLead.LeadADF) <> Convert(varchar,CustNotes.Detail)) AND CreateDate >= (CONVERT(DATETIME, CONVERT(VARCHAR, DATEPART(mm, Weblead.EntryDate)) +'-'+CONVERT(VARCHAR, DATEPART(dd, Weblead.EntryDate)) +'-'+CONVERT(VARCHAR, DATEPART(yyyy, Weblead.EntryDate)) +' '+CONVERT(VARCHAR, DATEPART(hh, Weblead.EntryTime)) + ':' + CONVERT(VARCHAR, DATEPART(mi, Weblead.EntryTime)) + ':' + CONVERT(VARCHAR, DATEPART(ss, Weblead.EntryTime))))
February 20, 2009 at 5:14 pm
Thanks for moving this, Steve.
February 22, 2009 at 6:10 pm
Anybody have an ideas on this? Thanks for any help.
Zach
February 23, 2009 at 7:50 am
I have looked are your code many times and I did not see any transactions and you are not using cursor which uses implicit transactions like fetch. You are using SQL Server 2005 Express so you may want to move to SQL Server 2008 Express because it implemented some new ANSI SQL transaction nesting feature which let you pass in more than one transaction. If I find the ADO.NET team blog posting I will post it.
Here is the blog post and no it is not Microsoft definition it came from ANSI SQL after SQL Server 2005.
http://blogs.msdn.com/adonet/archive/2008/03/26/extending-lightweight-transactions-in-sqlclient.aspx
Kind regards,
Gift Peddie
February 23, 2009 at 3:01 pm
Gift Peddie (2/23/2009)
I have looked are your code many times and I did not see any transactions and you are not using cursor which uses implicit transactions like fetch. You are using SQL Server 2005 Express so you may want to move to SQL Server 2008 Express because it implemented some new ANSI SQL transaction nesting feature which let you pass in more than one transaction. If I find the ADO.NET team blog posting I will post it.Here is the blog post and no it is not Microsoft definition it came from ANSI SQL after SQL Server 2005.
http://blogs.msdn.com/adonet/archive/2008/03/26/extending-lightweight-transactions-in-sqlclient.aspx
Thanks for the input, Gift. I was just able to obtain a standard '05 license, so I can run the profiler to get some more information. Do you think I should update to full SQL '05 and run the profiler, or just bank on the fact that Express '08 will clear up the issue. I am running this app on about 1500 other servers sucessfully (without the locking errors).
February 23, 2009 at 3:11 pm
You could move the database to SQL Server 2005 developer edition and run profiler because it cost $50 or less and comes with the same features as the Enterprise edition. So if you find the issue you could fix it before deploying it back to Express because you cannot run SQL Server developer edition in production.
If that shows many transaction connections at the same time then you could just use 2008 because the 2008 Express also comes in x64 which is 64bits. You should also check if there is Anti Virus in the box because I did not see any problem with the code.
Kind regards,
Gift Peddie
February 23, 2009 at 3:36 pm
Are there any other process that are writing to these tables at the same time?
Although multiple Select's should not normally deadlock, I would certainly think that it was possible for one or more read-committed Selects without Snaphots to deadlock with a DML statement/transaction.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply