Locking in SQL Server 6.5

Locking in SQL Server 6.5

Alexander Chigrik
chigrik@hotmail.com

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


This is 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


This is 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

3. SQL Server 6.5: Locking

4. Detailed locking view: sp_lock2

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