What are the different types of lock modes in SQL Server 2000?
Submitted by: AdministratorLock 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
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
Read Online SQL Server Locks Job Interview Questions And Answers
Top SQL Server Locks Questions
☺ | What is a live lock? |
☺ | Do you know what guidelines should be followed to help minimize deadlocks? |
☺ | What is a dead lock? |
☺ | Explain what is lock escalation and what is its purpose? |
☺ | Explain optimistic and pessimistic concurrency? |
Top New MS SQL Server Categories
☺ | SQL Server Database Administrator Interview Questions. |
☺ | SQL Server Service Broker Interview Questions. |
☺ | SQL Server Locks Interview Questions. |
☺ | SQL Server Constraints Interview Questions. |
☺ | MS SQL Data Mining Interview Questions. |