Post reply

specifying locking hints in code

  • February 18, 2023 at 9:15 am

    #4151363

    Hi All,

    Is specifying locking hints and set lock_timeout is a good thing or bad thing? I see a lot of this kind, in the app code?

    Please share your thoughts and view points.

    We are using SQL 2017 EE.

    SET LOCK_TIMEOUT ? UPDATE TNAME WITH (ROWLOCK) SET CREATE_DATE = CREATE_DATE WHERE ROWID_OBJECT = ?

    Thanks,

    Sam

  • February 18, 2023 at 11:34 am

    #4151387

    Generally, it is not a good idea unless there is a known and well-defined problem being addressed.

    😎

    It looks to me like someone trying to be clever without really understanding what they are doing.

  • February 19, 2023 at 12:27 am

    #4151502

    vsamantha35 wrote:

    Hi All,

    Is specifying locking hints and set lock_timeout is a good thing or bad thing? I see a lot of this kind, in the app code?

    Please share your thoughts and view points.

    We are using SQL 2017 EE.

    SET LOCK_TIMEOUT ? UPDATE TNAME WITH (ROWLOCK) SET CREATE_DATE = CREATE_DATE WHERE ROWID_OBJECT = ?

    Thanks,

    Sam

    Have you determined whether or not it's actually causing an issue or not?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 19, 2023 at 4:12 am

    #4151521

    As per app team, this query is executed several times and response times are higher (3-5 secs). We checked for blocking scenarios,

    waiting for locks, what other things to be checked  which can help us know what is causing the issue? I  am not sure if the locks getting escalated. The ROWID_OBJECT  column is also nchar(50) datatype.

  • February 19, 2023 at 3:46 pm

    #4151642

    vsamantha35 wrote:

    As per app team, this query is executed several times and response times are higher (3-5 secs). We checked for blocking scenarios,

    waiting for locks, what other things to be checked  which can help us know what is causing the issue? I  am not sure if the locks getting escalated. The ROWID_OBJECT  column is also nchar(50) datatype.

    Unless more details are put forward, it is absolutely impossible to advise on this issue!

    😎

    At the first glance, the issue looks like "Death By Design"

  • February 20, 2023 at 12:47 am

    #4151726

    vsamantha35 wrote:

    As per app team, this query is executed several times and response times are higher (3-5 secs). We checked for blocking scenarios,

    waiting for locks, what other things to be checked  which can help us know what is causing the issue? I  am not sure if the locks getting escalated. The ROWID_OBJECT  column is also nchar(50) datatype.

    The next question I have is... who wrote that code?  The app team or the 3rd party?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 20, 2023 at 6:31 am

    #4151801

    3rd party.  And some of the code they have customized.

Viewing 7 posts - 1 through 6 (of 6 total)

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