January 7, 2009 at 6:54 pm
Hey guys, my Workgroup edition doesnt have profiler and I'm kinda stuck as to how to analyse and solve my deadlocks problems. I am getting roughly 100 errors daily.
Tried searching but workgroup edition doesnt seem to get alot of coverage. All help appreciated!
Error: System.Data.SqlClient.SqlException: Transaction (Process ID 85) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at AspDotNetStorefrontCommon.DB.ExecuteSQL(String Sql)
at AspDotNetStorefrontCommon.Customer.MakeAnonCustomerRecord(String& customerCode, Guid& customerGuid)
at AspDotNetStorefrontCommon.Customer.RequireCustomerRecord()
at AspDotNetStorefront.SkinBase.RequireCustomerRecord() in c:\eCommStore\AspDotNetStorefront\App_Code\SkinBase.cs:line 808
at AspDotNetStorefront.checkout1.OnInit(EventArgs e) in c:\eCommStore\AspDotNetStorefront\checkout1.aspx.cs:line 36
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
URL: https://www.XXXXX.com/checkout1.aspx
Referer: http://www.XXXXXX.com/index.php?_m=tickets&_a=submit
LastError: System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException: Transaction (Process ID 85) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at AspDotNetStorefrontCommon.DB.ExecuteSQL(String Sql)
at AspDotNetStorefrontCommon.Customer.MakeAnonCustomerRecord(String& customerCode, Guid& customerGuid)
at AspDotNetStorefrontCommon.Customer.RequireCustomerRecord()
at AspDotNetStorefront.SkinBase.RequireCustomerRecord() in c:\eCommStore\AspDotNetStorefront\App_Code\SkinBase.cs:line 808
at AspDotNetStorefront.checkout1.OnInit(EventArgs e) in c:\eCommStore\AspDotNetStorefront\checkout1.aspx.cs:line 36
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
--- End of inner exception stack trace ---
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.checkout1_aspx.ProcessRequest(HttpContext context) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\828af055\681f38a7\App_Web_checkout1.aspx.cdcab7d2.1caahwan.0.cs:line 0
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
Message: Exception of type 'System.Web.HttpUnhandledException' was thrown.
Source: System.Web
TargetSite: HandleError
January 7, 2009 at 11:33 pm
Turn traceflag 1222 on (DBCC TRACEON (1222,-1))
Any deadlocks that occur will have the deadlock graph written to the error log. The deadlock graph will have enough information to locate the processes involved in the deadlock and the resources they were locked over.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2009 at 3:45 am
Superb! built the indexes on the tables responsible. nice! brought down site load speeds from 8s to 2s.
Mucho appreciate it.
January 8, 2009 at 6:29 pm
Ok part 2 of problem.
I've been looking at the DBCC 1222 -1 logs and the pagelocks are happening on this table apparently waiting in queue for an update.
UPDATE XCustomer SET CustomerCode = CustomerID WHERE CustomerGuid = N'c8960572-113d-4bf9-863d-9d8485b244f5'
This table is used to hold non-registered customers and it gets updated everytime a visitor/session from a new ip is made. The website gets abt 16,000 uniques a day and the table has 7000000 rows in it.
I indexed ( non clustered non unique ) customercode, CustomerGuid and customerID which solved a huge speed issue for me, but the deadlocks are still strolling in.
How should I go about optimizing to get rid of the deadlocks?
January 9, 2009 at 12:38 am
langsoo (1/8/2009)
How should I go about optimizing to get rid of the deadlocks?
Please post the deadlock graph that traceflag 1222 produces.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2009 at 12:54 am
Here it is!
01/08/2009 21:32:39,spid11s,Unknown,waiter id=processffffffffb285fc48 mode=U
requestType=wait
01/08/2009 21:32:39,spid11s,Unknown,waiter-list
01/08/2009 21:32:39,spid11s,Unknown,owner id=process351df18 mode=U
01/08/2009 21:32:39,spid11s,Unknown,owner-list
01/08/2009 21:32:39,spid11s,Unknown,pagelock fileid=1 pageid=243960 dbid=5 objectname=dbxxx.dbo.WebCustomer
id=lockffffffff8ac2dc00 mode=U associatedObjectId=72057594289848320
01/08/2009 21:32:39,spid11s,Unknown,waiter event=e_waitPipeGetRow type=consumer
id=processffffffffccc8ff18
01/08/2009 21:32:39,spid11s,Unknown,waiter-list
01/08/2009 21:32:39,spid11s,Unknown,owner event=e_waitNone type=producer id=processffffffffb285fc48
01/08/2009 21:32:39,spid11s,Unknown,owner event=e_waitNone type=producer id=process33705c8
01/08/2009 21:32:39,spid11s,Unknown,owner-list
01/08/2009 21:32:39,spid11s,Unknown,exchangeEvent id=portffffffff80199080 nodeId=7
01/08/2009 21:32:39,spid11s,Unknown,waiter id=process33705c8 mode=U requestType=wait
01/08/2009 21:32:39,spid11s,Unknown,waiter-list
01/08/2009 21:32:39,spid11s,Unknown,owner id=process351df18 mode=U
01/08/2009 21:32:39,spid11s,Unknown,owner-list
01/08/2009 21:32:39,spid11s,Unknown,pagelock fileid=1 pageid=244208 dbid=5 objectname=dbxxx.dbo.WebCustomer
id=lockffffffff91abf0c0 mode=U associatedObjectId=72057594289848320
01/08/2009 21:32:39,spid11s,Unknown,waiter id=process351df18 mode=U requestType=wait
01/08/2009 21:32:39,spid11s,Unknown,waiter-list
01/08/2009 21:32:39,spid11s,Unknown,owner id=processffffffffccc8ff18 mode=U
01/08/2009 21:32:39,spid11s,Unknown,owner id=processffffffffccc8ff18 mode=U
01/08/2009 21:32:39,spid11s,Unknown,owner-list
01/08/2009 21:32:39,spid11s,Unknown,pagelock fileid=1 pageid=245430 dbid=5 objectname=dbxxx.dbo.WebCustomer
id=lockffffffff88211f00 mode=U associatedObjectId=72057594289848320
01/08/2009 21:32:39,spid11s,Unknown,resource-list
01/08/2009 21:32:39,spid11s,Unknown,UPDATE WebCustomer SET CustomerCode = CustomerID
WHERE CustomerGuid = N'851e456e-2389-4c82-96b9-4be6aa93f717'
01/08/2009 21:32:39,spid11s,Unknown,inputbuf
01/08/2009 21:32:39,spid11s,Unknown,UPDATE WebCustomer SET CustomerCode = CustomerID
WHERE CustomerGuid = N'851e456e-2389-4c82-96b9-4be6aa93f717'
01/08/2009 21:32:39,spid11s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000063d2bd05f8abeb4d22bf1bf4cb5165161af6a0c1
01/08/2009 21:32:39,spid11s,Unknown,(@1 nvarchar(4000))UPDATE [WebCustomer] set
[CustomerCode] = [CustomerID] WHERE [CustomerGuid]=@1
01/08/2009 21:32:39,spid11s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000480f1d3780b8abdf8ced0217895ad1795fa3a344
01/08/2009 21:32:39,spid11s,Unknown,executionStack
01/08/2009 21:32:39,spid11s,Unknown,process id=processffffffffccc8ff18 taskpriority=0
logused=20007 waittime=875 schedulerid=4 kpid=5900 status=suspended spid=61 sbid=0
ecid=0 priority=0 transcount=2 lastbatchstarted=2009-01-08T21:32:38.737 lastbatchcompleted=2009-01-08T21:32:38.737
clientapp=.Net SqlClient Data Provider hostname=CXXXXXXXX hostpid=3236 loginname=xxxzone
isolationlevel=read committed (2) xactid=572060026 currentdb=5 lockTimeout=4294967295
clientoption1=671088672 clientoption2=128056
01/08/2009 21:32:39,spid11s,Unknown,inputbuf
01/08/2009 21:32:39,spid11s,Unknown,UPDATE WebCustomer SET CustomerCode = CustomerID
WHERE CustomerGuid = N'851e456e-2389-4c82-96b9-4be6aa93f717'
01/08/2009 21:32:39,spid11s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000063d2bd05f8abeb4d22bf1bf4cb5165161af6a0c1
01/08/2009 21:32:39,spid11s,Unknown,(@1 nvarchar(4000))UPDATE [WebCustomer] set
[CustomerCode] = [CustomerID] WHERE [CustomerGuid]=@1
01/08/2009 21:32:39,spid11s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000480f1d3780b8abdf8ced0217895ad1795fa3a344
01/08/2009 21:32:39,spid11s,Unknown,executionStack
01/08/2009 21:32:39,spid11s,Unknown,process id=processffffffffb285fc48 taskpriority=0
logused=0 waitresource=PAGE: 5:1:243960 waittime=984 ownerId=572060026 transactionname=UPDATE
lasttranstarted=2009-01-08T21:32:38.753 XDES=0xffffffff8005b960 lockMode=U schedulerid=3
kpid=1968 status=suspended spid=61 sbid=0 ecid=4 priority=0 transcount=0 lastbatchstarted=2009-01-08T21:32:38.737
lastbatchcompleted=2009-01-08T21:32:38.737 clientapp=.Net SqlClient Data Provider
hostname=CXXXXXXXX hostpid=3236 isolationlevel=read committed (2) xactid=572060026
currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
01/08/2009 21:32:39,spid11s,Unknown,inputbuf
01/08/2009 21:32:39,spid11s,Unknown,UPDATE WebCustomer SET CustomerCode = CustomerID
WHERE CustomerGuid = N'7fae9038-5c7e-4055-ac5d-59bad701cc5c'
01/08/2009 21:32:39,spid11s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000a8c87112bc024a252352532228f1ecdef65e2352
01/08/2009 21:32:39,spid11s,Unknown,(@1 nvarchar(4000))UPDATE [WebCustomer] set
[CustomerCode] = [CustomerID] WHERE [CustomerGuid]=@1
01/08/2009 21:32:39,spid11s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000480f1d3780b8abdf8ced0217895ad1795fa3a344
01/08/2009 21:32:39,spid11s,Unknown,executionStack
01/08/2009 21:32:39,spid11s,Unknown,process id=process351df18 taskpriority=0 logused=0
waitresource=PAGE: 5:1:245430 waittime=968 ownerId=572060024 transactionname=UPDATE
lasttranstarted=2009-01-08T21:32:38.753 XDES=0xffffffffb084c3c0 lockMode=U schedulerid=8
kpid=5580 status=suspended spid=64 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2009-01-08T21:32:38.737
lastbatchcompleted=2009-01-08T21:32:38.737 clientapp=.Net SqlClient Data Provider
hostname=CXXXXXXXX hostpid=3236 isolationlevel=read committed (2) xactid=572060024
currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
01/08/2009 21:32:39,spid11s,Unknown,inputbuf
01/08/2009 21:32:39,spid11s,Unknown,UPDATE WebCustomer SET CustomerCode = CustomerID
WHERE CustomerGuid = N'851e456e-2389-4c82-96b9-4be6aa93f717'
01/08/2009 21:32:39,spid11s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000063d2bd05f8abeb4d22bf1bf4cb5165161af6a0c1
01/08/2009 21:32:39,spid11s,Unknown,(@1 nvarchar(4000))UPDATE [WebCustomer] set
[CustomerCode] = [CustomerID] WHERE [CustomerGuid]=@1
01/08/2009 21:32:39,spid11s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000480f1d3780b8abdf8ced0217895ad1795fa3a344
01/08/2009 21:32:39,spid11s,Unknown,executionStack
01/08/2009 21:32:39,spid11s,Unknown,process id=process33705c8 taskpriority=0 logused=0
waitresource=PAGE: 5:1:244208 waittime=984 ownerId=572060026 transactionname=UPDATE
lasttranstarted=2009-01-08T21:32:38.753 XDES=0xffffffff80067c40 lockMode=U schedulerid=6
kpid=3896 status=suspended spid=61 sbid=0 ecid=1 priority=0 transcount=0 lastbatchstarted=2009-01-08T21:32:38.737
lastbatchcompleted=2009-01-08T21:32:38.737 clientapp=.Net SqlClient Data Provider
hostname=CXXXXXXXX hostpid=3236 isolationlevel=read committed (2) xactid=572060026
currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
01/08/2009 21:32:39,spid11s,Unknown,process-list
01/08/2009 21:32:39,spid11s,Unknown,deadlock victim=process351df18
01/08/2009 21:32:39,spid11s,Unknown,deadlock-list
January 9, 2009 at 7:54 pm
ups
January 12, 2009 at 6:19 pm
Any genius out there willing to help?
January 21, 2009 at 7:43 am
langsoo (1/12/2009)
Any genius out there willing to help?
approximately 1,000,000 members of SSC ...
first glimps:
One thing I've seen in your 1222 list :
UPDATE WebCustomer SET CustomerCode = CustomerID
WHERE CustomerGuid = N'851e456e-2389-4c82-96b9-4be6aa93f717'
....
(@1 nvarchar(4000))UPDATE [WebCustomer] set
[CustomerCode] = [CustomerID] WHERE [CustomerGuid]=@1
Declare the variables using the correct datatype (uniqueidentifier ?).
If this is not being taken care of, you'll end up with at best an index scan, causing more locks than you'd expect and resulting in many deadlocks. (because of implicit conversions to nvarchar(4000))
Is your column CustomerGuid indexed ? If not you'll end up with a full table scan, resulting in the same as previous.
btw: Can you post the execution plan for these updates ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply