deadlocks involving parallelism

We're experiencing a large number of deadlocks since we began running
SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper
threading intel processors. We don't have the same problem on Dell
6650's w/o the hyper threading. If I turn off the parallel query
processing option the deadlocks stop. I've tried all of the suggestions
from the Microsoft Knowledge Base under the following link -

http://support.microsoft.com/?kbid=837983

The only suggestion that actually yielded results was turning off
parallel query processing but I don't want to give up what should be a
performance advantage if it wasn't for the deadlocks. Query tuning and
index tuning hasn't helped. Any suggestions? I haven't applied SP4
yet. I'm wondering if anyone has seen the same problem resolved with
SP4.

*** Sent via Developersdex http://www.developersdex.com ***T Dubya (timber_toes@.bigfoot.com) writes:
> We're experiencing a large number of deadlocks since we began running
> SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper
> threading intel processors. We don't have the same problem on Dell
> 6650's w/o the hyper threading. If I turn off the parallel query
> processing option the deadlocks stop. I've tried all of the suggestions
> from the Microsoft Knowledge Base under the following link -

A general recommendation is to change "max degree of parallelism" to
the number of physical processors. Whether this will help your parallelism
deadlocks, I don't know, but you should make that configuration anyway.

As it was explained to me, HT processors creates that extra CPU by
giving it idle cycles from the first processor. But if you have a
parallel query, those idle cycles are not really there, and you get
a serialization of the processing.

If that does not, try tracking down the query/ies that have this
problem, and add "OPTION (MAXDOP 1)" to these queries, to turn off
parallelism for these queries.

> I haven't applied SP4 yet. I'm wondering if anyone has seen the same
> problem resolved with SP4.

I have no idea if that will help, but some general notes on SP4:

SP4 is here: http://www.microsoft.com/sql/downloads/2000/sp4.mspx.
Please observe the note about AWE. The note is out of date, since
there actually is a fix for the AWE problem; just follow the link
in the note.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the suggestion. I'll give it a try.
I found a "Best Practices" note in my Microsoft SQL Server 2000
Administrators Pocket Consultant on page 38 that recommends not
assigning the higher numbered processors (5,6,7, and 8) to the SQL
Server. It goes on to explain that Windows assigns deferred process
calls associated with network interface cards to the highest numbered
processors. If the system has two NICs, for example, the calls would be
directed to CPUs 7 and 8. Even though the default installation made
processors 0 through 7 available to the SQL Server it sounds like the
recommendation is to only make 0 through 3 available. What do you
think? Perhaps this would have the same effect as only assigning 4
processors for parallel execution of queries.

*** Sent via Developersdex http://www.developersdex.com ***|||T Dubya (timber_toes@.bigfoot.com) writes:
> Thanks for the suggestion. I'll give it a try.
> I found a "Best Practices" note in my Microsoft SQL Server 2000
> Administrators Pocket Consultant on page 38 that recommends not
> assigning the higher numbered processors (5,6,7, and 8) to the SQL
> Server. It goes on to explain that Windows assigns deferred process
> calls associated with network interface cards to the highest numbered
> processors. If the system has two NICs, for example, the calls would be
> directed to CPUs 7 and 8. Even though the default installation made
> processors 0 through 7 available to the SQL Server it sounds like the
> recommendation is to only make 0 through 3 available. What do you
> think? Perhaps this would have the same effect as only assigning 4
> processors for parallel execution of queries.

I will have to admit that the discussion went over my head here. If CPU:s
0-3 are the "default CPU" of each physical processor, this seems like
a good choice. I will have to admit that I don't know how processors
are numbered in a multi-processor HT box.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, Dubaya,

your problem is SQL Server 2000 SP3 - SP3 is not hiperthread aware
which means that if your query is parallelized into several worker
threads, these threads might end-up running concurrently on the
same physical processor, which means 2 threads running on 1 physical
processor due to Hyperthreading. I know that there have been made some
changes in build 818, and SP4, especially regarding HT and NUMA -
what you basically sohuld do is test your situation with build 818 or
SP4,
or turn off hyperthreading. Test, but be aware that Hyperthreading
is only giving you maybe 10% extra performance if you're lucky,
whereas
your parallisme within SQL Server can give you enormous amounts of
performance gains. Its no secret that Intel made hyperthreading since
the extra thread could run Antivirus software while the CPU was more
a less idle in some of their components. Running SQL Server 2000 with
hyperthreading can give you some headaches, try running on the latest
build
or turn of hyperthreading.sql

Deadlocks and multiple Indexed VIEWs on a table.

(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I can get help with.
I have a Table A that has a trigger to update Table B. There are about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, maybe they're getting
updated in a different *order* for different sessions? I would have assumed that it'd
always update in the same order, but I could see where maybe SQL Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas and
opinions!
Thanks! :-)
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_02AA_01C37229.4A089D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are about =6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, maybe =they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL Server =says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and Session =2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any further =ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_02AA_01C37229.4A089D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. SUM() =or BIG_COUNT().
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have a =Table A that has a trigger to update Table B. There are about 6 Indexed VIEWs(some of which are pretty "heavy") that use Table B. When =I have multiple sessions try toinsert into Table A, I'm getting consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And then =we'd have theclassic deadlock conditions of Session 1 requesting X and Y, =and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson

---

--=_NextPart_000_02AA_01C37229.4A089D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_008F_01C37211.C231F410
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_008F_01C37211.C231F410
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks, Tom! Would lock escalation contribute =to the propensity for a deadlock? I'm using the Profiler, and I see the =Deadlocks -- but I didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using =aggregation -- they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau" = wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. =SUM() or BIG_COUNT().
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have =a Table A that has a trigger to update Table B. There are about 6 =Indexed VIEWs(some of which are pretty "heavy") that use Table B. =When I have multiple sessions try toinsert into Table A, I'm getting =consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And =then we'd have theclassic deadlock conditions of Session 1 requesting X and =Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson

---

--=_NextPart_000_008F_01C37211.C231F410--|||This is a multi-part message in MIME format.
--=_NextPart_000_02DF_01C3722C.17629510
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Lock escalation converts a row or page lock to a table lock. If you =have an exclusive table lock (as in a monstrous INSERT or UPDATE), =that's going to block everything else from accessing the table. Now, if =you have a number of indexed views that access table B, then access to =those views is also blocked. The longer a lock is held, the greater the =chance for a deadlock.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:#UTgyxkcDHA.3620@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_02DF_01C3722C.17629510
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Lock escalation converts a row or page =lock to a table lock. If you have an exclusive table lock (as in a monstrous INSERT or UPDATE), that's going to block everything else =from accessing the table. Now, if you have a number of indexed views =that access table B, then access to those views is also blocked. The =longer a lock is held, the greater the chance for a deadlock.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:#UTgyxkcDHA.3620=@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute =to the propensity for a deadlock? I'm using the Profiler, and I see the =Deadlocks -- but I didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using =aggregation -- they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau" = wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. =SUM() or BIG_COUNT().
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have =a Table A that has a trigger to update Table B. There are about 6 =Indexed VIEWs(some of which are pretty "heavy") that use Table B. =When I have multiple sessions try toinsert into Table A, I'm getting =consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And =then we'd have theclassic deadlock conditions of Session 1 requesting X and =Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson

---

--=_NextPart_000_02DF_01C3722C.17629510--|||This is a multi-part message in MIME format.
--=_NextPart_000_00BB_01C37219.8C7BD360
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks again, Tom!
Yeah -- I don't think we have large INSERTs/UPDATEs, but rather just =row-at-a-time. It's puzzling to me why we're seeing these deadlocks, =but we are. Even if we have a bunch of processes just INSERT into this =Table A and have all the Indexed VIEWs get updated we get deadlocked. =I'd think that this process would always have the same "flow" and =potentially avoid deadlocks because there isn't much processing going on =with the exception of the Indexed VIEW updates that are "behind the =scenes".
Do you have any recommendations beyond looking for Escalating Locks that =I should examine? And, if we see Escalation, do you have =tips/techniques for what I can do about that?
Thanks!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23at1k2kcDHA.1204@.TK2MSFTNGP12.phx.gbl...
Lock escalation converts a row or page lock to a table lock. If you =have an exclusive table lock (as in a monstrous INSERT or UPDATE), =that's going to block everything else from accessing the table. Now, if =you have a number of indexed views that access table B, then access to =those views is also blocked. The longer a lock is held, the greater the =chance for a deadlock.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:#UTgyxkcDHA.3620@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case =you can detect this through the Profiler. It's also possible that you =are using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful =I can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." =And then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_00BB_01C37219.8C7BD360
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks again, Tom!
Yeah -- I don't think we have large INSERTs/UPDATEs, =but rather just row-at-a-time. It's puzzling to me why we're seeing =these deadlocks, but we are. Even if we have a bunch of processes just =INSERT into this Table A and have all the Indexed VIEWs get updated we get deadlocked. I'd think that this process would always have the same ="flow" and potentially avoid deadlocks because there isn't much processing =going on with the exception of the Indexed VIEW updates that are "behind the scenes".
Do you have any recommendations beyond looking for =Escalating Locks that I should examine? And, if we see Escalation, do you =have tips/techniques for what I can do about that?
Thanks!
John Peterson
"Tom Moreau" = wrote in message news:%23at1k2kcDHA.=1204@.TK2MSFTNGP12.phx.gbl...
Lock escalation converts a row or =page lock to a table lock. If you have an exclusive table lock (as in a monstrous INSERT or UPDATE), that's going to block everything =else from accessing the table. Now, if you have a number of indexed views =that access table B, then access to those views is also blocked. The =longer a lock is held, the greater the chance for a deadlock.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:#UTgyxkcDHA.3620=@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation =contribute to the propensity for a deadlock? I'm using the Profiler, and I see the = Deadlocks -- but I didn't include the Lock:Escalation =Event.

I don't think any of those Indexed VIEWs are using = aggregation -- they're just pulling a lot of data from a lot of =disparate tables.

Thanks again for any help you can =provide!

John Peterson

"Tom Moreau" = wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - =e.g. SUM() or BIG_COUNT().
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I =have a Table A that has a trigger to update Table B. There are about =6 Indexed VIEWs(some of which are pretty "heavy") that use Table =B. When I have multiple sessions try toinsert into Table A, I'm =getting consistent deadlocks.I'm postulating that perhaps, when the =Indexed VIEWs get updated, maybe they're gettingupdated in a different =*order* for different sessions? I would have assumed that =it'dalways update in the same order, but I could see where maybe SQL Server =says, "Oops...thisindex is busy, so I'll go ahead and update this =other one first." And then we'd have theclassic deadlock conditions =of Session 1 requesting X and Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to =solicit any further ideas andopinions!Thanks! =:-)John Peterson

---

--=_NextPart_000_00BB_01C37219.8C7BD360--

deadlocks after dbcc checkdb

sql2k sp3
Over the weekend I had severe corruption and needed to do DBCC with
repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
different tables. Any ideas as to why? I know I can trace them and figure
them out, but I was curious as to why this would start to happen after my
problems?
TIA, ChrisR.The only thing I can think of is that the repair dropped some pages. The
data now resides on fewer pages, fewer pages with the same number of locks =deadlock..
The problem with this thinking is that I do not know if DBCC repair moves
rows or simply drops bad page links..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"ChrisR" <noemail@.bla.com> wrote in message
news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> sql2k sp3
> Over the weekend I had severe corruption and needed to do DBCC with
> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
> different tables. Any ideas as to why? I know I can trace them and figure
> them out, but I was curious as to why this would start to happen after my
> problems?
> TIA, ChrisR.
>|||How about if repair removed a whole index in order to repair? That could increase deadlock due to
longer execution time = overlap. Or? I don't know whether repair does drop a whole index, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
> The only thing I can think of is that the repair dropped some pages. The
> data now resides on fewer pages, fewer pages with the same number of locks => deadlock..
> The problem with this thinking is that I do not know if DBCC repair moves
> rows or simply drops bad page links..
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
>> sql2k sp3
>> Over the weekend I had severe corruption and needed to do DBCC with
>> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
>> different tables. Any ideas as to why? I know I can trace them and figure
>> them out, but I was curious as to why this would start to happen after my
>> problems?
>> TIA, ChrisR.
>>
>|||Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
them) or do anything that would cause deadlocks. However, it probably had to
delete some pages so whatever application logic was inherent in the database
is now broken (i.e. if your app relied on certain values being present in
multiple tables, that may not be the case any more).
Did you study the output of repair to work out roughly what it dropped? I
take it your backup was broken too which is why you had to run repair rather
than restoring your backup? Did you work out why the corruption happened in
the first place so you can ensure it doesn't happen again?
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
> How about if repair removed a whole index in order to repair? That could
increase deadlock due to
> longer execution time = overlap. Or? I don't know whether repair does drop
a whole index, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
> > The only thing I can think of is that the repair dropped some pages. The
> > data now resides on fewer pages, fewer pages with the same number of
locks => > deadlock..
> >
> > The problem with this thinking is that I do not know if DBCC repair
moves
> > rows or simply drops bad page links..
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > (Please respond only to the newsgroup.)
> >
> > I support the Professional Association for SQL Server ( PASS) and it's
> > community of SQL Professionals.
> > "ChrisR" <noemail@.bla.com> wrote in message
> > news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
> >> sql2k sp3
> >>
> >> Over the weekend I had severe corruption and needed to do DBCC with
> >> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots of
> >> different tables. Any ideas as to why? I know I can trace them and
figure
> >> them out, but I was curious as to why this would start to happen after
my
> >> problems?
> >>
> >> TIA, ChrisR.
> >>
> >>
> >
> >
>|||Hardware issues were the cause. Yes the backups were bad. MS is now
evaluating the output. Thanks to all who helped.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:OXOmsFPUFHA.1148@.tk2msftngp13.phx.gbl...
> Assuming you're on SQL 2000, repair won't drop whole indexes (only rebuild
> them) or do anything that would cause deadlocks. However, it probably had
> to
> delete some pages so whatever application logic was inherent in the
> database
> is now broken (i.e. if your app relied on certain values being present in
> multiple tables, that may not be the case any more).
> Did you study the output of repair to work out roughly what it dropped? I
> take it your backup was broken too which is why you had to run repair
> rather
> than restoring your backup? Did you work out why the corruption happened
> in
> the first place so you can ensure it doesn't happen again?
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:ecDwqnBUFHA.2928@.TK2MSFTNGP09.phx.gbl...
>> How about if repair removed a whole index in order to repair? That could
> increase deadlock due to
>> longer execution time = overlap. Or? I don't know whether repair does
>> drop
> a whole index, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:eRZ6oeBUFHA.2172@.tk2msftngp13.phx.gbl...
>> > The only thing I can think of is that the repair dropped some pages.
>> > The
>> > data now resides on fewer pages, fewer pages with the same number of
> locks =>> > deadlock..
>> >
>> > The problem with this thinking is that I do not know if DBCC repair
> moves
>> > rows or simply drops bad page links..
>> >
>> >
>> > --
>> > Wayne Snyder MCDBA, SQL Server MVP
>> > Mariner, Charlotte, NC
>> > (Please respond only to the newsgroup.)
>> >
>> > I support the Professional Association for SQL Server ( PASS) and it's
>> > community of SQL Professionals.
>> > "ChrisR" <noemail@.bla.com> wrote in message
>> > news:uA12OEBUFHA.3644@.TK2MSFTNGP10.phx.gbl...
>> >> sql2k sp3
>> >>
>> >> Over the weekend I had severe corruption and needed to do DBCC with
>> >> repair_allow_data_loss. Since then Ive had tons of deadlocks on lots
>> >> of
>> >> different tables. Any ideas as to why? I know I can trace them and
> figure
>> >> them out, but I was curious as to why this would start to happen after
> my
>> >> problems?
>> >>
>> >> TIA, ChrisR.
>> >>
>> >>
>> >
>> >
>>
>

Deadlock trouble shooting

Hi all, I have a rather annoying problem finding out what causes my deadlocks.
I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
scheduled tasks and perhaps 20 databases. I have read a couple of articles
regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
and 3605. I am trying to follow the steps of this article in order to
pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
Furthermore I have used the sqldiag utility as well as profiler and gotten
the outputfile sqldiag.txt and I am looking at it with the help of the
article:
Here are two examples of deadlocks from the sqldiag.txt:
--
2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
2005-03-30 11:29:16.19 spid4 Target Resource Owner:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
verifying cycle
2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4
Deadlock encountered ... Printing deadlock information
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Wait-for graph
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:1
2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Wait List:
2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
Ref:1 Life:00000000 SPID:90 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
move_vcpu_dtl_from_spots @.days2copy = '1'
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:2
2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Grant List 1::
2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:88 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
import_ssnc_dtl
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:3
2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Grant List 0::
2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:86 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
import_pdc_sweden
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
was found.
2005-03-30 11:29:16.19 spid4 --
2005-03-30 11:29:21.19 spid4 --
2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
2005-03-30 11:29:21.19 spid4 Target Resource Owner:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
verifying cycle
2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4
Deadlock encountered ... Printing deadlock information
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Wait-for graph
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Node:1
2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:21.19 spid4 Grant List 1::
2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:88 ECID:0
2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
import_ssnc_dtl
2005-03-30 11:29:21.19 spid4 Requested By:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Node:2
2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:21.19 spid4 Grant List 0::
2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:86 ECID:0
2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
import_pdc_sweden
2005-03-30 11:29:21.19 spid4 Requested By:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
was found.
2005-03-30 11:29:21.19 spid4
--
Now as I understand it there should also be a value named "Key XXXX"
somewhere after the "Grant List" but I can not find it. I guess , correct me
if I am wrong, I need that value in order to take the trouble shooting
further... So this is as far as I get. Could someone please give me some
hints and pointers of what I need to do next'
Thanks on forehand!!
--
Alex
--
Computer Science StudentAlexandre geia ,
I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
your code regarding this line and try to find any loops or any other issue.
Also you can delete and create your indexes
Let me know if you need anything else
Andreas
"Alexander Simeonidis" wrote:
> Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> scheduled tasks and perhaps 20 databases. I have read a couple of articles
> regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> and 3605. I am trying to follow the steps of this article in order to
> pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> Furthermore I have used the sqldiag utility as well as profiler and gotten
> the outputfile sqldiag.txt and I am looking at it with the help of the
> article:
> Here are two examples of deadlocks from the sqldiag.txt:
> --
> 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> verifying cycle
> 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4
> Deadlock encountered ... Printing deadlock information
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Wait-for graph
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:1
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Wait List:
> 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> Ref:1 Life:00000000 SPID:90 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> move_vcpu_dtl_from_spots @.days2copy = '1'
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:2
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Grant List 1::
> 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:88 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> import_ssnc_dtl
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:3
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Grant List 0::
> 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:86 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> import_pdc_sweden
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> was found.
> 2005-03-30 11:29:16.19 spid4 --
> 2005-03-30 11:29:21.19 spid4 --
> 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> verifying cycle
> 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4
> Deadlock encountered ... Printing deadlock information
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Wait-for graph
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Node:1
> 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:21.19 spid4 Grant List 1::
> 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:88 ECID:0
> 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> import_ssnc_dtl
> 2005-03-30 11:29:21.19 spid4 Requested By:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Node:2
> 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:21.19 spid4 Grant List 0::
> 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:86 ECID:0
> 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> import_pdc_sweden
> 2005-03-30 11:29:21.19 spid4 Requested By:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> was found.
> 2005-03-30 11:29:21.19 spid4
> --
> Now as I understand it there should also be a value named "Key XXXX"
> somewhere after the "Grant List" but I can not find it. I guess , correct me
> if I am wrong, I need that value in order to take the trouble shooting
> further... So this is as far as I get. Could someone please give me some
> hints and pointers of what I need to do next'
> Thanks on forehand!!
>
> --
> Alex
> --
> Computer Science Student|||Geia Andrea :) kai efcharisto!
I am afraid the problem is not that simple. I did as you said and checked my
three
stored procedures, none had anything in common on line #50.
One had a print cmd,
the other an exec cmd and the third
an if stmnt. And these cmds had nothing in common i.e writing to the same
table etc.. So I am still looking for pointers of how to take my
troubleshooting further.
Thank you for your answer...
"Andreas Mavrogenis" wrote:
> Alexandre geia ,
> I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> your code regarding this line and try to find any loops or any other issue.
> Also you can delete and create your indexes
> Let me know if you need anything else
> Andreas
>
> "Alexander Simeonidis" wrote:
> > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > and 3605. I am trying to follow the steps of this article in order to
> > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > the outputfile sqldiag.txt and I am looking at it with the help of the
> > article:
> > Here are two examples of deadlocks from the sqldiag.txt:
> > --
> > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> >
> > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > verifying cycle
> > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4
> > Deadlock encountered ... Printing deadlock information
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:1
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Wait List:
> > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > Ref:1 Life:00000000 SPID:90 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > move_vcpu_dtl_from_spots @.days2copy = '1'
> >
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:2
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:88 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > import_ssnc_dtl
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:3
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:86 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > import_pdc_sweden
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > was found.
> > 2005-03-30 11:29:16.19 spid4 --
> >
> > 2005-03-30 11:29:21.19 spid4 --
> > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> >
> > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > verifying cycle
> > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4
> > Deadlock encountered ... Printing deadlock information
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Node:1
> > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:88 ECID:0
> > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > import_ssnc_dtl
> > 2005-03-30 11:29:21.19 spid4 Requested By:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Node:2
> > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:86 ECID:0
> > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > import_pdc_sweden
> > 2005-03-30 11:29:21.19 spid4 Requested By:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > was found.
> > 2005-03-30 11:29:21.19 spid4
> > --
> >
> > Now as I understand it there should also be a value named "Key XXXX"
> > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > if I am wrong, I need that value in order to take the trouble shooting
> > further... So this is as far as I get. Could someone please give me some
> > hints and pointers of what I need to do next'
> >
> > Thanks on forehand!!
> >
> >
> > --
> > Alex
> > --
> > Computer Science Student|||Geia,
Did you droped your indexes ?
Andreas
"Alexander Simeonidis" wrote:
> Geia Andrea :) kai efcharisto!
> I am afraid the problem is not that simple. I did as you said and checked my
> three
> stored procedures, none had anything in common on line #50.
> One had a print cmd,
> the other an exec cmd and the third
> an if stmnt. And these cmds had nothing in common i.e writing to the same
> table etc.. So I am still looking for pointers of how to take my
> troubleshooting further.
> Thank you for your answer...
> "Andreas Mavrogenis" wrote:
> > Alexandre geia ,
> >
> > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > your code regarding this line and try to find any loops or any other issue.
> >
> > Also you can delete and create your indexes
> >
> > Let me know if you need anything else
> > Andreas
> >
> >
> > "Alexander Simeonidis" wrote:
> >
> > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > and 3605. I am trying to follow the steps of this article in order to
> > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > article:
> > > Here are two examples of deadlocks from the sqldiag.txt:
> > > --
> > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > >
> > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > verifying cycle
> > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > >
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > import_ssnc_dtl
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > import_pdc_sweden
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > was found.
> > > 2005-03-30 11:29:16.19 spid4 --
> > >
> > > 2005-03-30 11:29:21.19 spid4 --
> > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > >
> > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > verifying cycle
> > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > import_ssnc_dtl
> > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > import_pdc_sweden
> > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > was found.
> > > 2005-03-30 11:29:21.19 spid4
> > > --
> > >
> > > Now as I understand it there should also be a value named "Key XXXX"
> > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > if I am wrong, I need that value in order to take the trouble shooting
> > > further... So this is as far as I get. Could someone please give me some
> > > hints and pointers of what I need to do next'
> > >
> > > Thanks on forehand!!
> > >
> > >
> > > --
> > > Alex
> > > --
> > > Computer Science Student|||Hi, do you mean dropping all my indexes on the tables involved?
I can not drop the indexes on my tables because the only index I have is the
PK.
To explain the flow of how my stored procedures work:
[Raw data table] -> [tmp-table ] -> [data-table]
The raw data tables contains no keys whatsoever and they are deleted after
each moving of data to the tmp tables. The tmp tables are also deleted after
each move of the data to the data tables. And they have no indexes or keys
either.
So the only table that have indexes are the datatables and for them the
index is the PK. But the PK consists of 5-6 different columns and the amount
of data in the table is about half of million rows.
The problems with deadlocks most often arises when I try to move data from
the raw data tables to the tmp tables.
Thanks a lot for your help and patience...
"Andreas Mavrogenis" wrote:
> Geia,
> Did you droped your indexes ?
> Andreas
> "Alexander Simeonidis" wrote:
> > Geia Andrea :) kai efcharisto!
> > I am afraid the problem is not that simple. I did as you said and checked my
> > three
> > stored procedures, none had anything in common on line #50.
> > One had a print cmd,
> > the other an exec cmd and the third
> > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > table etc.. So I am still looking for pointers of how to take my
> > troubleshooting further.
> > Thank you for your answer...
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Alexandre geia ,
> > >
> > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > your code regarding this line and try to find any loops or any other issue.
> > >
> > > Also you can delete and create your indexes
> > >
> > > Let me know if you need anything else
> > > Andreas
> > >
> > >
> > > "Alexander Simeonidis" wrote:
> > >
> > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > and 3605. I am trying to follow the steps of this article in order to
> > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > article:
> > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > --
> > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:16.19 spid4 --
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 --
> > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:21.19 spid4
> > > > --
> > > >
> > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > further... So this is as far as I get. Could someone please give me some
> > > > hints and pointers of what I need to do next'
> > > >
> > > > Thanks on forehand!!
> > > >
> > > >
> > > > --
> > > > Alex
> > > > --
> > > > Computer Science Student|||Alexandre gria kai pali,
Look, try to delete * from tmp before raw data insertion. Try the insertion
with insert statement rather that the update. I saw the update statement in
the error that you posted.
Also, try to put indexes on you tables if your app allows
Try that and let me know,
Andreas
"Alexander Simeonidis" wrote:
> Hi, do you mean dropping all my indexes on the tables involved?
> I can not drop the indexes on my tables because the only index I have is the
> PK.
> To explain the flow of how my stored procedures work:
> [Raw data table] -> [tmp-table ] -> [data-table]
> The raw data tables contains no keys whatsoever and they are deleted after
> each moving of data to the tmp tables. The tmp tables are also deleted after
> each move of the data to the data tables. And they have no indexes or keys
> either.
> So the only table that have indexes are the datatables and for them the
> index is the PK. But the PK consists of 5-6 different columns and the amount
> of data in the table is about half of million rows.
> The problems with deadlocks most often arises when I try to move data from
> the raw data tables to the tmp tables.
> Thanks a lot for your help and patience...
>
> "Andreas Mavrogenis" wrote:
> > Geia,
> >
> > Did you droped your indexes ?
> > Andreas
> >
> > "Alexander Simeonidis" wrote:
> >
> > > Geia Andrea :) kai efcharisto!
> > > I am afraid the problem is not that simple. I did as you said and checked my
> > > three
> > > stored procedures, none had anything in common on line #50.
> > > One had a print cmd,
> > > the other an exec cmd and the third
> > > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > > table etc.. So I am still looking for pointers of how to take my
> > > troubleshooting further.
> > > Thank you for your answer...
> > >
> > > "Andreas Mavrogenis" wrote:
> > >
> > > > Alexandre geia ,
> > > >
> > > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > > your code regarding this line and try to find any loops or any other issue.
> > > >
> > > > Also you can delete and create your indexes
> > > >
> > > > Let me know if you need anything else
> > > > Andreas
> > > >
> > > >
> > > > "Alexander Simeonidis" wrote:
> > > >
> > > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > > and 3605. I am trying to follow the steps of this article in order to
> > > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > > article:
> > > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > > --
> > > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > > >
> > > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > > verifying cycle
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > Deadlock encountered ... Printing deadlock information
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > > >
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > > import_ssnc_dtl
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > > import_pdc_sweden
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > > was found.
> > > > > 2005-03-30 11:29:16.19 spid4 --
> > > > >
> > > > > 2005-03-30 11:29:21.19 spid4 --
> > > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > > >
> > > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > > verifying cycle
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > Deadlock encountered ... Printing deadlock information
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > > import_ssnc_dtl
> > > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > > import_pdc_sweden
> > > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > > was found.
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > --
> > > > >
> > > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > > further... So this is as far as I get. Could someone please give me some
> > > > > hints and pointers of what I need to do next'
> > > > >
> > > > > Thanks on forehand!!
> > > > >
> > > > >
> > > > > --
> > > > > Alex
> > > > > --
> > > > > Computer Science Student|||See that article also,
http://support.microsoft.com/default.aspx?scid=kb;en-us;821537
CU
Andreas
"Andreas Mavrogenis" wrote:
> Geia,
> Did you droped your indexes ?
> Andreas
> "Alexander Simeonidis" wrote:
> > Geia Andrea :) kai efcharisto!
> > I am afraid the problem is not that simple. I did as you said and checked my
> > three
> > stored procedures, none had anything in common on line #50.
> > One had a print cmd,
> > the other an exec cmd and the third
> > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > table etc.. So I am still looking for pointers of how to take my
> > troubleshooting further.
> > Thank you for your answer...
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Alexandre geia ,
> > >
> > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > your code regarding this line and try to find any loops or any other issue.
> > >
> > > Also you can delete and create your indexes
> > >
> > > Let me know if you need anything else
> > > Andreas
> > >
> > >
> > > "Alexander Simeonidis" wrote:
> > >
> > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > and 3605. I am trying to follow the steps of this article in order to
> > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > article:
> > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > --
> > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:16.19 spid4 --
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 --
> > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:21.19 spid4
> > > > --
> > > >
> > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > further... So this is as far as I get. Could someone please give me some
> > > > hints and pointers of what I need to do next'
> > > >
> > > > Thanks on forehand!!
> > > >
> > > >
> > > > --
> > > > Alex
> > > > --
> > > > Computer Science Student