Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Have you ever faced situation when you trying to retrieve any data and the query keeps on waiting on

  • Have you ever faced situation when you trying to retrieve any data and the query keeps on waiting on the data and eventually times out. One of the reasons for the same can be locking. What are the different types of locks inside SQL Server 2008 R2?

    Posted on 01-05-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

13  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    7

    The following are the locking modes in SQL server 2008 R1

    Shared (S): Used for read operations that do not change or update data, such as a SELECT statement.

    Update (U): Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

    Exclusive (X): 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 types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

    Intent shared (IS)
    Intent exclusive (IX)
                Shared with intent exclusive (SIX)
                Intent update (IU)
                Shared intent update (SIU)
                Update intent exclusive (UIX)
    

    Schema: Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

    Bulk Update (BU): Used when bulk copying data into a table and the TABLOCK hint is specified.

    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.

    Cursor Locking: In SQL Server, the SELECT statement in a cursor definition is subject to the same transaction locking rules that apply to any other SELECT statement. In cursors, however, an additional set of scroll locks can be acquired based on the specification of a cursor concurrency level.

    We have faced many situations where the queries are waiting for the data to be available. This was manily due to the open transaction in the application. We have to always check the lock compatibilty between the locks. If two lock modes are not mutually compatible there is a good prone to have a blocking and locking leads to dead lock situation.

    Eg:

    --Session 1(Execute the below statement)
    --create Table T1 (Col1 int , Col2 int)
    
    BEGIN TRAN T1
    
    Update T1 Set Col1=2
    
    
    --Session 2 (Execute the below and see the waiting)
    Select * From T1
    
    --Session 2 (Stop the above query execution and look at the active transactions)
    SELECT SP.SPID,nt_username,[TEXT] as SQLcode FROM SYS.SYSPROCESSES SP
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1
    
    
    --Session 1(After confirming the delay due to the open transaction, please fire rollback and see the result in second session)
    Rollback Tran T1
    
    
    --Session 2 (Execute the below and see the results)
    Select * From T1
    
    
    Replied on Jan 5 2012 12:27AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    5

    **

    SQL Locks

    **

    Shared (S)

    Used for read operations that do not change or update data, such as a SELECT statement.

    Update (U)

    Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions

    are reading, locking, and potentially updating resources later.

    Exclusive (X)

    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 types of intent locks are: intent shared (IS), intent exclusive (IX),

    and shared with intent exclusive (SIX).

    Schema

    Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema

    modification (Sch-M) and schema stability (Sch-S).

    Bulk Update (BU)

    Used when bulk copying data into a table and the TABLOCK hint is specified.

    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.

    Few detail study about SQL Lock from wiki

    SQL Server allows multiple clients to use the same database concurrently.

    As such, it needs to control concurrent access to shared data, to ensure data integrity - when multiple clients update the same data, or clients attempt to read data that is in the process of being changed by another client. SQL Server provides two modes of concurrency control: pessimistic concurrency and optimistic concurrency.

    When pessimistic concurrency control is being used, SQL Server controls concurrent access by using locks. Locks can be either shared or exclusive.

    Exclusive lock grants the user exclusive access to the data - no other user can access the data as long as the lock is held.

    Shared locks are used when some data is being read - multiple users can read from data locked with a shared lock, but not acquire an exclusive lock.

    The latter would have to wait for all shared locks to be released.

    Locks can be applied on different levels of granularity - on entire tables, pages, or even on a per-row basis on tables.

    For indexes, it can either be on the entire index or on index leaves.

    The level of granularity to be used is defined on a per-database basis by the database administrator. While a fine grained locking system allows more users to use the table or index simultaneously, it requires more resources. So it does not automatically turn into higher performing solution.

    SQL Server also includes two more lightweight mutual exclusion solutions - latches and spinlocks - which are less robust than locks but are less resource intensive.

    SQL Server uses them for DMVs and other resources that are usually not busy. SQL Server also monitors all worker threads that acquire locks to ensure that they do not end up in deadlocks - in case they do, SQL Server takes remedial measures, which in many cases is to kill one of the threads entangled in a deadlock and rollback the transaction it started.

    To implement locking, SQL Server contains the Lock Manager.

    Example SQL Lock Refrence Click 1

    Study SQL Lock Refrence Click 2

    Thanks,

    Yogesh K

    Replied on Jan 5 2012 12:32AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    5

    Below are the different locks inside the SQL server 2008 R2:

    Shared (S): Used for read operations that do not change or update data, such as a SELECT statement.

    Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

    Exclusive (X): 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 types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

    Schema: Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

    Bulk Update (BU): Used when bulk copying data into a table and the TABLOCK hint is specified.

    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.

    Intent shared (IS) : Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.

    Intent Exclusive (IX): Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX is a superset of IS, and it also protects requesting shared locks on lower level resources.

    Shared with Intent exclusive (IX) : Protects requested or acquired shared locks on all resources lower in the hierarchy and exclusive locks on some (but not all) of the lower level resources. intent concurrent IS locks at the top-level resource are allowed. For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.

    Intent Update (IU): Protects requested or acquired update locks on all resources lower in the hierarchy. IU locks are used only on page resources. IU locks are converted to IX locks if an update operation takes place.

    Shared intent update (SIU): A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.

    Update intent exclusive (UIX): A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

    Schema Locks: The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released.

    Bulk Update Locks: The Database Engine uses bulk update (BU) locks when bulk copying data into a table, and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table.

    Key-Range Locks: Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQ L statement while using the serialize transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

    Replied on Jan 5 2012 12:58AM  . 
    dips
    928 · 0% · 30
  • Score
    4

    *Different types of locks inside SQL Server 2008 R2 are as below:*

    Shared (S) - Used for read operations that do not change or update data, such as a SELECT statement.

    Update (U) - Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

    Exclusive (X) - 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 types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

    Schema - Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

    Bulk Update (BU) - Used when bulk copying data into a table and the TABLOCK hint is specified.

    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. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.

    Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). They are as below:

    Intent shared (IS) - Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.

    Intent exclusive (IX) - Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX is a superset of IS, and it also protects requesting shared locks on lower level resources.

    Shared with intent exclusive (SIX) - Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. Concurrent IS locks at the top-level resource are allowed. For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.

    Intent update (IU) - Protects requested or acquired update locks on all resources lower in the hierachy. IU locks are used only on page resources. IU locks are converted to IX locks if an update operation takes place.

    Shared intent update (SIU) - A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.

    Update intent exclusive (UIX) - A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

    Best example of Locking you can observe is:

    1. Start transaction in one query editor
    2. Update one column or record
    3. Open another query editor
    4. Fire select query on the same table or record to retrieve the data
    5. Your query keeps on waiting for the data

    If you Rollback or Commit the Transaction then query return the result without any waiting.

    Replied on Jan 5 2012 1:29AM  . 
    Hardik Doshi
    20 · 9% · 2863
  • Score
    4

    When we are trying to retrieve or select any data, that query or user requests a shared lock on the table/data. However if another query is running prior to this one such as update query, that would have created an exclusive lock on the table/data which prevents the select query from running. The default isolation level is READ COMMITTED which is in effect and the query keeps on waiting on the data and eventually times out (if update takes too long).

    There are different types of locks that can be acquired to avoid dirty reads and get consistent results.

    Shared Locks: used with select statements

    Update Locks: are used when SQL Server intends to modify the page, and later promotes the update page lock to an exclusive lock before actually making the changes

    Exclusive Locks: are used for DML only.

    Let me describe with an example.

    Process 1: fires an update query such as

    begin tran update

    update table table_name set col=value

    commit tran update

    Process 2: fires a select query as select * from table_name. Let us assume that process 1 starts before process 2. The update statement would acquire the exclusive lock on the table. Since shared lock is not compatible with exclusive lock, the process 2 ie select statement would wait until process 1 finishes. As soon as process 1 is completed, process 2 executes successfully.

    Other locks are: Intent, Schema,Bulk Update and Key-range (more on: Lock Modes)

    Locking Optimizer Hints

    NOLOCK: no locks as name says so can read an uncommitted transaction, high concurrency but less consistency.

    HOLDLOCK:directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used.HOLDLOCK is equivalent to SERIALIZABLE.

    UPDLOCK: while reading table, this lock would acquire Update lock instead of Shared lock and holds it until end of transaction

    TABLOCK: shared lock on the table until end of command.

    PAGLOCK: Page lock is used by default by sql server.

    Replied on Jan 5 2012 2:20AM  . 
    manik
    752 · 0% · 42
  • Score
    7

    Different types of locks inside SQL Server 2008 R2 are as below

    Shared locks

    Shared (S) locks are used for operations that read data, such as a SELECT statement. During Shared (S) locks use, concurrent transactions can read (SELECT) a resource, but cannot modify the data while Shared (S) locks exist on the resource. If you do not use the HOLDLOCK locking hint and your transaction isolation level is not set to REPEATABLE READ or SERIALIZABLE, the Shared (S) locks on a resource are released as soon as the data has been read. If you use the HOLDLOCK locking hint or your transaction isolation level is set to REPEATABLE READ or SERIALIZABLE, the Shared (S) locks on a resource will be held until the end of the transaction.

    Update locks

    Update (U) locks are used when SQL Server intends to modify a row or page, and later promotes the update page lock to an exclusive lock before actually making the changes. The Update (U) locks are used to prevent a deadlock. For example, if two transactions intend to update the same row, each of these transactions will set the shared lock on this resource and then try to set the exclusive lock. Without Update (U) locks, each transaction will wait for the other transaction to release its shared-mode lock, and a deadlock will occur.

    To prevent a potential deadlock, the first transaction that tries to update the row will set the Update (U) lock on this row. Because only one transaction can obtain an Update (U) lock to a resource at a time, the second transaction will wait until the first transaction converts the update lock to an exclusive lock and releases the locked resource.

    Exclusive locks

    Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.

    Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock.

    Intent locks

    Intent locks are used when SQL Server wants to acquire a shared lock or exclusive lock on some of the resources lower down in the hierarchy.

    Intent locks include:
    intent shared (IS)
    intent exclusive (IX)
    shared with intent exclusive (SIX)
    intent update (IU)
    update intent exclusive (UIX)
    shared intent update (SIU)

    Schema locks

    Schema locks are used when an operation dependent on the schema of a table is executing.

    Schema locks include: schema modification (Sch-M) schema stability (Sch-S) Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation is being performed.

    Schema stability (Sch-S) locks are used when compiling queries. This lock does not block any transactional locks, but when the Schema stability (Sch-S) lock is used, the DDL operations cannot be performed on the table.

    Bulk Update locks

    Bulk Update (BU) locks are used during bulk copying of data into a table when one of the following conditions exist:

    • TABLOCK hint is specified
    • table lock on bulk load table option is set using sp_tableoption

    The bulk update table-level lock allows processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

    Key-Range locks

    Key-Range locks are used by SQL Server to prevent phantom insertions or deletions into a set of records accessed by a transaction. Key-Range locks are used on behalf of transactions operating at the serializable isolation level.

    Shared Key-Range and Shared Resource (RangeS_S) locks are used to indicate a serializable range scan.

    Shared Key-Range and Update Resource (RangeS_U) locks are used to indicate a serializable update scan.

    Insert Key-Range and Null Resource (RangeI_N) locks are used to test ranges before inserting a new key into an index.

    Exclusive Key-Range and Exclusive Resource (RangeX_X) locks are used when updating a key in a range.

    There are also Key-Range conversion locks. Key-Range conversion locks include:

    RangeIS RangeIU RangeIX RangeXS RangeX_U Key-Range conversion locks are created when a Key-Range lock overlaps another lock.

    RangeIS locks are used when RangeIN lock overlap Shared (S) lock. RangeIU locks are used when RangeIN lock overlap Update (U) lock. RangeIX locks are used when RangeIN lock overlap Exclusive (X) lock. RangeXS locks are used when RangeIN lock overlap RangeSS lock. RangeXU locks are used when RangeIN lock overlap RangeSU lock.

    Key-Range conversion locks are rarely used and can be observed for a short period of time under complex circumstances.

    http://www.databasejournal.com/features/mssql/article.php/3289661/Understanding-SQL-Server-2000-Locking.htm

    http://msdn.microsoft.com/en-us/library/ms175519.aspx

    To Get full information about transaction locks
    sys.dmtranlocks
    http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx

    Example
    There are four processes, which attempt to lock the same page of the same table. These processes start one after another, so Process1 is the first process, Process2 is the second process and so on.

    Process1 : SELECT
    Process2 : SELECT
    Process3 : UPDATE
    Process4 : SELECT

    Process1 sets the Shared lock on the page, because there are no another locks on this page.
    Process2 sets the Shared lock on the page, because Shared locks are compatible with other Shared locks.
    Process3 wants to modify data and wants to set Exclusive lock, but it cannot make it before Process1 and Process2 will be finished, because Exclusive lock is not compatible with other lock types. So, Process3 sets Update lock.

    Process4 cannot set Shared lock on the page before Process3 will be finished. So, there is no Lock starvation.

    Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. So, Process4 waits before Process3 will be finished.

    After Process1 and Process2 were finished, Process3 transfer Update lock into Exclusive lock to modify data.

    After Process3 was finished, Process4 sets the Shared lock on the page to select data.

    http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

    Replied on Jan 5 2012 3:38AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    4

    SQL Server can lock these resources:

    Resource Description RID Row identifier. Used to lock a single row within a table. Key Row lock within an index. Used to protect key ranges in serializable transactions. Page 8 kilobyte –(KB) data page or index page. Extent Contiguous group of eight data pages or index pages. Table Entire table, including all data and indexes. DB Database.

    The following are the locking modes in SQL server 2008 R1:

    Shared (S) - Used for operations that do not change or update data (read-only operations), such as a SELECT statement.

    Update (U) - Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

    Exclusive (X) - 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 types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

    Schema - Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

    Bulk Update (BU) - Used when bulk-copying data into a table and the TABLOCK hint is specified.

    Steps to observe Locking is:

    Step 1: Start transaction in one query editor Step 2: Update one column or record of a table Step 3: Open another query editor Step 4: Fire select query on the same table to retrieve the data, now you can see the waiting of query to retrive the data. Step 5: If you Rollback or Commit the transaction then query return the result without any waiting.

    Replied on Jan 5 2012 6:19AM  . 
    SQLShan
    690 · 0% · 48
  • Score
    2

    Shared,Update,Exclusive,Intent,Bulk Update and Key Range lock modes.

    Replied on Jan 5 2012 7:44PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    3

    SQL Server 2008 R2 - Lock Modes

    The Microsoft SQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.

    The following table shows the resource lock modes that the Database Engine uses.

    Shared (S)

    Used for read operations that do not change or update data, such as a SELECT statement.

    Update (U)

    Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

    Exclusive (X)

    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 types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

    Schema

    Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).

    Bulk Update (BU)

    Used when bulk copying data into a table and the TABLOCK hint is specified.

    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.

    Replied on Jan 5 2012 10:20PM  . 
    nikhildas
    1537 · 0% · 13
  • Score
    5

    Locking

    SQL Server has multigranular locking that allows different types of resources to be locked by a transaction to determine how the resources can be accessed by concurrent transactions. To minimize the cost of locking, SQL Server locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency, but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions, but has a lower overhead because fewer locks are being maintained.

    The following are the resource lock modes available in SQL Server R2

    Shared Locks

    Shared locks allow concurrent transactions to read a resource. No other transactions can modify the data while shared locks exist on the resource. Shared locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared locks for the duration of the transaction.

    Update Locks

    Used on resources that can be updated. Update locks prevent a common form of deadlock . Only one update lock can be held on the data at one time. update lock don’t allow to modify the underlying data. It has to be converted to an exclusive lock before the modification takes place. We can also force an update lock with the UPDLOCK hint

    Exclusive Locks

    Exclusive locks prevent access to a resource by concurrent transactions. No other transactions can read or modify data locked with an exclusive lock. Read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. Because DML statements first need to read the data they want to modify we will always find Exclusive locks accompanied by shared locks on that same data.

    Intent Locks

    Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the data. An intent lock indicates that SQL Server wants to acquire a shared lock or exclusive lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock on the table containing that page. Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

    Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

    Intent shared (IS): Indicates the intention of a transaction to read some (but not all) resources lower in the hierarchy by placing Shared locks on those individual resources.

    Intent exclusive (IX): Indicates the intention of a transaction to modify some (but not all) resources lower in the hierarchy by placing exclusive locks on those individual resources. IX is a superset of IS.

    Shared with intent exclusive (SIX) : Indicates the intention of the transaction to read all of the resources lower in the hierarchy and modify some (but not all) resources lower in the hierarchy by placing IX locks on those individual resources.

    Schema Locks

    Schema modification locks are used when a table data definition language (DDL) operation is being performed. Schema stability locks are used when compiling queries. Schema stability locks do not block any transactional locks, including exclusive locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive locks on a table. However, DDL operations cannot be performed on the table.

    Bulk Update Locks

    Bulk update locks are used when bulk copying data into a table and either the TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption. Bulk update locks allow processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

    Key-Range Locks

    Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

    Reference:

    http://msdn.microsoft.com/en-us/library/ms175519.aspx

    http://blogs.technet.com/b/josebda/archive/2009/03/19/sql-server-2008-locking.aspx

    http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

    Replied on Jan 6 2012 1:03AM  . 
    Sineetha
    106 · 2% · 492
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.