Parallelism Deadlock Trace Intepretation in SQL Server 2000

I've asked this before but in a differnet context and never got a solid answer.

Could someone interpret this for me.  I'm getting some deadlocks that I'm trying to resolve and need help trying to examine where to look first..and how to solve them second. The deadlocks show up under heavy heavy traffic with hundreds of queries originating from a ASP.NET web application.

Below is my Trace 1024/3605 Log

2006-04-13 09:47:43.39       spid4      Node:1
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      -- next branch --
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      Waiting for parallel threads to enlist.
2006-04-13 09:47:43.39       spid4      Node:5
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 3, EC = 0x6cec6098, SPID: 67, ECID: 4, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 2, EC = 0x6c40c098, SPID: 67, ECID: 2, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 1, EC = 0x6bf8c098, SPID: 67, ECID: 1, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 0, EC = 0x68072098, SPID: 67, ECID: 3, Blocking
2006-04-13 09:47:43.39       spid4      Producer List::
2006-04-13 09:47:43.39       spid4      Consumer List::
2006-04-13 09:47:43.39       spid4      Coordinator: EC = 0x4a23b508, SPID: 67, ECID: 0, Not Blocking
2006-04-13 09:47:43.39       spid4      Port: 0x19db0100  Xid Slot: -1, EC: 0x4a23b508, ECID: 0 (Coordinator), Exchange
2006-04-13 09:47:43.39       spid4      Node:1
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      -- next branch --
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      Waiting for parallel threads to enlist.
2006-04-13 09:47:43.39       spid4      Node:2
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 3, EC = 0x6cec6098, SPID: 67, ECID: 4, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 2, EC = 0x6c40c098, SPID: 67, ECID: 2, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 1, EC = 0x6bf8c098, SPID: 67, ECID: 1, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 0, EC = 0x68072098, SPID: 67, ECID: 3, Blocking
2006-04-13 09:47:43.39       spid4      Producer List::
2006-04-13 09:47:43.39       spid4      Consumer List::
2006-04-13 09:47:43.39       spid4      Coordinator: EC = 0x4a23b508, SPID: 67, ECID: 0, Not Blocking
2006-04-13 09:47:43.39       spid4      Input Buf: Language Event: EXEC Get_Next_Transcription '04/13/2006 09:47:38 AM'
2006-04-13 09:47:43.39       spid4      SPID: 67 ECID: 0 Statement Type: UPDATE Line #: 17
2006-04-13 09:47:43.39       spid4      Port: 0x19db0100  Xid Slot: -1, EC: 0x4a23b508, ECID: 0 (Coordinator), Exchange
2006-04-13 09:47:43.39       spid4      Node:1
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      Wait-for graph
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      ...
2006-04-13 09:47:43.39       spid4      ResType:PageSupp Stype:'OR' SPID:67 ECID:3 Ec:(0x68072098) Value:0x68072098 Cos
2006-04-13 09:47:43.39       spid4      Victim Resource Owner:
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 3, EC = 0x6cec6098, SPID: 67, ECID: 4, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 2, EC = 0x6c40c098, SPID: 67, ECID: 2, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 1, EC = 0x6bf8c098, SPID: 67, ECID: 1, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 0, EC = 0x68072098, SPID: 67, ECID: 3, Blocking
2006-04-13 09:47:43.39       spid4      Producer List::
2006-04-13 09:47:43.39       spid4      Consumer List::
2006-04-13 09:47:43.39       spid4      Coordinator: EC = 0x4a23b508, SPID: 67, ECID: 0, Not Blocking
2006-04-13 09:47:43.39       spid4      Port: 0x19db0100  Xid Slot: -1, EC: 0x4a23b508, ECID: 0 (Coordinator), Exchange
2006-04-13 09:47:43.39       spid4      Node:1
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      -- next branch --
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      Waiting for parallel threads to enlist.
2006-04-13 09:47:43.39       spid4      Node:8
2006-04-13 09:47:43.39       spid4      
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 3, EC = 0x6cec6098, SPID: 67, ECID: 4, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 2, EC = 0x6c40c098, SPID: 67, ECID: 2, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 1, EC = 0x6bf8c098, SPID: 67, ECID: 1, Blocking
2006-04-13 09:47:43.39       spid4      Producer: Xid Slot: 0, EC = 0x68072098, SPID: 67, ECID: 3, Blocking
2006-04-13 09:47:43.39       spid4      Producer List::
2006-04-13 09:47:43.39       spid4      Consumer List::
2006-04-13 09:47:43.39       spid4      Coordinator: EC = 0x4a23b508, SPID: 67, ECID: 0, Not Blocking
2006-04-13 09:47:43.39       spid4      Port: 0x19db0100  Xid Slot: -1, EC: 0x4a23b508, ECID: 0 (Coordinator), Exchange



Below is my SQL profiler log from the same time period (had to change some data to blah to protect the innocent  :)  )

RowNumber      EventClass      TextData      DatabaseID      TransactionID      HostName      SPID      StartTime      Severity      EventSubClass      ObjectID      IndexID      IntegerData      Mode
1686807      13      "SELECT     *
FROM         TranscriptionistsV2
WHERE     (CONVERT(binary, Login) = CONVERT(binary, 'blah')) AND (Login = 'blah') AND (CONVERT(binary, Password) = CONVERT(binary, 'Zblah')) AND (password = 'Zblah') and isactive = 1"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686808      40      "SELECT     *
FROM         TranscriptionistsV2
WHERE     (CONVERT(binary, Login) = CONVERT(binary, 'blah')) AND (Login = 'blah') AND (CONVERT(binary, Password) = CONVERT(binary, 'Zblah')) AND (password = 'Zblah') and isactive = 1"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686809      41      "SELECT     *
FROM         TranscriptionistsV2
WHERE     (CONVERT(binary, Login) = CONVERT(binary, 'blah')) AND (Login = 'blah') AND (CONVERT(binary, Password) = CONVERT(binary, 'Zblah')) AND (password = 'Zblah') and isactive = 1"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                              1      
1686810      12      "SELECT     *
FROM         TranscriptionistsV2
WHERE     (CONVERT(binary, Login) = CONVERT(binary, 'blah')) AND (Login = 'blah') AND (CONVERT(binary, Password) = CONVERT(binary, 'Zblah')) AND (password = 'Zblah') and isactive = 1"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686811      13      "Update TranscriptionistsV2
Set BrowserInfo = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; {BA548D9A-0D2B-4CBF-A74A-A1FDDFF723E5}; iebar; .NET CLR 1.1.4322)'
WHERE TranscriptionID = 6184"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686812      40      "Update TranscriptionistsV2
Set BrowserInfo = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; {BA548D9A-0D2B-4CBF-A74A-A1FDDFF723E5}; iebar; .NET CLR 1.1.4322)'
WHERE TranscriptionID = 6184"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686813      50            7      7936775      10.91.130.186      67      4/13/2006 9:47:43 AM            0                        
1686814      50            7      7936775      10.91.130.186      67      4/13/2006 9:47:43 AM            1                        
1686815      50            7      7936776      10.91.130.186      67      4/13/2006 9:47:43 AM            0                        
1686816      50            7      7936776      10.91.130.186      67      4/13/2006 9:47:43 AM            1                        
1686817      41      "Update TranscriptionistsV2
Set BrowserInfo = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; {BA548D9A-0D2B-4CBF-A74A-A1FDDFF723E5}; iebar; .NET CLR 1.1.4322)'
WHERE TranscriptionID = 6184"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                              1      
1686818      12      "Update TranscriptionistsV2
Set BrowserInfo = 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; {BA548D9A-0D2B-4CBF-A74A-A1FDDFF723E5}; iebar; .NET CLR 1.1.4322)'
WHERE TranscriptionID = 6184"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686819      13      "SELECT Count(IVR_ID) as TransDayTotal FROM IVRTranscriptions
where EmployeeID = 6184 AND DateEntered >= '04/13/2006' and firstname <> 'CheckData'"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686820      40      "SELECT Count(IVR_ID) as TransDayTotal FROM IVRTranscriptions
where EmployeeID = 6184 AND DateEntered >= '04/13/2006' and firstname <> 'CheckData'"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686821      41      "SELECT Count(IVR_ID) as TransDayTotal FROM IVRTranscriptions
where EmployeeID = 6184 AND DateEntered >= '04/13/2006' and firstname <> 'CheckData'"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                              1      
1686822      12      "SELECT Count(IVR_ID) as TransDayTotal FROM IVRTranscriptions
where EmployeeID = 6184 AND DateEntered >= '04/13/2006' and firstname <> 'CheckData'"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686823      13      EXEC Get_Next_Transcription '04/13/2006 09:47:38 AM','6184'      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686824      40      EXEC Get_Next_Transcription '04/13/2006 09:47:38 AM','6184'      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686825      44      "-- Get_Next_Transcription
SET NOCOUNT ON

"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                  1915153868                  
1686826      45      "-- Get_Next_Transcription
SET NOCOUNT ON

"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                  1915153868            0      
1686827      44      "-- Get_Next_Transcription
SET @myid = NEWID()

"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                  1915153868                  
1686828      45      "-- Get_Next_Transcription
SET @myid = NEWID()

"      7            10.91.130.186      67      4/13/2006 9:47:43 AM                  1915153868            1      
1686829      44      <Long Text>      7            10.91.130.186      67      4/13/2006 9:47:43 AM                  1915153868                  
1686830      50            7      7936796      10.91.130.186      67      4/13/2006 9:47:43 AM            0                        
1686831      59      Parallel query worker thread was involved in a deadlock      0                  4      4/13/2006 9:47:43 AM            102                  24763      
1686832      59      Parallel query worker thread was involved in a deadlock      0                  4      4/13/2006 9:47:43 AM            102                  24763      
1686833      59      Parallel query worker thread was involved in a deadlock      0                  4      4/13/2006 9:47:43 AM            102                  24763      
1686834      59      Parallel query worker thread was involved in a deadlock      0                  4      4/13/2006 9:47:43 AM            102                  24763      
1686835      50            2      7936800      10.91.130.186      67      4/13/2006 9:47:43 AM            0                        
1686836      33      Error: 1205, Severity: 13, State: 2      7            10.91.130.186      67      4/13/2006 9:47:43 AM      13                              
1686837      50            7      7936800      10.91.130.186      67      4/13/2006 9:47:43 AM            1                        
1686838      50            7      7936796      10.91.130.186      67      4/13/2006 9:47:43 AM            2                        
1686839      12      EXEC Get_Next_Transcription '04/13/2006 09:47:38 AM','6184'      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686840      13      SET TRANSACTION ISOLATION LEVEL READ COMMITTED      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686841      40      SET TRANSACTION ISOLATION LEVEL READ COMMITTED      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
1686842      41      SET TRANSACTION ISOLATION LEVEL READ COMMITTED      7            10.91.130.186      67      4/13/2006 9:47:43 AM                              0      
1686843      12      SET TRANSACTION ISOLATION LEVEL READ COMMITTED      7            10.91.130.186      67      4/13/2006 9:47:43 AM                                    
                                                                              
LVL 2
Matt GrofskyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

imran_fastCommented:
Hi,

select * from master.dbo.sysprocesses will give you information about all your processes.
sp_who and sp_lock is also benificial.
 
Try modifying your database by creating indexes and updating statistics. run Database tuning wizard for this purpose.

Try changing all your reads with hint nolock this will read all the uncomitted transaction and the process will fetch the data witout waiting for the updates to be commited.

select columnname from dbo.yourtablename  with (nolock)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matt GrofskyAuthor Commented:
imran

I'll see how this goes, index tuning wizard gave me a prediction of 73% improvement in performance.  I'm going to run this then take a couple of my heavy read queries that dont rely on committed updates to much and see how it goes.  
0
imran_fastCommented:
Ok !!
0
Matt GrofskyAuthor Commented:
imran,

Index tuning wizard helped out quit a bit, I dont see the locking that I saw earlier before running it.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.