Locking in SQL Server 6.5

Introduction

General details

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

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

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

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

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

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