ALLBASE/SQL Reference Manual (36216-90216)

184 Chapter5
Concurrency Control through Locks and Isolation Levels
What Determines Lock Types
the first page of a table and reads each page, looking for rows that qualify for the query
result, until it arrives at the end of the table. An index scan looks up the page locations of
those rows that qualify for the query result in an index which you have separately created.
A hash scan accesses an individual row by calculating the row's primary page location
from a value supplied in the query's predicate. A TID scan obtains a specific row by
obtaining its page number from the TID (tuple ID) directly. A hash scan accesses an
individual row by calculating the row's primary page location from a value supplied by the
query's predicate.
When a sequential scan is used to access a table, the data is being read at the table level.
Depending on the isolation level of a transaction (described in the next section), a
sequential scan either locks the whole table or else locks each page of a table in share mode
(each row, in the case of a PUBLICROW table) in turn until it finds the row it is seeking.
When an index scan is used to access a table, the data is being read at the page level if the
table is PUBLIC or at the row level if the table is PUBLICROW. An index scan has to read
index pages, but no locks are acquired; a transaction only needs to lock the data page or
row pointed to by the index. Thus, an index scan that retrieves only a few rows from a
large PUBLIC table will obtain locks on fewer data pages than a sequential scan on the
same table. (Index pages are locked with IX locks only when an index is updated.) A TID
scan locks only the page or row pointed to by the TID. A hash scan locks only the data page
containing the hash key, possibly with some overflow pages. Hashing is not possible with
PUBLICROW tables.
By default, the choice of a plan of access to the data is made by the ALLBASE/SQL
optimizer. You can override the access plan chosen by the optimizer with the SETOPT
statement.
As a rule of thumb, you can assume that the optimizer chooses a sequential scan when the
query needs to read a large proportion of the pages in a table. Similarly, the optimizer
often chooses an existing index when a small number of rows (or only a single row) is to be
retrieved, and the index was created on the columns referred to in the WHERE clause of the
query. When you use a TID function, you can assume the optimizer will choose a TID scan.
To display the access plan chosen by the optimizer, use the SQL GENPLAN statement,
specifying the query of interest. Then perform a query on the SYSTEM.PLAN view in the
system catalog to display the optimizer’s choices. For more information, refer to the section
“Using GENPLAN to Display the Access Plan” in Chapter 3 , “SQL Queries.”
NOTE If you are reading a large table, and if you do not expect it to be updated by
anyone while your transaction is running, you can avoid excessive overhead
in shared memory from locks obtained on each page by using the LOCK TABLE
statement in SHARE mode. This makes it unnecessary for ALLBASE/SQL to
lock individual pages or rows.
Choice of Isolation Level
One more factor that determines the kinds of locks obtained on data objects is the isolation
level of the transaction. A higher degree of isolation means less concurrency in operations
involving PUBLIC and PUBLICROW tables. You can select the isolation level used in your
transactions to maximize concurrency for the type of operation you are performing and to