3

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)...
supervisor
  • 33
  • 7
  • Does this answer your question? [TABLOCK vs TABLOCKX](https://stackoverflow.com/questions/5102152/tablock-vs-tablockx) – Jayasurya Satheesh Nov 10 '21 at 13:40
  • 2
    `TABLOCKX` takes an exclusive lock on the table, preventing any other locks from being taken. Using `HOLDLOCK` causes that lock to be kept until the end of the transaction, rather than just until that table is done being used. – paneerakbari Nov 10 '21 at 13:43
  • @Jayasurya Satheesh, I have reviewed it, but it compare another hints. My question is what benefits of (TABLOCKX, HOLDLOCK) vs single (TABLOCKX) – supervisor Nov 10 '21 at 13:49

1 Answers1

5

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Thanks you for input, but my question is not compare TABLOCKX vs HOLDLOCK. I have updated the post with example to clarify question. – supervisor Nov 10 '21 at 13:57
  • @supervisor I thought my answer did address that question, but I will make it explicit ... OK, I have updated it. Does this answer your question? – RBarryYoung Nov 10 '21 at 13:58
  • I thought so initially, but TABLOCKX also hold table until end of the transaction, so my question was born. Another session can't read from the table until transaction is commited by first session. – supervisor Nov 10 '21 at 14:05
  • @supervisor The doc indicates that it does not hold the lock for TABLOCK(X) alone. I will include the relevant quote from the doc in my answer ... OK, I have updated my answer. – RBarryYoung Nov 10 '21 at 14:08
  • Sorry, but the quote about TABLOCK, not TABLOCKX. I believe they are different hints. – supervisor Nov 10 '21 at 14:13
  • @supervisor TABLOCKX is just TABLOCK+XLOCK, it doesn't have a separate description in the doc. It just says "*TABLOCKX Specifies that an exclusive lock is taken on the table.*" Exclusive locks do not have a different time-scope than shared locks. HOLDLOCK changes the time-scope, TABLOCK changes the record-scope and XLOCK changes the lock-mode, each is independent of the other. – RBarryYoung Nov 10 '21 at 14:16
  • Do you have the doc reference where described TABLOCKX = TABLOCK+XLOCK? Also please review XLOCK description, it contains more than you wrote. – supervisor Nov 10 '21 at 14:27
  • 1
    @supervisor Lets try this from another angle: Where have you read that TABLOCKX holds locks until the end of the transaction? I know of no Microsoft doc that says or implies this. – RBarryYoung Nov 10 '21 at 14:29
  • @supervisor Yes, you are right, I should not have said that "*TABLOCKX is just TABLOCK+XLOCK*", I should have said that "*TABLOCKX is just TABLOCK+Exclusive*" which is what the doc says. – RBarryYoung Nov 10 '21 at 14:32
  • @RBarryYoung, I can't provide any doc where said TABLOCKX holds locks until the end of the transaction, but I have real examples in my work. I have tested before my question and the table is locked for other sessions until end of the transaction. So, let stop this disscussion, may be some other answers will be. – supervisor Nov 10 '21 at 14:40
  • @supervisor OK, fair enough. I will try to find someone who knows the locking internals to check my answer for accuracy, – RBarryYoung Nov 10 '21 at 14:46
  • 3
    X locks and U locks (at least when taken because of lock hints) are always held until the end of the transaction. SERIALIZABLE/HOLDLOCK also triggers range locking, but that won't matter if you already have an table-level X lock. S locks and U locks are normally released ASAP unless the isolation level requires them to be held. – David Browne - Microsoft Nov 10 '21 at 14:56
  • @DavidBrowne-Microsoft Thanks, I will correct my answer. – RBarryYoung Nov 10 '21 at 14:58
  • @Charlieface `TABLOCKX` will always take an X lock which is held until the end of the transaction. `TABLOCK` on a `SELECT` will take an S lock and release it at the end of the statement. – David Browne - Microsoft Nov 10 '21 at 15:02
  • @DavidBrowne-Microsoft, if so, why many examples on Microsoft pages contains both hints, also why ssms generates both hints during scripting? – supervisor Nov 10 '21 at 16:06
  • 1
    Probably because not everyone knows that TABLOCKX will be held until the end of the transaction, so using both makes the intent super-clear. – David Browne - Microsoft Nov 10 '21 at 16:12
  • @DavidBrowne-Microsoft - in re: clarity, I would argue the opposite. If the behavior is as you say, always specifying both in the docs (as supervisor says) not only leads to these sorts of questions but also promulgates cargo cult behavior. – Ben Thul Nov 10 '21 at 16:50
  • @DavidBrowne-Microsoft The "X locks and U locks are always held" contradicts the "S locks and U locks are normally released" later in your comment. – GSerg Nov 10 '21 at 18:07
  • U locks taken while scanning during an UPDATE are released. U locks taken because of a UPDLOCK hint are held. – David Browne - Microsoft Nov 10 '21 at 19:46