Different locks in sql server, DOT NET Programming

What are the different locks in SQL SERVER?

Depending on the transaction level there are six types of lock that can be acquired on data :-

1)Intent:

The intent lock desribes the future intention of SQL Server's lock manager to acquire locks on a specific unit of data for the  particular transaction. The SQL Server uses intent locks to queue exclusive locks, thereby assuring that these locks will be placed on the data elements in order the transactions were initiated. Intent locks come in three flavors:  intent exclusive (IX), intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

The locks indicate that the transaction will read few (but not all) resources in the table or page by placing the shared locks.

The IX locks indicate that the transaction will change some (but not all) resources in the table or page by placing exclusive locks.

The SIX locks shows that the transaction will read all resources, and change some(but not all) of them. This will be accomplished by placing the shared locks on the resources read & exclusive locks on the rows changed. The Only one SIX lock is allowed per resource at one time; so, SIX locks prevent another connections from changing any data in the resource (page or table), although they do allow reading the data in the same resource.

2)Shared:

The Shared locks (S) allow the transactions to read data with SELECT statements. The Other connections are allowed to read the data at the same time; however, no transactions are allowed to change data until the shared locks are released.

3)Update:

The Update locks (U) are acquired just prior to changing the data. If a transaction changes a row, then the update lock is escalated to an exclusive lock; otherwise, it is converted to a shared lock. Only one transaction can acquire update locks to a resource at one time. By Using update locks prevents multiple connections from having a shared lock that want to eventually change a resource using an exclusive lock. the Shared locks are compatible with the other shared locks, but are not compatible with Update locks.

4)Exclusive:

The Exclusive locks (X) completely lock the resource from any type of access including the reads. They are issued when data is being modified through INSERT,DELETE and UPDATE statements.

5)Schema:

The Schema modification locks (Sch-M) are acquired when the data definition language statements, like  CREATE TABLE,  ALTER TABLE,CREATE INDEX, and so on are being executed. Schema stability locks (Sch-S) are acquired when the store procedures are being compiled.

6)Bulk Update:

The Bulk update locks (BU) are used when performing a bulk-copy of data into a table with the TABLOCK hint. These locks improve the performance while bulk copying data into a table; however, they decrease the concurrency by effectively disabling any other connections to read or change data in the table.

Posted Date: 9/24/2012 4:18:41 AM | Location : United States







Related Discussions:- Different locks in sql server, Assignment Help, Ask Question on Different locks in sql server, Get Answer, Expert's Help, Different locks in sql server Discussions

Write discussion on Different locks in sql server
Your posts are moderated
Related Questions
Mention the name of the directory where it is essential to locate the proxy file to use a Web service. The proxy file must be kept in the /bin directory. This directory is situ

What are abstract classes? The features of the abstract class are as follows:- 1.     You can not create an object of the abstract class. 2.     The Abstract class is des

What is the use of data adapter? The Data adapter are the objects that connect one or more Command objects to a Dataset object. They give logic that would get data from the dat

1. Prepare  a grocery list   having four columns (Serial number, The name of the product, quantity and price)  for the month of April, 06. Font specifications for Title(Groce

What is three tier architecture? The 3 tier software architecture was comes into action in the 1990s to overcome the limitations of the 2 tier architecture. There are 3 laye

What is the DTD? The DTD is Document Type Definition that explains the formation of the content of an XML document. The DTD manages the data to keep in a consistent format. It

What is the use of attribute  ? This attribute works as a compatibility option. We know that ASP worked in STA model and ASP.NET works in MTA model, but what if your ASP.NET

Using the attached XML file (xml_ind.xml) create a XSLT and CSS file for the following information: select the following information for ONLY the cars that cost more than $50,00

Briefly explain how the server control validation controls work? A validation control works by evaluating the value of an input server control on the page to see whether it mee

I Need paypal and urolator pro api integrated in to aspx site Project Description: Need paypal and urolator pro api integrated in to aspx site. The is presently completed and