SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-16
Specifying Access Option and Lock Characteristics
Prior to parallel execution of a SELECT statement, some table might not be partitioned
or might be partitioned in a way that does not facilitate parallel processing. The
optimizer can request that the executor repartition (reorganize) a copy of the data at
run time. During repartitioning, SQL distributes the data over a set of temporary
partitions. Each partition contains data that can then be processed in parallel by a
separate ESP.
The optimizer considers the cost of repartitioning and sorting the data when it selects
the best execution plan for the query. For more information, see Section 5, Selectivity
and Cost Estimates. For more information about adding partitions to tables, see the
SQL/MP Installation and Management Guide.
Specifying Access Option and Lock
Characteristics
SQL supplies locks and access options to protect the integrity and concurrency of a
database:
•
Integrity refers to data that is accurate, valid, and consistent according to rules
established for changing the database.
•
Concurrency refers to the ability of two or more processes to gain access to the
same data at the same time. With more concurrency, a greater number of
transactions can complete in a given timeframe.
Access option and lock mode can also influence the use of sequential block buffering
and the use of certain parallel access plans.
This subsection provides an introduction to access options, lock mode, and lock
granularity. For more information, see the SQL/MP Reference Manual.
Access Option
SQL provides these access options that affect the characteristics of locks:
•
Browse access
Browse access ignores existing locks and does not acquire any locks. A DML
statement using this option reads data locked by other users. This option is allowed
only for querying data (not for updating data). Browse access is also known as
read through locks or dirty reads. For a UNION operation, browse access applies
only to the portion of the query for which it is specified, and does not necessarily
apply to the entire query.
•
Repeatable access
Repeatable access locks all data accessed through the DML statement. If a
transaction reads the same row multiple times, the row will contain the same data.
Range locks prohibit the insertion of rows with keys between the first key and the
last key of the range.