what are the different types of locks available in sql server? [Resolved]

Posted by Rajendra.prasad under Sql Server on 5/12/2011 | Points: 10 | Views : 323756 | Status : [Member] | Replies : 7
Hi,
what are the different types of locks available in sql server?

Regards,
Rajendra




Responses

Posted by: Msahoo on: 5/12/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
There are different types of lock in SQL Server 2000 and 2005. These locks are applied in different situations. Following are the list of locks :

SHARED - SQL Server uses shared locks for all read operations. For example a select statement.

UPDATE - This lock applied on those resources that can be updated. This lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later.

EXCLUSIVE - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

INTENT - Used to establish a lock hierarchy. The different types of intent locks are: intent shared, intent exclusive, and shared with intent exclusive.

SCHEMA - Used when an operation dependent on the schema of a table is executing. The different types of schema locks are: schema modification and schema stability.

BULK UPDATE - This lock is applied when there is a bulk copying of data and the TABLOCK is applied.

KEY RANGE - Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Thank You.

Regards,
Manoranjan Sahoo
http://www.dotnetsquare.com

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vinith on: 5/12/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,
listed below the types of locks available..

* Shared (S)
* Update (U)
* Exclusive (X)
* Intent
* Schema
* Bulk Update (BU)
* Key-range

To know more please refer the below link
http://msdn.microsoft.com/en-us/library/ms175519.aspx

Thanks,

Vinith Anthony

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/23/2012 [Member] Starter | Points: 25

Up
0
Down




1) SHARE LOCK
2) SHARE UPDATE LOCK
3) EXCLUSIVE LOCK


REGARDS


sriram

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 5/23/2012 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Dear Rajendra.prasad

If the answers that have been supplied here have helped you , please mark them as resolved.

Thanks

Thank you for posting at Dotnetfunda
[Administrator]

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajeshatkiit on: 12/17/2015 [Member] Starter | Points: 25

Up
0
Down
http://www.codeproject.com/Articles/342248/Locks-and-Duration-of-Transactions-in-MS-SQL-Serve/
http://www.lcard.ru/~nail/sybase/perf/19289.htm/

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manideepgoud on: 12/17/2015 [Member] Starter | Points: 25

Up
0
Down
Do not Copy paste the code, please provide the code snippet and describe it so that it would be easy for the post author to understand the subject and implement it.

Rajendra.prasad, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response