What is the difference between using (TABLOCKX) and both (TABLOCKX, HOLDLOCK) hints? It seems table is exclusively locked with one TABLOCKX until transaction committed, what benefits of (TABLOCKX, HOLDLOCK)?

For example,

BEGIN TRANSACTION SELECT TOP 1 * FROM Foo WITH (TABLOCKX)...
vs
BEGIN TRANSACTION SELECT TOP 1 * FROM Foo WITH (TABLOCKX, HOLDLOCK)...

CodePudding user response：

TABLOCKX changes the record-scope and type of locks taken against the table, elevating all of your locks to table-wide and making them exclusive (i.e., no sharing with other sessions).

HOLDLOCK on the other hand, elevates the current isolation level to Serializable, effectively changing the time-scope of the lock from "as long as I am using this table in this transaction" to "until the end of this transaction, even if I am no longer using it".

So the difference between (TABLOCKX) and (TABLOCKX, HOLDLOCK) is that the table-wide exclusive locks from TABLOCKX are now held until the end of the transaction, even if your transaction is no longer using the table. Without the HOLDLOCK these locks would only be held within the transaction for as long as you are actually using the table.

Note that TABLOCK(X) alone will not hold the lock past its use, as indicated by this quote from the doc:

TABLOCK Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.

TABLOCKX is just TABLOCK with exclusive locks:

TABLOCKX Specifies that an exclusive lock is taken on the table.

Apparently, what I wrote above is incorrect. According to @DavidBrowne-Microsoft, "X locks and U locks (at least when taken because of lock hints) are always held until the end of the transaction.". This is confusing because the doc page on Hints never explicitly says (or denies) this (though there may be some other MS doc page that does).

So effectively (TABLOCKX) and (TABLOCKX,HOLDLOCK) do the same thing. I assume that some folks do this because, like me, they were confused by the doc on Hints.