Friday 9 November 2012

Introduction to Locking in SQL Server


Introduction

In this article, I want to tell you about SQL Server 7.0/2000 Transaction Isolation Levels, what kinds of Transaction Isolation Levels exist, and how you can set the appropriate Transaction Isolation Level, about Lock types and Locking optimizer hints, about deadlocks, and about how you can view locks by using the sp_lock stored procedure.

Transaction Isolation Levels

There are four isolation levels:


  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE


  • Microsoft SQL Server supports all of these Transaction Isolation Levels and can separateREPEATABLE READ and SERIALIZABLE.

    Let me to describe each isolation level.

    READ UNCOMMITTED

    When it's used, SQL Server not issue shared locks while reading data. So, you can read an uncommitted transaction that might get rolled back later. This isolation level is also called dirty read. This is the lowest isolation level. It ensures only that a physically corrupt data will not be read.

    READ COMMITTED

    This is the default isolation level in SQL Server. When it's used, SQL Server will use shared locks while reading data. It ensures that a physically corrupt data will not be read and will never read data that another application has changed and not yet committed, but it not ensures that the data will not be changed before the end of the transaction.

    REPEATABLE READ

    When it's used, the dirty reads and nonrepeatable reads cannot occur. It means that locks will be placed on all data that is used in a query, and another transactions cannot update the data.

    This is the definition of nonrepeatable read from SQL Server Books Online:

    nonrepeatable read
    When a transaction reads the same row more than one time, and between the
    two (or more) reads, a separate transaction modifies that row. Because the
    row was modified between reads within the same transaction, each read
    produces different values, which introduces inconsistency.
    

    SERIALIZABLE

    Most restrictive isolation level. When it's used, the phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction will be completed.

    This is the definition of phantom from SQL Server Books Online:

    phantom
    Phantom behavior occurs when a transaction attempts to select a row that
    does not exist and a second transaction inserts the row before the first
    transaction finishes. If the row is inserted, the row appears as a phantom
    to the first transaction, inconsistently appearing and disappearing.
    
    You can set the appropriate isolation level for an entire SQL Server session by using theSET TRANSACTION ISOLATION LEVEL statement.
    This is the syntax from SQL Server Books Online:

    SET TRANSACTION ISOLATION LEVEL 
        {
            READ COMMITTED 
            | READ UNCOMMITTED 
            | REPEATABLE READ 
            | SERIALIZABLE
        }
    
    You can use the DBCC USEROPTIONS statement to determine the Transaction Isolation Level currently set. This command returns the set options that are active for the current connection. This is the example:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    GO
    DBCC USEROPTIONS
    GO
    

    Lock types

    There are three main types of locks that SQL Server 7.0/2000 uses:


  • Shared locks
  • Update locks
  • Exclusive locks


  • Shared locks are used for operations that do not change or update data, such as a SELECT statement.

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

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

    Shared locks are compatible with other Shared locks or Update locks.

    Update locks are compatible with Shared locks only.

    Exclusive locks are not compatible with other lock types.

    Let me to describe it on the real 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 starvationLock 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 Exclusivelock to modify data. After Process3 was finished, Process4 sets the Shared lock on the page to select data.

    Locking optimizer hints

    SQL Server 7.0/2000 supports the following Locking optimizer hints:


  • NOLOCK
  • HOLDLOCK
  • UPDLOCK
  • TABLOCK
  • PAGLOCK
  • TABLOCKX
  • READCOMMITTED
  • READUNCOMMITTED
  • REPEATABLEREAD
  • SERIALIZABLE
  • READPAST
  • ROWLOCK


  • NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

    HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used. You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.

    UPDLOCK instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.

    TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.

    PAGLOCK is used by default. Directs SQL Server to use shared page locks.

    TABLOCKX takes an exclusive lock on the table that is held until the end of the command or transaction.

    READCOMMITTED
    Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.

    READUNCOMMITTED
    Equivalent to NOLOCK.

    REPEATABLEREAD
    Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.

    SERIALIZABLE
    Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.

    READPAST
    Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
    You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

    ROWLOCK
    Use row-level locks rather than use the coarser-grained page- and table-level locks.

    You can specify one of these locking options in a SELECT statement.
    This is the example:

    SELECT au_fname FROM pubs..authors (holdlock)

    Deadlocks

    Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. For example, User1 has a lock on object "A" and wants a lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A". In this case, SQL Server ends a deadlock by choosing the user, who will be a deadlock victim. After that, SQL Server rolls back the breaking user's transaction, sends message number 1205 to notify the user's application about breaking, and then allows the nonbreaking user's process to continue.

    You can decide which connection will be the candidate for deadlock victim by using SET DEADLOCK_PRIORITY. In other case, SQL Server selects the deadlock victim by choosing the process that completes the circular chain of locks.

    So, in a multiuser situation, your application should check the error 1205 to indicate that the transaction was rolled back, and if it's so, restart the transaction.

    Note. To reduce the chance of a deadlock, you should minimize the size of transactions and transaction times.

    View locks (sp_lock)

    Sometimes you need a reference to information about locks. Microsoft recommends using the sp_lock system stored procedure to report locks information. This very useful procedure returns the information about SQL Server process ID, which lock the data, about locked database, about locked table ID, about locked page and about type of locking (locktype column).

    This is the example of using the sp_lock system stored procedure:

    spid   locktype                            table_id    page        dbname
    ------ ----------------------------------- ----------- ----------- ---------------
    11     Sh_intent                           688005482   0           master
    11     Ex_extent                           0           336         tempdb
    
    The information, returned by sp_lock system stored procedure needs in some clarification, because it's difficult to understand database name, object name and index name by their ID numbers.

    Check the link below if you need to get user name, host name, database name, index name object name and object owner instead of their ID numbers:

    Sunday 4 November 2012

    Retrieve Last Inserted Identity of Record

    SELECT @@IDENTITY
    SELECT SCOPE_IDENTITY()
    SELECT IDENT_CURRENT(‘TableName’)

    All of the abouve three will get the identity value but in different approches.

    The variable @@IDENTITY will return the last generated identity value produced on a connection, without based on the table that produced the value. While @@IDENTITY is limited to the current session, it is not limited to the current scope. This means that if we insert some record in Table1 which has a trigger on the insert and the trigger inserts a record in some other table2 then the @@IDENTITY will return the identity value inserted in Table2.

    SCOPE_IDENTITY() will return the last IDENTITY value produced on a connection and by a statement in the same scope, without based on the table that produced the value. So we can say that this function is some identical to @@IDENTITY with one exception. like @@IDENTITY will return the last identity value created in the current session, but it will also limit it to your current scope as well . So that means it will return identity value inserted in Table1.

    Use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
    IDENT_CURRENT will reutrn returns the last IDENTITY value produced in a table, Without based on the connection that created the value, and Without based on the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session., So it will retrieve the last generated table identity value.


    CREATE TABLE Parent(id int IDENTITY);
    
    CREATE TABLE Child(id int IDENTITY(100,1));
    
    GO
    
    CREATE TRIGGER Parentins ON Parent FOR INSERT
    
    AS
    
    BEGIN
    
       INSERT Child DEFAULT VALUES
    
    END;
    
    GO
    
    --End of trigger definition
    
    SELECT id FROM Parent;
    --id is empty.
    
    SELECT id FROM Child;
    --ID is empty. 
    
    --Do the following in Session 1
    INSERT Parent DEFAULT VALUES;
    SELECT @@IDENTITY;
    /*Returns the value 100. This was inserted by the trigger.*/
    
    SELECT SCOPE_IDENTITY();
    /* Returns the value 1. This was inserted by the
    INSERT statement two statements before this query.*/ 
    
    SELECT IDENT_CURRENT('Child');
    
    /* Returns value inserted into Child, that is in the trigger.*/
    
    SELECT IDENT_CURRENT('Parent');
    
    /* Returns value inserted into Parent. 
    This was the INSERT statement four statements before this query.*/ 
    
    -- Do the following in Session 2.
    
    SELECT @@IDENTITY;
    
    /* Returns NULL because there has been no INSERT action
    up to this point in this session.*/ 
    
    SELECT SCOPE_IDENTITY();
    
    /* Returns NULL because there has been no INSERT action
    up to this point in this scope in this session.*/
    
    SELECT IDENT_CURRENT('Child');
    
    /* Returns the last value inserted into Child.*/