update statement

  • I would like to find out if I run 4 update statements on a production server, will that lock the table, and affect users of the application.

    It may take 20 seconds to run.

    What effect could the front end users notice?

    Thanks

  • Maybe. Depends how many rows will be affected, how many rows are in the table, how much concurrent access there is (and hence how much lock memory is available).

    The updates could lock just the rows affected, they could lock the pages affected or they could lock the entire table.

    Users could have their queries wait until the updates are complete, maybe timeout if they wait too long.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sqlfriends (2/21/2012)


    I would like to find out if I run 4 update statements on a production server, will that lock the table, and affect users of the application.

    It may take 20 seconds to run.

    What effect could the front end users notice?

    What is being locked?... row?... page?... table?

    What are other users looking at?... same rows?... other rows?

    Impossible to tell not knowing your environment.

    EDIT: Once again Gail got it first 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Thanks all.

  • GilaMonster (2/21/2012)


    Maybe. Depends how many rows will be affected, how many rows are in the table, how much concurrent access there is (and hence how much lock memory is available).

    The updates could lock just the rows affected, they could lock the pages affected or they could lock This is an excellen[/url]t[/url] s[/url]i[/url]t[/url]e I love[/url] it

    the entire table.

    Users could have their queries wait until the updates are complete, maybe timeout if they wait too long.

    Many thanks for your tips. They helped me too! 🙂

    Elizabeth

  • I'm confused... What happened here?

    Jared
    CE - Microsoft

  • If there are concerns over concurrency and there's no reason transactionally to update the rows as a single batch I would look at writing a simple loop to update the rows individually or as smaller batches.

  • An UPDATE statement will always cause some type of lock(s) on the table. SQL must do locks to insure the integrity of the UPDATEs.

    Depending on the number of rows affected, and/or some settings in SQL, it might lock:

    just the row(s) affected; or

    a key value/range(s); or

    an entire (8K) page(s); or

    in extreme cases, the entire table.

    Unless you enclose the UPDATEs in an explicit TRANSACTION(s), each will run independently of the others. That is, as soon as each UPDATE finishes, its locks will be freed.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

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