Locking is a major part of every RDBMS and is important to know about. It is a database functionality which without a multi-user environment could not work. The main problem of locking is that in an essence it's a logical and not physical problem. This means that no amount of hardware will help you in the end. Yes you might cut execution times but this is only a virtual fix. In a heavy multi-user environment any logical problems will appear sooner or later.
Our application has been showing more timeout errors due to lock contention since upgrading from SQL Server 2000 SP3 to SP4 and SQL Server 2005. Did the locking semantics change between these versions? I haven't found any clues on the Microsoft support web site. - Nils B.
i don't think that you're problems are caused by changes in locking mechanism which are very few that i could find out about. they're probably caused more by the lack of updated statistics and good indexing.
The article states that "Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level." I don’tthink that is true. Key-range locks will be obtained while in the default read-committed isolation level too. Can be easily shown y running:
BEGIN TRANSACTION SELECT * FROM Person.Address WITH(HOLDLOCK) WHERE AddressId >= 4 AND AddressId <= 5
SELECT resource_type, request_mode, resource_description, * FROM sys.dm_tran_locks WHERE resource_type <> 'DATABASE'
I think there is some mistake here. "In the example you can't see the shared locks because they're taken for the duration of the select statement and are already released when we would select data from sys.dm_tran_locks. That is why an addition of WITH (HOLDLOCK) is needed to see the locks."
If there is optimistic transaction level as here, then there is no SHARED LOCK! It will be Sch-S lock for the time of select statement.
Here you can see Shared lock only because HOLDLOCK is added. It looks that HOLDLOCK escalate Sch_S lock to Shared lock.