Deadlock still happens even relevant hint added

1

In order to solve the deadlock that comes from updating same table from 2 procedures, I simulated deadlock by using a little bit modified code from this 2 procedures.
In window 1 i ran this code (to 'update' code added WHILE 1=1 to create infinite loop and create deadlock):
WHILE 1=1
BEGIN
UPDATE top (10) pl
SET ModifiedDate = getdate(),
LogDate = getdate(),
LogRTypeID = 321
FROM dbo.TableA AS pac
INNER JOIN dbo.TableB AS pl WITH (READPAST) ON pl.PID = 5344
WHERE pac.AccessDate > '2023-02-01'
AND pl.LID =123899
END
GO

And at second window ran this code:
SET NOCOUNT ON
Use Scratch
GO
WHILE 1=1
UPDATE pl
SET ModifiedDate = getdate(),
LogDate = getdate(),
LogRTypeID= ISNULL(1, 323)
FROM dbo.TableB AS pl
WHERE PID = 123
GO

Deadlock happened on primary key (clustered index - PK_TableB_LID) and Non clustered index IX_TableB_PID_LogDate_LINDate (key locks)
I tried to add hint UPDLOCK , also tried NOLOCK at second query, but it didnt help...

2

If I understand it correcly you have key locks on indexes, so it's not that you have table locks or something simular, key is the smallest part of the index that can be locked as far as I know (I'm not an expert). If this is happening and is causing a lot of trouble, why don't you capture the error statement, wait for x number of seconds and try again, something like this:

BEGIN CATCH
  IF ERROR_NUMBER() = 1205 /* deadlock */
    BEGIN
   WAITFOR DELAY '00:00:02'  /* wait 2 seconds */
    END      
 END CATCH;
3

Thank you.
But it still for me not clear why use of hints doesnt help.

4

You should avoid using hints as the sql server knows most of the times the best option. You should only use hints as you are certain that it helps an issue.

In your first query tableA and table B are not joined. I'm pretty sure it is your testcase. You can join with

INNER JOIN dbo.TableB AS pl ON pl.PID=pac.ID AND pl.PID=5344 and maybe it helps. If it doesn't please provide a script with sample data so we can test it ourself instead of guessing.

But you cannot avoid a deadlock. Sometimes it happend and error-handling is needed if a deadlock is a problem to your business.

1 Like
5

No Lock is for selects, no update, insert, delete. It's impossible to update without issuing locks

1 Like