SQLServerCentral Article

Locking in SQL Server 6.5

,


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

Rate

Share

Share

Rate