Hello, Looking into why a given process has a tendency to deadlock, where I found code that made me wonder. Would the following cause any problems. Basically since the table that the called stored procedure is populated in the calling stored procedure would this create a problem. Note, I did not write the actual code and the example leave a lot out.
-- from first stored procedure
DECLARE @BatchID int, @sp varchar(50) = 'usp_whatever';
-- table populated
insert into Eval.dbo.EvalBatchHeader (
BatchDate, PlanID, Client , StatusList,
Operator, Workstation, IsLive)
values (
GETDATE(), 7, 'Client1', 'Status,List',
'Me', 'MyWS', 1);
--
SET @BatchID = @@IDENTITY;
--
EXECUTE @sp @BatchID;
GO
-- called stored procedure
CREATE PROCEDURE usp_whatever (@BatchID INT) AS
DECLARE @PlanID int, @Client varchar(10);
-- table called
SELECT @PlanID = PlanID, @Client = Client
FROM Eval.dbo.EvalBatchHeader
WHERE BatchID = @BatchID;
GO
It's more to do with what locking the INSERT and SELECT have to do.
Is BatchID the first (or only) clustering key on Eval.dbo.EvalBatchHeader?
If not, review index stats -- usage stats, missing stats, operations stats -- on that table and its indexes and see if it should be the clus key. SELECTing and UPDATEing by the clus key will reduce locking on the table and its indexes and thus reduce deadlocking.
Seems to me that the insert is trivial, and any lock would be momentary, and the SELECT is trivial too (even if there was no index it should be fine, shouldn't it?)
I don't write stuff like this, but I am definitely curious as to why this would lead to deadlocks as it looks fine to me! hence if I knew a bit more about why this causes a problem I might well be able to avoid that trap in similar situations that I fall into
Insert ROW into Table
EXEC SProc
Re-read the ROW from the Table
or is there more complexity that might be the reason for the Deadlock? Some LOCKING HINTS or other tables being updated such that Chicken-and-Egg might apply?
If no index, SQL will be forced to scan the entire table. If an exclusive lock is being held on any row, such as for an INSERT or UPDATE, that could cause a deadlock.
Again, indexing is the problem, one way or another.
CREATE NONCLUSTERED INDEX IX_EvalBatchItem_BatchID ON dbo.EvalBatchItem (BatchID ASC)[/code]
Currently working on getting the stats between other tasks.
Since you have both a clustered and a non-clustered index on BatchID, you'd have to look at the query plan to see which SQL is using. Presumably for an UPDATE it would be the clustered index, but it's best to verify it. Coming in thru a non-clus index for updates considerably increases chances for deadlocks.
Not sure I have understood - do you mean understanding how a 3rd Party Application's Database works and is configured / optimised?
If so, and if you are not already using it, it would be worth firing up Brent Ozar's "sp_Blitz" and seeing what warnings it generates. Something I wish I had done was to log its output to a table, so I could see, over time, what new entries were being created, what alerts went away, and so on. Brent does say to do that, but I didn't have time originally but, with benefit of hindsight, I wish I had done that ... There is a lot that is not relevant / urgent, and running it again means all that same stuff comes up, whereas if it was logged I could more easily say "What is new since last time" ...
[quote="Kristen, post:10, topic:5171"]
Not sure I have understood - do you mean understanding how a 3rd Party Application's Database works and is configured / optimised?
[/quote]Nope.
The third party software is Idera diagnostic manager. It gives information about deadlocks and I need to work at reading the information correctly.
I have recently downloaded Brent Ozar's code but have not had time to look into it.
I got a lot of "stuff" in the bundle I received, but I think it would be fine to just create sp_Blitz in an Admin / Sandpit database and run it, and cast your eye over the results cherry-picking anything that jumps off the page!! I picked up several things that I would not have believed were wrong on my server / database, so it earned its keep for me right-there-and-then
Scott, Without knowing the table schema (keys and indexes) you cannot say that "Indexing is the problem". Additionally, the locking situation you mention may or may not cause a deadlock although it will certainly cause blocking. Even, with an index, SQL may in fact scan the table anyway depending on the index columns, existing statistics, etc.
Just looking at the bare bones code djj55 posted it is highly unlikely to Deadlock. There must be a lot more to the situation (e.g., additional queries, multiple threads of this query, other tables, triggers,...) than djj55 provided.
No, I do feel comfortable saying that indexing is the problem in this case. People underestimate the importance of the best clustered index on a table. Yes, you can make any table deadlock with bad enough code, but updating based on a unique clus key reduces the chance as much as possible.
It may or may not, but, as I said:
Coming in thru a non-clus index for updates considerably increases chances for deadlocks.
And that is 100% true, regardless of the other details on the table.
