Post reply

Delete and update locks on table when there is no row to delete/update

  • July 3, 2019 at 1:12 pm

    #3659751

    I was looking to see how the lock will work in the delete and update commands, but I am a little confused.

    So let's say a command will be run to update some rows in a table, but no rows has meet the conditions of the command. Will it still lock the table?

    UPDATE mytable SET column1='xx' WHERE column1='yy' // but column1 is not 'yy' for any rows

    What about the delete?

     

    Best Regards,
    Ashkan

  • July 3, 2019 at 6:18 pm

    #3659902

    It would take an Intent Exclusive (IX) lock but not an exclusive lock. An actual update/delete would take an Exclusive (X) lock. You can capture what happens with extended events if you wanted to test these.

    Sue

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

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