SQL Server Locks Interview Question:

What are the different types of lock modes in SQL Server 2000?

Submitted by: Administrator

Lock modes in SQL Server 2000:

Shared: Used for operations that read data. When shared lock is applied concurrent transactions can read data/resource but not modify it. This lock is released as soon as the transaction finishes.
Update: These locks are used when there is a need to update a row or page and later promote the update page lock to an exclusive lock before making the actual changes. Update locks are used to prevent deadlocks.
Exclusive: Used for data modification operations. No other transaction can read or modify data while Exclusive lock has been acquired.
Intent: These are used when SQL Server wants to acquire a shared or exclusive lock on some resource lower down in the hierarchy.
Schema: These are used when an operation dependent on the schema of a table is executing.
Bulk update: These are used during bulk copying of data into tables provided either TABLOCK hint is mentioned or table lock on bulk upload table option is set.
Key range: These are used by SQL Server to prevent phantom insertions/deletions into a set of records which are being accessed by a transaction.
Submitted by: Administrator