SQL Server 6.5: Locking



Introduction

General details

Lock Escalation

Setting insert row lock

Introduction

In this article I want to tell you about some general details of
SQL Server 6.5 locking, about Lock Escalation, 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.


    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’
    GO
    EXEC sp_configure ‘LE threshold maximum’, 250
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    EXEC sp_configure ‘LE threshold maximum’
    GO

    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


    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’]

    where

    @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’


    »


    See All Articles by Columnist
    Alexander Chigrik

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles