• 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