SQL Server Locks Interview Question:

Explain different types of lock modes in SQL Server 2000?

Submitted by: Administrator

Different lock modes:

Shared (S): Mostly used for Read only operations like SELECT statements. It allows concurrent transactions to read data. No other transaction can modify the data until the lock is present. The lock is released as soon as the read is over.

Update locks (U): used to prevent dead locks. Used on resources that can be updated. Common forms of deadlocks occur when multiple sessions are reading, locking, and potentially updating resources later.

Exclusive (X): used for data modifications statements like INSERT, UPDATE or DELETE. This lock ensures multiple updates cant be made simultaneously.

Schema: Sch-M or Schema modification locks are used when an operation related to the table schema is being performed. Schema Stability or Sch-S locks are used when queries are being complied.

Bulk update locks: Used when bulk copy is being performed. BU allows processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.
Submitted by: Administrator