|
What are the different types of lock modes in SQL Server 2000?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.What are the different types of lock modes in SQL Server 2000? 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.
|
