Exclusive locks prevent access to a resource by concurrent transactions
Just a small correction is needed:
Exclusive locks prevent write access to a resource by concurrent transactions
vk-kirov (2/18/2010)
I am puzzled with the fourth answer:Data modification statements typically request both shared locks and exclusive locks
The word 'typically' is so vague. For example, typically I work with databases with the READ_COMMITTED_SNAPSHOT option turned on. Data modification statements typically don't request shared locks in this case. So I didn't select the fourth answer.
I disagree. The majority of databases don't run under one of the row-versioning isolation levels, so use of the word 'typically' is justified.
The default isolation level in SQL Server is, and has always been, READ COMMITTED.
At that level of isolation, shared locks (or maybe update locks if requested) are taken by the read cursor, and escalated to exclusive locks by the write cursor if the row qualifies for modification.
I understand that you don't see shared locks very often in your environment (though you must have used WITH READCOMMITTEDLOCK at some stage!) but you should still be aware of the default behaviour.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi