Post reply

Insert - Row or table level locking

  • April 24, 2016 at 10:09 am

    #325876

    Hi,

    Please let me know adding a row using insert statement is row level or table level locking

    Regards,

    Nikhil Desai

  • April 24, 2016 at 10:24 am

    #1874133

    i believe locking on insert will depend on the PK of your table, and clustered indexes, if any., and how many rows you are inserting.

    if you have an identity column as the pk +clustered index, then rows will be appended to the end, with minimal row locking for what is being inserted.

    if you had a clustered guid for your PK, which has to insert those new semi random rows into the middle of tables might expand some rows to make room for page splits. ~5000 rows would get a lock esclation to the whole table, since it's less expensive to lock the table than to try and hold 5000 row locks.

    if you had a multi key clustered index, i think you'd see the same kind of behavior, where it depends on whether it inserts into the middle of the page structures, or adds to the end as new pages.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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