January 29, 2009 at 9:05 am
Hello!
Using SQL Server 2005, placing data into a database from a classic ASP page.
I have a stored procedure that inserts data into a table. The next line of code after the SP is a select statement that pulls a coulpe of bits of data from the recently (the line of code previous to it) inserted item to validate some things about the data received.
The problem I am having is that the table (even logged in locally to the server) becomes completely locked. As in if I open the table, even right on the server in the Database Management, I cannot edit records. I click on a record and get a "timeout period elapsed" error and that the "row was not committed" and the change is not committed. About ten minutes later the table is fine. Am I potentially causing a deadlock with my coding as specified above? In that the data is trying to be retrieved while the stored procedure is still executing?
Will simply adding a NOLOCK clause to my select statement (after the SP) fix this problem? Is a deadlock what is truly happening??
I have not been able to recreate, it happens randomly, and has only started to happen in the past week... any help is appreciated.
Thanks!
Eric Dimbleby
January 29, 2009 at 9:44 am
You can set up a trace to capture deadlock events. Have you tried doing that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 10:02 am
Yes, I'm running one right now.... haven't caught anything yet... I'm more wondering- do the symptoms (getting timeouts when trying to manually change data in the Management Console directly on the server) sound like it could be caused by a deadlock???
January 29, 2009 at 10:56 am
How are your system's perfmon counters looking? Are you running high CPU? Do you have a very low idle time?
Adding a NOLOCK hint on the SELECT won't hurt, as long as dirty reads are acceptable. Also make sure that you're performing a seek on your SELECTs with no bookmark lookups.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
January 29, 2009 at 11:07 am
It could be caused by a deadlock. Could also be caused by loss of network connectivity, or high network latency, or lost packets in a faulty network component. Could also be caused by a severely overloaded server, or workstation. Any number of things can cause timeout issues.
Start with tracing for deadlocks (which you are already doing). If that comes up negative, then you'll have to start checking other things, like is the command ever getting to the server.
I had an issue once where a dev spent the whole day complaining about how slow one of my procs was, but when we checked the trace logs, the proc hadn't been called all day. Turned out, there was a router or switch or something on the network that was faulty and was losing packets all over the place. That's one of the reasons I say the trace is the place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 11:50 am
It sounds like you're locking the resource and then blocking all others, not like a deadlock. Leave the deadlock monitors in place, but you should take a look at the wait states on the server. Here's a good reference on monitoring wait states from Microsoft.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2009 at 12:45 pm
If it was a deadlock, you'd be getting a different error.
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Are you seeing that?
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 30, 2009 at 7:11 am
Nope- not at all... okay so we've eliminated deadlocks. Now we'll monitor for blocks.. thanks for your help everybody...
January 30, 2009 at 7:12 am
Ahhh wait states... okay, let me try that... thanks for the resource!
January 30, 2009 at 1:02 pm
OK- I have new evidence!
It happened again, and I was able to prove:
1. It happens on only one table.
2. Restarting the SQL Server fixes the problem (of course)
3. I ran a Blocked Process report, here are the results, can anybody make anything out of this? I found 3 processes running at the time, all of which I could trace back to either myself or the user who reported the problem.
<process id="process6e87a8" taskpriority="0" logused="0" waitresource="OBJECT: 16:2027154267:0 " waittime="10906" ownerId="11162948"
transactionname="user_transaction" lasttranstarted="2009-01-30T14:23:30.327" XDES="0x20716250" lockMode="IX" schedulerid="1" kpid="7696"
status="suspended" spid="172" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-01-30T14:23:30.343"
lastbatchcompleted="2009-01-30T14:23:30.327" lastattention="2009-01-30T14:22:17.653" clientapp="Microsoft SQL Server Management Studio"
hostname="MCDAPP01" hostpid="280" loginname="MCD\Administrator" isolationlevel="read committed (2)" xactid="11162948" currentdb="16"
lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
(@FNAME nvarchar(7),@Param1 decimal(3,0),@Param2 decimal(1,0),@Param3 nvarchar(6),@Param4 nvarchar(4),@Param5 nvarchar(4000),@Param6
nvarchar(17),@Param7 nvarchar(4000),@Param8 nvarchar(4000),@Param9 nvarchar(41),@Param10 nvarchar(24),@Param11 nvarchar(4000),@Param12
nvarchar(5),@Param13 nvarchar(2),@Param14 nvarchar(5),@Param15 nvarchar(4000),@Param16 nvarchar(11),@Param17 nvarchar(4000),@Param18
nvarchar(26),@Param19 nvarchar(4000),@Param20 nvarchar(4000),@Param21 nvarchar(4000),@Param22 nvarchar(4000),@Param23 nvarchar(4000),@Param24
nvarchar(4000),@Param25 nvarchar(4000),@Param26 nvarchar(4000),@Param27 nvarchar(4000),@Param28 nvarchar(4000),@Param29 decimal(2,0),@Param30
bit,@Param31 nvarchar(6),@Param32 nvarchar(4000),@Param33 nvarchar(4000),@Param34 nvarchar(4000),@Param35 nvarchar(4000),@Param36
decimal(1,0),@Param37 datetime,@Param38 bit,@Param39 smalldatetime)UPDATE MCAP.REGISTRATION SET FNAME = @FNAME WHERE (PK_REGISTRATION =
@Param1) AND (FK_REGISTRATION_TEMPLATE = @Param2) AND (FK_REG_DATES IS N
<process status="suspended" waittime="796" spid="167" sbid="0" ecid="0" priority="0" transcount="0"
lastbatchstarted="2009-01-30T14:06:14.577" lastbatchcompleted="2009-01-30T14:06:14.577" lastattention="2009-01-30T14:06:03.937"
clientapp="2007 Microsoft Office system" hostname="D9P4RJ81" hostpid="3240" loginname="MCDREG_READ" isolationlevel="read committed (2)"
xactid="11144255" currentdb="16" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
SELECT "MCAP"."REGISTRATION_LOOKUP"."PK_REGISTRATION"
,"MCAP"."REGISTRATION_LOOKUP"."FK_REGISTRATION_TEMPLATE"
,"MCAP"."REGISTRATION_LOOKUP"."EVENT_TITLE"
,"MCAP"."REGISTRATION_LOOKUP"."LOCATION" ,"MCAP"."REG_DATES"."REG_DATE"
,"MCAP"."REGISTRATION_LOOKUP"."START_DATE"
,"MCAP"."REGISTRATION_LOOKUP"."END_DATE" ,"MCAP"."REG_DATES"."LOCATION"
,"MCAP"."REG_DATES"."REG_DATE" ,"MCAP"."REGISTRATION_LOOKUP"."FNAME"
,"MCAP"."REGISTRATION_LOOKUP"."LNAME" ,"MCAP"."REGISTRATION_LOOKUP"."SUFFIX"
,"MCAP"."REGISTRATION_LOOKUP"."CREDENTIALS"
,"MCAP"."REGISTRATION_LOOKUP"."JOB_TITLE"
,"MCAP"."REGISTRATION_LOOKUP"."CREDIT_DATE"
,"MCAP"."REGISTRATION_LOOKUP"."ORGANIZATION"
,"MCAP"."REGISTRATION_LOOKUP"."SCHOOL"
,"MCAP"."REGISTRATION_LOOKUP"."SCHOOL_DISTRICT"
,"MCAP"."REGISTRATION_LOOKUP"."ADDRESS" ,"MCAP"."REGISTRATION_LOOKUP"."ADDRESS2"
,"MCAP"."REGISTRATION_LOOKUP"."CITY" ,"MCAP"."REGISTRATION_LOOKUP"."STATE"
,"MCAP"."REGISTRATION_LOOKUP"."ZIP" ,"MCAP"."REGISTRATION_LOOKUP"."WORK_PHONE"
,"MCAP"."REGISTRATION_LOOKUP"."HOME_PH
February 1, 2009 at 11:44 am
It would also be helpful to see the code.
[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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply