Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
danielc
Starting Member
49 Posts |
 Posted - 2010-10-06 : 00:02:59
|
| Hello,We are having deadlock issues from code base that has previously not had issues. The following is a sample trace of one of many locks that occur. All are similar to this trace but always with different tables.- <EVENT_INSTANCE> <EventType>DEADLOCK_GRAPH</EventType> <PostTime>2010-10-05T20:48:45.027</PostTime> <SPID>18</SPID> - <TextData>- <deadlock-list>- <deadlock victim="process3c81198">- <process-list>- <process id="process3c81198" taskpriority="0" logused="524" waitresource="OBJECT: 34:1496248981:0" waittime="781" ownerId="2044228229" transactionname="InsertNewRecord" lasttranstarted="2010-10-05T20:48:44.213" XDES="0x4069beef0" lockMode="S" schedulerid="13" kpid="44124" status="suspended" spid="101" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-10-05T20:48:44.210" lastbatchcompleted="2010-10-05T20:48:44.210" clientapp=".Net SqlClient Data Provider" hostname="RKLW123P" hostpid="7552" loginname="name" isolationlevel="read uncommitted (1)" xactid="2044228229" currentdb="34" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">- <executionStack> <frame procname="A01dAccountDatesAdd" line="17" stmtstart="596" stmtend="934" sqlhandle="0x03002200300f172705101a01f89d00000100000000000000">INSERT INTO dbo.A01dAccountDates ([AccountNumber],[DateType],[DateValue],[Active],[DataSource]) VALUES (@AccountNumber,@DateType,@DateValue,@Active,@DataSource)</frame> <frame procname="adhoc" line="1" stmtstart="198" sqlhandle="0x0100220049a81b13702fb21d010000000000000000000000">EXEC @RETURN_VALUE = [dbo].[A01dAccountDatesAdd] @SessionID = @p0, @AccountNumber = @p1, @DateType = @p2, @DateValue = @p3, @Active = @p4, @DataSource = @p5</frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame> </executionStack> <inputbuf>(@p0 bigint,@p1 bigint,@p2 varchar(9),@p3 datetime,@p4 bit,@p5 varchar(3),@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[A01dAccountDatesAdd] @SessionID = @p0, @AccountNumber = @p1, @DateType = @p2, @DateValue = @p3, @Active = @p4, @DataSource = @p5</inputbuf> </process>- <process id="process49a9048" taskpriority="0" logused="6968" waitresource="OBJECT: 34:1921298500:15" waittime="781" ownerId="2044228204" transactionname="user_transaction" lasttranstarted="2010-10-05T20:48:44.183" XDES="0x915fbab0" lockMode="IX" schedulerid="16" kpid="46268" status="suspended" spid="121" sbid="0" ecid="0" priority="0" transcount="3" lastbatchstarted="2010-10-05T20:48:44.217" lastbatchcompleted="2010-10-05T20:48:44.217" clientapp=".Net SqlClient Data Provider" hostname="RKLW123P" hostpid="7552" loginname="name" isolationlevel="read uncommitted (1)" xactid="2044228204" currentdb="34" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">- <executionStack> <frame procname="A01dAccountDatesAdd" line="17" stmtstart="596" stmtend="934" sqlhandle="0x03002200300f172705101a01f89d00000100000000000000">INSERT INTO dbo.A01dAccountDates ([AccountNumber],[DateType],[DateValue],[Active],[DataSource]) VALUES (@AccountNumber,@DateType,@DateValue,@Active,@DataSource)</frame> <frame procname="adhoc" line="1" stmtstart="198" sqlhandle="0x01002200d286e806a0678010030000000000000000000000">EXEC @RETURN_VALUE = [dbo].[A01dAccountDatesAdd] @SessionID = @p0, @AccountNumber = @p1, @DateType = @p2, @DateValue = @p3, @Active = @p4, @DataSource = @p5</frame> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame> </executionStack> <inputbuf>(@p0 bigint,@p1 bigint,@p2 varchar(8),@p3 datetime,@p4 bit,@p5 varchar(3),@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[A01dAccountDatesAdd] @SessionID = @p0, @AccountNumber = @p1, @DateType = @p2, @DateValue = @p3, @Active = @p4, @DataSource = @p5</inputbuf> </process> </process-list>- <resource-list>- <objectlock lockPartition="15" objid="1921298500" subresource="FULL" dbid="34" objectname="A01dAccountDates" id="lock135410f80" mode="X" associatedObjectId="1921298500">- <owner-list> <owner id="process3c81198" mode="X" /> </owner-list>- <waiter-list> <waiter id="process49a9048" mode="IX" requestType="wait" /> </waiter-list> </objectlock>- <objectlock lockPartition="0" objid="1496248981" subresource="FULL" dbid="34" objectname="A01_AccountMaster" id="lock2ad8dd780" mode="X" associatedObjectId="1496248981">- <owner-list> <owner id="process49a9048" mode="X" /> </owner-list>- <waiter-list> <waiter id="process3c81198" mode="S" requestType="wait" /> </waiter-list> </objectlock> </resource-list> </deadlock> </deadlock-list> </TextData> <TransactionID /> <LoginName>sa</LoginName> <StartTime>2010-10-05T20:48:45.027</StartTime> <ServerName>server</ServerName> <LoginSid>AQ==</LoginSid> <EventSequence>609491233</EventSequence> <IsSystem>1</IsSystem> <SessionLoginName /> </EVENT_INSTANCE>Appreciate all help,Daniel |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-06 : 16:36:37
|
| Did the deadlocks just start happening on a (previously) working system with no software/hardware changes?Or has this code base been implemented on a new server/database?If you had a working system and then all of a sudden lots of deadlocks it sounds like it may be a corruption or hardware problem.Be One with the OptimizerTG |
 |
|
|
danielc
Starting Member
49 Posts |
Posted - 2010-10-06 : 23:39:10
|
| We do have a large complex environment and we try to manage changes to software development to the best of our capabilities. Unless a change slipped through the cracks we had no software changes.What kind of hardware and or corruption would cause this type of problem?Thanks,Daniel |
|
|
|
|
|
|
|
|
Serious deadlock issue