SQL Server Interview Questions and Answers
<< Previous Question Next Question >>
 Question: 285 Page Views: 

Discuss how Exclusive Locks are not compatible with other locks.



Posted By: Avi Date: 1 October 2010 11:25:07 AM
 Answer:

Exclusive Locks are not compatible with other lock types such as Shared Locks and Update Locks. Example below explains the concept:- We are having 4 processes such as process 1 (P1) is Select Process,Process 2(P2) is a Select process,Process 3 (P3) is an update process and process 4 (P4) is again a select process.All these processes tries to lock the same page of the same table. P1 sets the shared Lock on the page as there is no other lock on this page. P2 also sets the Shared lock on the page as Shared locks are compatible with the shared locks. Now in case of P3 which is required to modify the data wabts to set Exclusive lock on it but fails . This is because P1 and P2 are still running so it has to wait. Now P4 process too cannot set Shared lock till the processes running before it comes to an end. So first processes P1 and P2 will be finished ,then P3 will be set to Exclusive lock which then will modify the data and after it completes its task P4 will apply shared lock and will complete its task.


Posted By: avibtech

Date: 1 October 2010 11:25:07 AM
Post a better Answer if you have
 
(Will show your Gravatar icon)  
  Country flag

Loading
Enter the text as shown in the image 5k2d7z
Related Questions
SQL Server : What is the purpose of the locks in SQL Server?

What is the purpose of the locks in SQL Server?

Category: SQL Server Date: 10/1/2010 11:12:07 AM
SQL Server : Usually clustered and non-clustered indexes are discussed. It can be further configured into three categories. Which are those?

Usually clustered and non-clustered indexes are discussed. It can be further configured into three c....

The three ways other than clustered and non-clustered it can be configured into Composite Index and ....
Category: SQL Server Date: 10/1/2010 11:11:07 AM
SQL Server : There are several advantages of creating and index , but can you state some drawbacks of creating an index?

There are several advantages of creating and index , but can you state some drawbacks of creating an....

There are major two drawbacks of using indexes. 1.First one is the disk space. Index uses much spac....
Category: SQL Server Date: 10/1/2010 11:10:07 AM
SQL Server : What is the purpose of creating an index?

What is the purpose of creating an index?

The indexes in SQL server are similar to the indexes of the books.When no index is used for searchin....
Category: SQL Server Date: 10/1/2010 11:09:07 AM
SQL Server : What is an index?

What is an index?

Index is a way of finding out data quickly based on the values in the columns. Indexes are created o....
Category: SQL Server Date: 10/1/2010 11:08:07 AM
 
Signup for our weekly email newsletter
email newsletter
Questions Categories