none
UPDLOCK on only one table RRS feed

  • Question

  • I have query like this:

    SELECT col1, col2, ... FROM dbo.v_stock WITH (UPDLOCK)

    This way the lock is hold on all tables inside view, which columns are part of select.

    How can I put UPDLOCK only on one table inside view?
    I don't wan't to copy view code to my query to put explicitly lock on one table.

    There must be some other way?

    Wednesday, April 13, 2016 11:22 AM

Answers

  • If holding the locks on all the tables during the duration of the calculation, maybe should try optimistic concurrency instead? That is, you read the values and save the key values. Once you are read for update you include a check on this key value, and if it has changed you raise an error or silently start over.

    Wednesday, April 13, 2016 2:38 PM

All replies

  • Simple, don't use UPDLOCK and tell me reason for using it in select statement, what you want to achieve. And its is not going to lock a whole table UPDLOCK takes update locks and holds them to the end of the transaction. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, April 13, 2016 11:34 AM
  • I read stock from this view and until the end of transaction I don't allow others to change this stock since I do some calculations with it and at the end I update it.

    BEGIN TRAN
    --read stock SELECT col1, col2, ... FROM dbo.v_stock WITH (UPDLOCK)
    --some logic with this stock
    ....
    ....

    COMMIT TRAN

    UPDLOCK is very usefull hint which solves many problems...

    Wednesday, April 13, 2016 11:38 AM
  • Greetings,

    i was typing a message stating the same as Shanky_621.
    Though if you really want UPDLOCK: don't make the source a view.

    Also be aware you might lock the whole table and depending on the implementation of the view you might not lock the base-tables.

    and IF you really need that kind of isolation; Please look for SERIALIZABLE for your transaction.
    That will ensure your query is the only one reading/editing and all others (read, insert, update) will have to wait for your execution.

    but to answer your question:
    No you cannot add 'WITH (UPDLOCK)' outside of hard-coding it in the view... and then it makes sense to rather copy the view's logic.

    Sebastian


    Wednesday, April 13, 2016 11:42 AM
  • I read stock from this view and until the end of transaction I don't allow others to change this stock since I do some calculations with it and at the end I update it.

    BEGIN TRAN
    --read stock SELECT col1, col2, ... FROM dbo.v_stock WITH (UPDLOCK)
    --some logic with this stock
    ....
    ....

    COMMIT TRAN

    UPDLOCK is very usefull hint which solves many problems...

    For default isolation level when you read data it takes shared lock and when you update data it initially takes update lock and then exclusive lock. Exclusive lock is not compatible with shared so while you are reading no body can update data you are reading. So again I am not sure what is use of UPDLOCK. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Wednesday, April 13, 2016 11:48 AM
  • It is really not a good solution. For each query where I'm using view and I need to add UPDLOCK i need to create another view with hardcoded updlock on required table. Can you imagine how many copy of code would that produce over the database?

    There should be some option to tell which table would you like to add lock. Well, MS little dissapoint me with this.

    Wednesday, April 13, 2016 11:50 AM
  • I read available stock over each warehouse. Then I calculate what part of each stock i will use for some transfers.
    Then i make all transfers and at the end I update stock.

    If stock change in meanwhile, all my calculations would be wrong and transfers couldn't exists.
    So, I need to prevent updates on my warehouses for the time of my transaction(I can put also rowlock to prevent the whole table to be locked).

    UPDLOCK is very usefull with CTE also since CTE query can execute more than once inside the same implicit transaction.

    Wednesday, April 13, 2016 11:58 AM
  • If holding the locks on all the tables during the duration of the calculation, maybe should try optimistic concurrency instead? That is, you read the values and save the key values. Once you are read for update you include a check on this key value, and if it has changed you raise an error or silently start over.

    Wednesday, April 13, 2016 2:38 PM