As you have seen there is a really strong need for Update Locks in a relational database, because otherwise it would yield to deadlock situations and decreased concurrency.I hope that you now have a better understanding of Update Locks, and how they are used in SQL Server. An unpublished study of System R also showed that this kind of deadlock was the most prominent one.System R was initially implemented without any Update Locks.Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again..No other transactions can modify the data while shared (S) locks exist on the resource.(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) Today I want to talk about a specific question that I almost get every time when I teach about Locking & Blocking in SQL Server: Why does SQL Server need to have Update Locks?
Therefore there is also the need for the Update Lock, because this specific lock is compatible with the traditional Shared Lock.Instead of acquiring an Update Lock during the 1st phase, it would be also a viable option to acquire an Exclusive Lock directly in that phase.This will also overcome the deadlock problem, because an Exclusive Lock is not compatible with another Exclusive Lock.Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.