Row cannot be located for updating sql server

An exclusive lock can be imposed to a page or row only if there is no other shared or exclusive lock imposed already on the target.This practically means that only one exclusive lock can be imposed to a page or row, and once imposed no other lock can be imposed on locked resources Shared lock (S) – this lock type, when imposed, will reserve a page or row to be available only for reading, which means that any other transaction will be prevented to modify the locked record as long as the lock is active.

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.

While objects are locked, SQL Server will prevent other transactions from making any change of data stored in objects affected by the imposed lock.

Once the lock is released by committing the changes or by rolling back changes to initial state, other transactions will be allowed to make required data changes.

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.

Leave a Reply