Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Locking in SQL Server 6.5

By Alexander Chigrik,



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

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

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'
Total article views: 2684 | Views in the last 30 days: 0
 
Related Articles
FORUM

Lock escalation

Lock escalation

FORUM

Maximum row size in SQL Server 2000.

Maximum row size in SQL Server 2000.

FORUM

Server exceeding maximum memory threshold

I have a server that is set to a minimum and maximum memory setting of 3GB. However external monitor...

FORUM

MAXIMUM instance

MAXIMUM instance

ARTICLE

Maximum Row Size in SQL Server 2005

What is the page size in SQL Server 2005? That's an easy question, but what is the maximum row size?...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones