1

We have a table on which we have implemented a queuing system using locks SELCT TOP 1 ... (WITH (UPDLOCK, READPAST)). It's been working very well until recently when we added a column to another table that has a FK relationship with the first table. I must add that in the query that uses locks, we do a join on this second table.

Now after adding the column, we seem to be getting very frequent locks and timeouts when the query is run.

Is it possible this is related to the new column? What can be done to resolve?

We are using Telerik ORM to run the stored procedure.

user2927726
  • 43
  • 4

1 Answers1

1

If you're joining on another table you'll probably want the table hints on that one too. Are there other transactions hitting that other table? If so then it obviously increases the chance of a deadlock.

For implementing a queue in a SQL Server table I've found this answer a good reference, which suggests WITH (ROWLOCK, READPAST, UPDLOCK). It's possible that you're getting lock escalation beyond just ROW, which could cause the issues you're seeing. Try adding that hint and see if it resolves things. Also ensure you have ROW locking only on other queries hitting that table or the referenced table.

Community
  • 1
  • 1
Rory
  • 40,559
  • 52
  • 175
  • 261