Alternate of UPDLOCK,TABLOCK RRS feed

  • Question

  • Hi,

    Can some one help me with table hints. Somebody has used WITH(UPDLOCK,TABLOCK) in sql in one of the applications I am working for an update statement. I suspect that this is causing timeout and slowness issues in the same part of the code. Can somebody please tell me what is the alternate of the above locks so that the query execution becomes fast?

    Below is the query

    UPDATE TOP (1) [dbo].[table] SET [STATUS] =@status OUTPUT INSERTED.col1, INSERTED.col2, INSERTED.STATUS WHERE col1= (SELECT TOP(1) col1 FROM[dbo].[tables]  WITH(UPDLOCK,TABLOCKX) WHERE Col4=@col4 ORDER BY col1 ASC)

    Thursday, November 14, 2019 2:10 PM

All replies

  • It is difficult to say what is right without knowing the context. It seems unlikely, though, that the TABLOCKX hint is correct. Then again, that's not the only thing that looks wrong. I would expect a condition on STATUS in the sub-select. Then again, it may be that your attempt to anonymise the table was only half-hearted, so I may be looking at something which is inaccurate. Yeah, is that the same table in the query twice or two different tables in the query? (You have posted the latter!)

    For the query as given, there needs to be one index on (col4, col1) and one index on (col1).

    The TABLOCKX hint as such will make things run slower, but it will hamper concurrency.

    You talk about timeouts. By default, SQL Server never times out a query. However, many client API has a default timeout of 30 seconds, so they will time out.

    That's all I say for now. Please revert with more accurate information for further help.

    Erland Sommarskog, SQL Server MVP,

    Sunday, November 17, 2019 3:31 PM
  • Hi Erland,

    Thanks for the reply.  

    The same table in the query is being used twice. The first is updating the top (1) record that is supposed to be returned from the sub query. So yes, it is the same table in the query.

    For index, non clustered index is already there only on col1. Do you suggest an index on col1 and col4 combine?

    And what about the table hints? We have both UPDLOCK,TABLOCKX, that I believe makes exclusive lock(not sure) . Is that correct ?

    Thanks again

    • Edited by Hp2019 Monday, November 18, 2019 9:37 AM
    Monday, November 18, 2019 9:35 AM
  • Since the condition is the subquery is on col4, you need an index on col4 so these rows can be located swiftly. And that index should have col1 as the second index column, so the correct value of col1 can be located even faster.

    A UPDLOCK is a read-lock, but only one process at a time can hold a UPDLOCK on a resource. That is, this is a lock you take when you intend to update a row. So that hint certainly makes sense here. Except that the TABLOCKX hint makes it completely irrelevant. The TABLOCKX hint takes an exclusive lock on the table, which is pretty heavy artillery.

    But what is the right thing is difficult to say without further knowledge of what one wants to achieve with the code.

    Erland Sommarskog, SQL Server MVP,

    Monday, November 18, 2019 11:53 AM