SQL Server Performance Forum – Threads Archive

Deadlock

Hi, this is my first message on the forum.<br />We do have deadlocks happened on the system everyday all of the sudden. I run the profiler trace overnight along with a trace flag ont he server and got info in both error log and profiler but i am having a problem to explain what is going on.<br />Attached is a part from profiler. Can someone please help me to understand where the problem is?<br />I can add more info from the log if needed.<br /><br />EventClass spid eventsubclass IntData Mode TextData IndexIDObjectID<br />Lock<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />eadlock Chain 4 Lock 171125 Null-Share Deadlock Chain SPID = 58 1 2025058250 <br />Lock<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />eadlock Chain 4 Lock 171125 Intent-Share-Share Deadlock Chain SPID = 65 6 2025058250 <br />Lock<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />eadlock 58 171128 Null-Share 1 2025058250 <br />Exception 58Error: 1205, Severity: 13, State: 50<br /><br /><br />Thank you

---

Check the following…
http://support.microsoft.com/kb/832524
http://www.sql-server-performance.com/deadlocks.asp
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Thank you. Can this statement be a problem? UPDATE test set StatusID=2, [email protected] where ID in (SELECT
ID FROM test WHERE [email protected] AND [email protected] AND
[email protected])

---

Most of the dead lock happens between two spid…
Your update may cuase deadlock…it is hard to tell…depends on what else going on the system…. Why are you using the subquery… your update can be written like this… UPDATE test set StatusID=2, [email protected]
from test
WHERE [email protected]
AND [email protected]
AND [email protected] MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Dont Self-join the table during UPDATE or DELETE
Use straight forward method suggested by MU Madhivanan Failing to plan is Planning to fail

---

Exactly, that was also my question when i first saw this update.I am not an author of this query.
Here is my understanding: When update and select happened at the same time with select, the exclusive and shared locks conflict is happening. Plus another session is calling the same SP and deadlock always happenes on this update statement.
+ i found this trigger on test table: CREATE TRIGGER SetCompletedDate ON dbo.test
FOR UPDATE
AS IF UPDATE(StatusID)
BEGIN
UPDATE test
SET CompletionDate = GetDate()
FROM test
INNER JOIN Inserted ON test.ID = Inserted.ID
WHERE test.ID = Inserted.ID
AND Inserted.StatusID in (2, 3)
END
Thank you guys.

---

]]>