Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
What are the different locks in SQL SERVER
What are the different locks in SQL SERVER ?
✍: Guest
Depending on the transaction level six types of lock can be acquired on data
Intent
The intent lock shows the future intention of SQL Server's lock manager to acquire locks
on a specific unit of data for a particular transaction. SQL Server uses intent locks to
queue exclusive locks, thereby ensuring that these locks will be placed on the data elements
in the order the transactions were initiated. Intent locks come in three flavors: intent
shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
IS locks indicate that the transaction will read some (but not all) resources in the table or
page by placing shared locks.
IX locks indicate that the transaction will modify some (but not all) resources in the table
or page by placing exclusive locks.
SIX locks indicates that the transaction will read all resources, and modify some(but not
all) of them. This will be accomplished by placing the shared locks on the resources read
and exclusive locks on the rows modified. Only one SIX lock is allowed per resource at
one time; therefore, SIX locks prevent other connections from modifying any data in the
resource (page or table), although they do allow reading the data in the same resource.
Shared
Shared locks (S) allow transactions to read data with SELECT statements. Other
connections are allowed to read the data at the same time; however, no transactions are
allowed to modify data until the shared locks are released.
Update
Update locks (U) are acquired just prior to modifying the data. If a transaction modifies 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.
Using update locks prevents multiple connections from having a shared lock that want to
eventually modify a resource using an exclusive lock. Shared locks are compatible with
other shared locks, but are not compatible with Update locks.
Exclusive
Exclusive locks (X) completely lock the resource from any type of access including reads.
They are issued when data is being modified through INSERT, UPDATE and DELETE
statements.
Schema
Schema modification locks (Sch-M) are acquired when data definition language statements,
such as CREATE TABLE, CREATE INDEX, ALTER TABLE, and so on are being
executed. Schema stability locks (Sch-S) are acquired when store procedures are being
compiled.
Bulk Update
Bulk update locks (BU) are used when performing a bulk-copy of data into a table with
TABLOCK hint. These locks improve performance while bulk copying data into a table;
however, they reduce concurrency by effectively disabling any other connections to read
or modify data in the table.
2007-10-25, 4839👍, 0💬
Popular Posts:
In below sample code if we create a object of class2 which constructor will fire first? Public Class...
Can include files be nested? The answer is yes. Include files can be nested any number of times. As ...
What are different properties provided by Objectoriented systems ? Following are characteristic’s of...
How do you locate the first X in a string txt? A) txt.find('X'); B) txt.locate('X'); C) txt.indexOf(...
How To Enter Numeric Values as HEX Numbers? - MySQL FAQs - Introduction to SQL Basics If you want to...