• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

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                                    
                                                                              
0
Matt Grofsky
Asked:
Matt Grofsky
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now