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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 25, 2001

Locking in SQL Server 6.5

By Alexander Chigrik

General details
Transaction Isolation Levels
Lock types
Locking optimizer hints
Lock Escalation
Viewing locks (sp_lock)
Setting insert row lock


In this article I want to tell you about general details of SQL Server 6.5 locking, about Transaction Isolation Level, 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, about how you can view locks with sp_lock stored procedure, and how you can set insert row lock for a particular table or tables.

General details

There are three units of data storage in SQL Server 6.5:

  • Page
  • Extent
  • Allocation Unit
  • SQL Server 6.5 stores data on the 2Kb data pages. Only 1962 bytes are used to store the user's data, other space is used by SQL Server to store system information.

    See this article for more details: Data page structure in MS SQL 6.5

    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 is the 8 continuous pages, used by one table. When you create new table, then new Extent will be generated. One Extent cannot stores the data from the two or more tables (it's for SQL Server 6.5, not for SQL Server 7.0).

    Allocation Unit contains 32 extents, or 256 pages. It's the largest unit used by SQL Server 6.5 to store the data.

    SQL Server 6.5 can lock the following types of items:

  • Page
  • Extent
  • Table
  • Intent
  • Page lock is the most common type of lock. When you update or insert new row, entire page will be locked. SQL Server automatically generates page-level locks, when a query requests rows from a table.

    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. You can set this percentage with sp_configure system stored procedure to the appropriate value. This is described below in the Lock Escalation item.

    Intent lock is a special type of table lock to indicate the type of page locks currently placed on the table.

    There is also insert row locking. The insert row locking can be set with sp_tableoption system stored procedure and will be described below.

    Transaction Isolation Levels

    There are four isolation levels:

  • SQL Server 6.5 supports all of these Transaction Isolation Levels, but has only three different behaviors, because in SQL Server 6.5 REPEATABLE READ and SERIALIZABLE are synonyms. It because SQL Server 6.5 supports only page locking (there is no full support of row locking as in SQL Server 7.0) and if REPEATABLE READ isolation level was set, then another transaction cannot insert the row before the first transaction was finished, because page will be locked. So there are no phantoms in SQL Server 6.5, if REPEATABLE READ isolation level was set.

    Let me to describe each isolation level.


    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.


    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.


    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.


    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.

    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:

            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:


    These are the results:

    Set Option                     Value
    ------------------------------ ------------------------------------
    textsize                       64512
    language                       us_english
    dateformat                     mdy
    datefirst                      7
    isolation level                read uncommitted

    Lock types

    There are three main types of locks that SQL Server 6.5 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 six Locking optimizer hints in SQL Server 6.5:

  • 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.

    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.

    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

    You can customize locking by setting Lock Escalation level. The Lock Escalation level determines, when SQL Server applies table locks instead of page locks, and it affects all users of SQL Server. So it's escalation from the page's to the table's level locking.

    There are three Lock Escalation options:

  • LE threshold maximum
  • LE threshold minimum
  • LE threshold percent
  • LE threshold maximum is the maximum number of page locks to hold before escalating to a table lock. The default value is 200.

    LE threshold minimum is the minimum number of page locks required before escalating to a table lock. The default value is 20.

    LE threshold percent is the percentage of page locks needed on a table before escalating to a table lock. The default value is 0, it means that a table lock will be occur only when the LE threshold maximum will be exceeded.

    You can configure Lock Escalation levels by using the sp_configure system stored procedure.

    This is the example to set LE threshold maximum to 250:

    EXEC sp_configure 'LE threshold maximum'
    EXEC sp_configure 'LE threshold maximum', 250
    EXEC sp_configure 'LE threshold maximum'

    These are the results:

    name                                minimum     maximum     config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    LE threshold maximum                2           500000      200          200
    Configuration option changed. Run the RECONFIGURE command to install.
    name                                minimum     maximum     config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    LE threshold maximum                2           500000      250          250


    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 6.5 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, about locked table ID, about locked page and about type of locking (locktype column).

    This is the results set of sp_lock stored procedure:

    spid   locktype                            table_id    page        dbname
    ------ ----------------------------------- ----------- ----------- ---------------
    11     Sh_intent                           688005482   0           master
    11     Ex_extent                           0           336         tempdb

    Setting insert row lock

    You can set insert row lock option for the user-defined table/tables with sp_tableoption system stored procedure.

    This is the syntax from SQL Server Books Online:

    sp_tableoption @TableNamePattern  [, '@OptionName'] [, '@OptionValue']


    @TableNamePattern is a user-defined database table.
    @OptionName is a option name.
    There are insert row lock and pintable options.
    @OptionValue is a option value. @OptionValue can be 'false' or 'true'.

    The default value for insert row lock option is 'false'.

    This is the example to set insert row lock option for authors table:

    EXEC sp_tableoption 'authors', 'insert row lock', 'true'


    1. SQL Server Books Online

    2. Transaction Isolation Level

    3. SQL Server 6.5: Locking

    4. Detailed locking view: sp_lock2

    5. INF: Analyzing and Avoiding Deadlocks in SQL Server

    » 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