Locking is a mechanism used by the Microsoft SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.
Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data. The transaction does this by requesting
a lock on the piece of data. Locks have different modes, such as shared or exclusive. The lock mode defines the level of dependency the transaction has on the data. No transaction can be granted a lock that would conflict with the mode of a lock already granted
on that data to another transaction. If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the Database Engine will pause the requesting transaction until the first lock is released.
When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. How long a transaction holds the locks acquired to protect read operations depends on the transaction isolation level setting. All
locks held by a transaction are released when the transaction completes (either commits or rolls back).
Applications do not typically request locks directly. Locks are managed internally by a part of the Database Engine called the lock manager. When an instance of the Database Engine processes a Transact-SQL statement, the Database Engine query processor determines
which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from
the lock manager. The lock manager grants the locks if there are no conflicting locks held by other transactions.
diya0076
Member
57 Points
105 Posts
Advantage and Disadvantage of lock in OLTP system??
Oct 15, 2010 07:20 PM|LINK
i want to know Advantage and Disadvantage of lock in OLTP system??
vijay sirohi...
Member
12 Points
1 Post
Re: Advantage and Disadvantage of lock in OLTP system??
Oct 21, 2010 05:04 PM|LINK
This is a SQL question and should be opened in SQL forum
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/threads
Jothiraman J...
Member
44 Points
7 Posts
Re: Advantage and Disadvantage of lock in OLTP system??
Oct 21, 2010 05:22 PM|LINK
Hello ,
Locking is a mechanism used by the Microsoft SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.
Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data. The transaction does this by requesting a lock on the piece of data. Locks have different modes, such as shared or exclusive. The lock mode defines the level of dependency the transaction has on the data. No transaction can be granted a lock that would conflict with the mode of a lock already granted on that data to another transaction. If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the Database Engine will pause the requesting transaction until the first lock is released.
When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. How long a transaction holds the locks acquired to protect read operations depends on the transaction isolation level setting. All locks held by a transaction are released when the transaction completes (either commits or rolls back).
Applications do not typically request locks directly. Locks are managed internally by a part of the Database Engine called the lock manager. When an instance of the Database Engine processes a Transact-SQL statement, the Database Engine query processor determines which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager. The lock manager grants the locks if there are no conflicting locks held by other transactions.
You can read more about locks in following links
Lock Escalation (Database Engine)
http://msdn.microsoft.com/en-us/library/ms184286.aspx
How to reduce lock contention in SQL Server
<http://support.microsoft.com/?kbid=75722>
How to resolve blocking problems that are caused by lock escalation in SQL Server
<http://support.microsoft.com/?kbid=323630>
Thank You
Jayaprakash JO - MSFT