Locking in SQL Server 6.5



Introduction

General details

Transaction Isolation Levels

Lock types

Locking optimizer hints

Lock Escalation

Deadlocks

Viewing locks (sp_lock)

Setting insert row lock

Literature

Introduction

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:

  • READ UNCOMMITTED

  • READ COMMITTED

  • REPEATABLE READ

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

    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.

    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.

    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

    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

  • HOLDLOCK

  • UPDLOCK

  • TABLOCK

  • PAGLOCK

  • TABLOCKX
  • 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’
    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


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

    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’

    Literature

    1. SQL Server Books Online

    2. Transaction Isolation Level

    http://www.swynk.com/friends/achigrik/TIL.asp

    3. SQL Server 6.5: Locking

    http://www.swynk.com/friends/achigrik/Locking65.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

    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