Difference between Update and Exclusive Lock
| Update Lock | Exclusive Lock |
|---|---|
| Update locks are something of a hybrid between shared locks and exclusive locks. In order to do an update, you need to validate your WHERE clause (assuming there is one) to figure out just which rows you are going to be updating. That means that you only need a shared lock, until you actually go to make the physical update. At the time of the physical update, you will need an exclusive lock. | An exclusive lock reserves a page or row for the exclusive use of a single transaction. It is used for DML statements (INSERT, UPDATE and DELETE) that modify the resource. Once an exclusive lock is set for the page (or row), no other lock can be placed on the same resource. |
| An update lock can be placed only if no other update or exclusive lock exists. On the other hand, it can be placed on objects that already have shared locks. (In this case, the update lock acquires another shared lock on the same object.) If a transaction that modifies that object is committed, the update lock is changed to an exclusive lock if there are no other locks on the object. There can be only one update lock for an object. | An exclusive lock cannot be set if some other process holds a shared or exclusive lock on the resource - that is, there can be only one exclusive lock for a resource. Once an exclusive lock is set for the page (or row), no other lock can be placed on the same resource. |
For more on SQL Server Update and Exclusive locks refer books Microsoft SQL Server 2012 A Begineers Guide by PETKOVIC and Beginning Microsoft SQL Server 2012 Programming by Paul Atkinson, Robert Vieira