Home

	Expand Search

Performance and Tuning Guide Volumes 1 - 3 (Online Only)

Chapter 10 Locking in Adaptive Server

Chapter 10

Locking in Adaptive Server

This chapter discusses basic locking concepts and the locking schemes and types of locks used for databases in Adaptive Server.

The following chapters provide more information on locking:

• Chapter 13, "Locking Configuration and Tuning," describes performance considerations and suggestions and configuration parameters that affect locking.

• Chapter 11, "Using Locking Commands,"describes commands that affect locking: specifying the locking scheme for tables, choosing an isolation level for a session or query, the lock table command, and server or session level lock time-outs periods.

• Chapter 12, "Reporting on Locks," describes commands for reporting on locks and locking behavior, including sp_who, sp_lock, and sp_object_stats.

How locking affects performance

Overview of locking

Granularity of locks and locking schemes

Allpages locking

Datapages locking

Datarows locking

Types of locks in Adaptive Server

Page and row locks

Table locks

Demand locks

Range locking for serializable reads

Latches

Lock compatibility and lock sufficiency

How isolation levels affect locking

Isolation Level 0, read uncommitted

Isolation Level 1, read committed

Isolation Level 2, repeatable read

Isolation Level 3, serializable reads

Adaptive Server default isolation level

Lock types and duration during query processing

Lock types during create index commands

Locking for select queries at isolation Level 1

Table scans and isolation Levels 2 and 3

When update locks are not required

Locking during or processing

Skipping uncommitted inserts during selects

Pseudo column-level locking

Select queries that do not reference the updated column

Using alternative predicates to skip nonqualifying rows

Qualifying old and new values for uncommitted updates

Suggestions to reduce contention