Updating multiple rows in sql server

While the update lock can be imposed on a record that has the shared lock, the shared lock cannot be imposed on the record that already has the update lock Intent locks (I) – this lock is a means used by a transaction to inform another transaction about its to acquire a lock.

The purpose of such lock is to ensure data modification to be executed properly by preventing another transaction to acquire a lock on the next in hierarchy object.

In practice, when a transaction wants to acquire a lock on the row, it will acquire an intent lock on a table, which is a higher hierarchy object.

When a bulk update lock is acquired, other processes will not be able to access a table during the bulk load execution.In doing so, it will acquire an intent exclusive (IX) lock on those lower hierarchy resources that should be modified.In practice, this means that once the transaction acquires a SIX lock on the table, it will acquire intent exclusive lock (IX) on the modified pages and exclusive lock (X) on the modified rows.Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect.A lock as an in-memory structure is 96 bytes in size.