Post reply

Table Hints question

  • November 30, 2011 at 5:15 pm

    #247722

    After going back and re-reading available info http://msdn.microsoft.com/en-us/library/ms187373.aspx still not sure on the best case usage of

    1. UPDLOCK [what happens when used in "insert table1 select * from table2 with (updlock)" ]

    2. TABLOCK vs. TABLOCKX

    3. XLOCK

    Thanks.

  • December 1, 2011 at 5:07 am

    #1415171

    Lexa (11/30/2011)

    After going back and re-reading available info http://msdn.microsoft.com/en-us/library/ms187373.aspx still not sure on the best case usage of

    1. UPDLOCK [what happens when used in "insert table1 select * from table2 with (updlock)" ]

    2. TABLOCK vs. TABLOCKX

    3. XLOCK

    Thanks.

    In general, don't use hints. They are there as a last resort way to take control away from SQL Server because you're in some odd circumstance.

    1) Usually, I've used update locks when I'm reading from a table that I also intend to update later in the query. This takes out locks in such a way that if another process is also reading from the table, it is unable to get a lock and must wait for mine to clear, thus avoiding deadlock situations.

    2) Shared lock taken on the whole table vs. Exclusive lock taken on the whole table

    3) It means that all locks taken are exclusive locks. So a row lock is instead an exclusive row lock, etc.

    Are you studying or trying to solve a specific problem?

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 2, 2011 at 1:52 pm

    #1416178

    Grant Fritchey (12/1/2011)

    Lexa (11/30/2011)

    After going back and re-reading available info http://msdn.microsoft.com/en-us/library/ms187373.aspx still not sure on the best case usage of

    1. UPDLOCK [what happens when used in "insert table1 select * from table2 with (updlock)" ]

    2. TABLOCK vs. TABLOCKX

    3. XLOCK

    Thanks.

    In general, don't use hints. They are there as a last resort way to take control away from SQL Server because you're in some odd circumstance.

    1) Usually, I've used update locks when I'm reading from a table that I also intend to update later in the query. This takes out locks in such a way that if another process is also reading from the table, it is unable to get a lock and must wait for mine to clear, thus avoiding deadlock situations.

    2) Shared lock taken on the whole table vs. Exclusive lock taken on the whole table

    3) It means that all locks taken are exclusive locks. So a row lock is instead an exclusive row lock, etc.

    Are you studying or trying to solve a specific problem?

    Thanks. I'm trying to troubleshoot some code written by prev. DBA but in general I try not to use hints.

Viewing 3 posts - 1 through 2 (of 2 total)

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