Different locks in sql server, DOT NET Programming

Assignment Help:

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.


Related Discussions:- Different locks in sql server

Simple fb integration in ms-sql and asp.net, Simple FB Integration in MS-SQ...

Simple FB Integration in MS-SQL and ASP.NET I am seeking urgent project to be done, please I am interested in someone who will present demo for retrieving personal informatio

Define common type system, What is Common Type System (CTS)? CTS is the...

What is Common Type System (CTS)? CTS is the component of CLR by which .NET Framework provides support for multiple languages because it have a type system that is common acros

Develop a iphone and android mobile game, Develop a iPhone and Android mobi...

Develop a iPhone and Android mobile game Project Description: I am seeking a developer to create a mobile game for me. I need an endless runner game with simple controls. The

Uml, What is UML? The Unified Modeling Language (UML) is a graphical la...

What is UML? The Unified Modeling Language (UML) is a graphical language for documenting, specifying, visualizing,  constructing, and  the artifacts of a software-intensive sys

Asp .net facebook app project completion, Project Description: We are a ...

Project Description: We are a small development house in Singapore, and have a project which is 90 percent complete; thud our previous developer could not finish due to other co

Post cache substitution, What is Post Cache substitution? The Post cach...

What is Post Cache substitution? The Post cache substitution is used when we want to cache the complete page but also require some of the dynamic region inside that cached page

Named pipe in matlab, What I need is the following: I need an implementatio...

What I need is the following: I need an implementation in matlab under windows to open a named pipe and to read data from the pipe (an other program writes into the pipe) in quasi

Thread.sleep(), What is Thread.Sleep() The Thread's execution can be pa...

What is Thread.Sleep() The Thread's execution can be paused by calling Thread.Sleep method. This Thread.Sleep() method takes an integer value which  determines how long the thr

Data validation application in asp.net, Project Description: i have pred...

Project Description: i have predefined template which contains all my user details and there order details We have to create a login where a user uploads data. Mobile  Nam

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd