Using table hint TabLock

  • Junior Galvão - MVP

    SSCertifiable

    Points: 6376

    Comments posted to this topic are about the item Using table hint TabLock

  • Lempster

    SSCoach

    Points: 15451

    All the possible answers assume that the table exists and that the @Contador variable has been declared.

  • Shayn Thomas

    SSCertifiable

    Points: 5391

    Nice question, thanks

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • ako58

    Hall of Fame

    Points: 3205

    In TabLockTable are as minimum two fields.
    Insert Into TabLockTable Values ('Test TabLock') - syntax error

  • Sean Lange

    SSC Guru

    Points: 286408

    This is just wrong on many levels. The insert doesn't "do nothing". It is however unable to complete because there is a transaction in progress. Once the transaction is resolved the insert statement will complete. The transaction in this question has muddied the waters so much that the code is no longer really testing TABLOCK. And of course the insert statement would fail for column mismatch.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Junior Galvão - MVP

    SSCertifiable

    Points: 6376

    Sean Lange - Thursday, March 21, 2019 7:23 AM

    This is just wrong on many levels. The insert doesn't "do nothing". It is however unable to complete because there is a transaction in progress. Once the transaction is resolved the insert statement will complete. The transaction in this question has muddied the waters so much that the code is no longer really testing TABLOCK. And of course the insert statement would fail for column mismatch.

    OK. I respect you comment.
    But this is simulation.

  • Sean Lange

    SSC Guru

    Points: 286408

    Junior Galvão - MVP - Thursday, March 21, 2019 9:03 AM

    Sean Lange - Thursday, March 21, 2019 7:23 AM

    This is just wrong on many levels. The insert doesn't "do nothing". It is however unable to complete because there is a transaction in progress. Once the transaction is resolved the insert statement will complete. The transaction in this question has muddied the waters so much that the code is no longer really testing TABLOCK. And of course the insert statement would fail for column mismatch.

    OK. I respect you comment.
    But this is simulation.

    I know it is simulation and transaction is there to hold the lock. But since it is in a transaction is makes the simulation inaccurate. And the closest right answer is "A message saying that the query was successfully executed and the Insert was performed.". Regardless I applaud the effort and say a huge thanks for writing a QOTD!!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Scott Coleman

    One Orange Chip

    Points: 27363

    I assumed the author intended the INSERT to be a valid statement, for example the Codigo column could be an IDENTITY column.  And that the @Contador variable was declared somewhere.  That's more assumptions than I would like to make considering how tricky these questions can get sometimes, but the possible answers did not appear to include any gotchas and that was the only way the question made any sense.

  • Junior Galvão - MVP

    SSCertifiable

    Points: 6376

    Scott Coleman - Thursday, March 21, 2019 3:50 PM

    I assumed the author intended the INSERT to be a valid statement, for example the Codigo column could be an IDENTITY column.  And that the @Contador variable was declared somewhere.  That's more assumptions than I would like to make considering how tricky these questions can get sometimes, but the possible answers did not appear to include any gotchas and that was the only way the question made any sense.

    Hi Scoot,

    Thanks for your comment!

  • t.ovod-everett

    Ten Centuries

    Points: 1247

    Scott Coleman - Thursday, March 21, 2019 3:50 PM

    I assumed the author intended the INSERT to be a valid statement, for example the Codigo column could be an IDENTITY column.  And that the @Contador variable was declared somewhere.  That's more assumptions than I would like to make considering how tricky these questions can get sometimes, but the possible answers did not appear to include any gotchas and that was the only way the question made any sense.

    I assumed that @Contador was Portuguese for @COUNTER and that it was an incomplete search and replace that lead to the variable issue, and thus that the intent of the author was for @Contador to be @COUNTER (that the author wasn't deliberately writing a trick question), and so answered according to what I presumed was the intent (and thus got the question correct, even though an actual execution of the code would have resulted in a different answer).  Still, I think these issues should have been caught during the editorial review process.

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71316

    Interesting question, thanks, Junior.
    I haven't worked with locking for quite some time, so need to dust off those memory plates...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Junior Galvão - MVP

    SSCertifiable

    Points: 6376

    Stewart "Arturius" Campbell - Sunday, March 24, 2019 11:34 PM

    Interesting question, thanks, Junior.
    I haven't worked with locking for quite some time, so need to dust off those memory plates...

    Campbell,

    Thanks for your comment.

    Regards.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply