spid 72 says its being blocked by spid -2 in EM
-
SQL gurus! Anyone with info that would allow me to resolve this deadlocking issue I've been encountering daily would be excellent. I have encountered a deadlock situation w/ a SQL process that runs thru a reporting tool called BRIO. The first thing is that EM reports that -2 is causing the blocking. I've never seen a -2 spid in EM! No info is even being reported for the blocking spid -2 in EM. When I check the last sql statement being excuted for the other spid (72) that is being blocked it shows a sproc that is doing this:
(spid 72)
SELECT I.ItemID, ItemName, ItemDesc, I.ItemType,
I.CategoryID, I.ReportCategoryID, I.Active,
I.UnitPrice, I.CompanyID, K.CompanyName,
R.CatDesc as ReportCategoryName, C.CatDesc as CatDesc,
I.PrimaryCategoryID, I.CouponType, I.RefID, I.BenefitType,
I.BenefitAMount, I.BarCodeID,
ShouldDisplay, I.ExtData, VoucherProviderCompanyID,
IsPerishable, OwningCompanyFlag , I.SortOrder ItemSortOrder,
C.SortOrder CatSortOrder
FROM Items I, Companies K, Categories C, Categories R
WHERE I.CompanyID = K.CompanyID
AND I.Active = 1
AND I.ItemType = 5
AND I.CategoryID = C.CategoryID
AND I.ReportCategoryID = R.CategoryID
Order BY I.CompanyID, I.PrimaryCategoryID, I.CategoryID, ItemName
(spid -2) is doing this:
Select
F.DepartureDateTime,
F.FlightNum,
F.FlightID,
T.TransactionID,
I.ItemName,
C.CatDesc,
TI.Qty,
TI.TotalAmount,
CONVERT(char(8),T.TransactionDate,1),
TI.ItemID,
T.ActionId,
T.CashAmount,
T.CreditAmount,
T.CouponAmount,
TI.DiscountType,
TI.DiscountAmount,
T.ChangeAmount,
UPPER(T.CouponCode)
From TransactionItems TI, Transactions T, Items I, Flights F, Categories C
Where TI.TransactionID = T.TransactionID
And TI.ItemID = I.ItemID
And F.FlightID = T.FlightID
And TI.AchProcDate = '01/01/1900'
AND F.DepartureDateTime < '04/29/2004'
AND T.ActionId != 3 !Pre-Flight Sale
AND C.CategoryID = I.ReportCategoryId
AND TI.CategoryId != 31
AND TI.ReportCategoryId IN (20,21,22)
AND F.CarrierCompanyId = 7
AND F.KitchenCompanyId = 2
ORDER BY F.FlightNum, F.DepartureDateTime,T.TransactionID, I.ItemName
I'm guessing that maybe using 'NOLOCK' or some hints might avoid this, but any info you think could eliminate this deadlock issue would be greatly appreciated.
-
Have you tried restarting the SQL Server? It appears you might be having "zombie" troubles ... (a process that is really dead, but locks still being held by the SQL Server?)
-
We tried using the hint WITH (NOLOCK) on a table that was getting a lot of acess and was causing blocks and it has seemed to resolve our blocking.
-
To prevent blocking, I always use the with (nolock) option in all readonly queries.
pro: why lock what you don't want to change
dis: reads uncommited data!
-
Grasshopper,
Yes, I actually have restarted the SQL Server services. It resolves the prob temporarily, but SPID -2 still seems to show up as BLOCKING from time to time again. And of course since the -2 SPID doesn't show in EM or QA, you can't even kill the process. Weird stuff!
Any other ideas Grasshopper? I'm open for suggestions.
- Dave
-
I used WITH (NOLOCK) in this query. I'll test it and see how it works. Thanks for your help.
-
I've experienced this same problem when trying update two tables - my solution was to use WITH (pagelock) hint since an update needs to lock at least the row. rowlock hint isn't compatible with some other hints such as noupdlock but pagelock is compatible.
Victor Campos, MCP/MCSD
Options Software Consultant -
I suggested using WITH (NOLOCK) for our SQL process to one of my developers and he had concern that it reads uncommited data. Because we produce a file from this process that is sent to a bank and bills based of the data. What do you think?
-
Even if you use WITh (NOLOCK) the transaction still locks the table for the update. SQL performs this in an implicit transaction whether the user likes it or not. However, WITH (NOLOCK, PAGELOCK) only locks the page in which the record resides and reduces the chances of a deadlock.
Hope this helps!
Victor Campos, MCP/MCSD
Options Software Consultant -
(NOLOCK):
Open the query analyser and open 3 connections.
First connection:
Create Table MyTest(TestField1 char(2), TestValue int, constraint [pk_mytest] primary key nonclustered (TestField1) with fillfactor=90 )
begin Transaction
Insert into MyTest(TestField1, TestValue)
Values ('AA', 1)
Insert into MyTest(TestField1, TestValue)
Values ('BB', 2)
Run this Part
Second connection (run direct - don't wait for it):
Select * from MyTest
Third connection (run direct):
Exec sp_who
You will see that the second connection is blocked by the first one.
Now rollback the first connection, and execute the second with the (nolock) option.
This time you will see the inserted data (which was not committed yet).
You won't get a blocking condition this time.
I hope this test will clear up things.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply