Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 29, 2001

Locking in SQL Server 7.0

By Alexander Chigrik


Introduction
General details
Transaction Isolation Levels
Lock types
Locking optimizer hints
Lock Escalation
Deadlocks
Viewing locks (sp_lock, sp_lock2)
Literature


Introduction

In this article, I want to tell you about general details of SQL Server 7.0 locking, about Transaction Isolation Levels, what kind of Transaction Isolation Level exists, and how you can set the appropriate Transaction Isolation Level, about Lock types and Locking optimizer hints, about Lock Escalation, about Deadlocks, and about how you can view locks with sp_lock or sp_lock2 stored procedure.


General details

There are two units of data storage in SQL Server 7.0:

  • Page
  • Extent
  • SQL Server 7.0 stores data on the 8Kb data pages. Only 8060 bytes are used to store the user's data, other space is used by SQL Server to store system information.

    When you insert a new row, and there is no space on the current data page to store this row, then new page will be created.

    Extent consists of eight 8 KB pages. When you create new table, then new Extent will be generated. In SQL Server 7.0, different objects can share an extent or an object can have its own extent(s). A table and index both have a minimum of two pages.

    SQL Server 7.0 can lock the following types of items:

  • RID
  • Key
  • Page
  • Extent
  • Table
  • Database
  • RID is a row identifier. It is used to individually lock a single row within a table.

    Key is a row lock within an index. Used to protect key ranges in serializable transactions.

    Page lock is a lock, when entire 8-KB data page or index page will be locked.

    Extent lock is only used for allocation. When it's used, entire extent will be locked.

    Table lock is used when a large percentage of the table's rows are queried or updated. This lock includes all table's data and indexes.

    Database lock is used when you restore the database.


    Transaction Isolation Levels

    There are four isolation levels:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
  • SQL Server 7.0 supports all of these Transaction Isolation Levels and can separate REPEATABLE 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, then 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, then phantom values cannot occur. It prevents other users from updating or inserting rows into the data set until the transaction is complete.

    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 with the SET 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 DBCC USEROPTIONS command 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 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 the other, 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 are 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.


    Locking optimizer hints

    There are twelve Locking optimizer hints in SQL Server 7.0:

  • NOLOCK
  • HOLDLOCK
  • UPDLOCK
  • TABLOCK
  • PAGLOCK
  • TABLOCKX
  • READCOMMITTED
  • READUNCOMMITTED
  • REPEATABLEREAD
  • SERIALIZABLE
  • READPAST
  • ROWLOCK
  • The first six Locking optimizer hints are used in SQL Server 6.5 and were described in my previous article about "Locking in SQL Server 6.5". See this article for more information: Locking in SQL Server 6.5

    The last six Locking optimizer hints are the new SQL Server 7.0 hints.

    Let me to describe them.

    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 performs 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 is equivalent to NOLOCK.

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

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

    READPAST skips locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set. The READPAST lock hint applies only to the SELECT statement.

    Note You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

    ROWLOCK uses row-level locks rather than use the page or 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)
    


    Lock Escalation

    This is the description of the Lock Escalation process from the SQL Server Books Online:

    Lock escalation is the process of converting many fine-grain locks
    into fewer coarse-grain locks, reducing system overhead.
    

    You cannot customize locking by setting Lock Escalation level in SQL Server 7.0. There is no such option now. SQL Server 7.0 automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold.

    Lock escalation thresholds are determined dynamically by SQL Server 7.0 and cannot be configured manually.


    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 7.0 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 for message 1205 to indicate that the transaction was rolled back and if so, to restart the transaction.

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


    Viewing locks (sp_lock)

    Sometimes you need a reference to information about locks. Microsoft recommends to use 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 ID, about locked object ID, about locked index ID and about type of locking (type, resource, mode and status columns).

    This is the results set of sp_lock stored procedure:

    spid   dbid   ObjId       IndId  Type Resource         Mode     Status
    ------ ------ ----------- ------ ---- ---------------- -------- ------
    1      1      0           0      DB                    S        GRANT
    6      1      0           0      DB                    S        GRANT
    7      1      0           0      DB                    S        GRANT
    8      1      0           0      DB                    S        GRANT
    9      1      0           0      DB                    S        GRANT
    9      2      0           0      DB                    S        GRANT
    9      1      117575457   0      TAB                   IS       GRANT
    10     1      0           0      DB                    S        GRANT
    11     5      0           0      DB                    S        GRANT
    

    The information, returned by sp_lock stored procedure, is needed in some clarification, because it's difficult to understand database name, object name and index name by their ID numbers.

    Microsoft provides an enhanced version of the sp_lock system stored procedure, which returns user name, host name, database name and object name also.

    You can find this stored procedure at here:

    INF: sp_lock2 Returns Additional Locking Details
    http://support.microsoft.com/support/kb/articles/Q255/5/96.ASP

    This enhanced stored procedure works under SQL Server 7.0 as well, but has syntax error under SQL Server 2000. It does not return the name of an index also.

    Here you can find the new version of the sp_lock2 stored procedure for SQL Server 7.0 and SQL Server 2000. This version returns index name and object owner also:

    Detailed locking view: sp_lock2


    Literature

    1. SQL Server Books Online

    2. Transaction Isolation Level
    http://www.swynk.com/friends/achigrik/TIL.asp

    3. INF: sp_lock2 Returns Additional Locking Details
    http://support.microsoft.com/support/kb/articles/Q255/5/96.ASP

    4. Detailed locking view: sp_lock2
    http://www.swynk.com/friends/achigrik/LockView.asp

    5. INF: Analyzing and Avoiding Deadlocks in SQL Server
    http://support.microsoft.com/support/kb/articles/Q169/9/60.ASP


    » See All Articles by Columnist Alexander Chigrik




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















    Thanks for your registration, follow us on our social networks to keep up-to-date