HP 3000 Manuals HP 3000 Manuals
What Determines Lock Types
ALLBASE/SQL locks one or more of the following objects:
* Tables. Rows or pages of tables or entire tables are locked when
you execute SQL statements referencing them.
* PCRs. Pages of PCRs (indexes that support referential
constraints) are locked when ALLBASE/SQL updates a key value.
* Indexes. Pages of indexes are locked when ALLBASE/SQL updates an
index.
* System tables. Rows or pages in one or more system tables are
locked when you execute any SQL statement. System tables are
always locked at the RR level regardless of the transaction
isolation level, when they are accessed for execution of an SQL
statement. Refer to the appendix "Locks Held on the System
Catalog by SQL Statements" in the ALLBASE/SQL Database
Administration Guide for complete information.
As this summary indicates, locks on user data and indexes may be obtained
at the row level, page level, or table level. Although some locking of
system data is done at the row level, system catalog indexes are always
locked at the page level.
The locks that are applied to pages and tables are determined by a
combination of the following factors:
* Type of SQL statement.
* Locking structure implicit at CREATE TABLE time.
* Use of the LOCK TABLE statement.
* Optimizer's choice of a scan type.
* Choice of isolation level.
* Updatability of cursors or views used to access data.
* Use of sorting.
Type of SQL Statement
Specific SQL statements imply particular kinds of data access.
Statements such as SELECT and FETCH, which merely read data, request
share locks. INSERT, DELETE, and UPDATE, all of which modify tables,
request exclusive locks. In addition, the cursor manipulation statements
let you specify an intention to update certain rows of data. When you
declare a cursor in a program for updating certain columns, and you then
open the cursor, share update (SIX) locks may be obtained.
Data definition statements (CREATE and DROP, ADD and REMOVE) also request
exclusive locks, both for the objects being defined, and for the system
catalog pages containing descriptions of the objects. During data
definition, locking of system catalog pages can be extensive. Refer to
the appendix "Locks Held on the System Catalog by SQL Statements" in the
ALLBASE/SQL Database Administration Guide for a complete list of
statements and their effects on the system catalog.
When data manipulation or data definition statements update a table that
has a B-tree or constraint index defined on it, locks may also be placed
on those index pages.
Locking Structure Implicit at CREATE TABLE Time
Table 2-2 shows the general locking structure used for a table
depending on the type of locking assigned when the table is created. For
clarity, the table shows only the locks obtained for index scans. (Scan
type is described in a later section.)[REV BEG]
Table 2-2. Locking Behavior Determined by CREATE TABLE Statement
-----------------------------------------------------------------------------------------------
| | | |
| Table Type | Read Locks | Write Locks |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| PRIVATE (default) | Table Exclusive (X) | Table Exclusive (X) |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| PUBLICREAD | Table Share (S) | Table Exclusive (X) |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| PUBLIC | Table Intent Share (IS) | Table Intent Exclusive (IX) |
| | | |
| | Page Share (S) | Page Exclusive (X) |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| PUBLICROW | Table Intent Share (IS) | Table Intent Exclusive (IX) |
| | | |
| | Page Intent Share (IS) | Page Intent Exclusive (IX) |
| | | |
| | Row Share (S) | Row Exclusive (X) |
| | | |
-----------------------------------------------------------------------------------------------
[REV END][REV BEG]
PUBLICROW and PUBLIC tables allow concurrent users to access the table
for both reads and writes but they increase the chances of deadlock,
because concurrent transactions can be waiting for each other to release
locks. PUBLICROW tables obtain locks at the row level, which affords
more concurrency than with PUBLIC tables, at the possible cost of
obtaining more locks. PUBLICREAD tables allow only one transaction to
write to a table, or they allow multiple transactions to read the table;
no readers can access the table while any writing is going on. PRIVATE
tables allow only one transaction to read from or write to a table at a
time.[REV END]
If the locking structure of a table does not allow a transaction to
access the table, the transaction must wait. In a typical example, if
one transaction is reading a PUBLICREAD table, and a second transaction
executes a statement to update that table, the second transaction waits
until the first transaction executes a COMMIT WORK or ROLLBACK WORK
statement.
[REV BEG]
The implicit locking structure of a table can be changed by using the
ALTER TABLE statement.[REV END]
Use of the LOCK TABLE Statement
The LOCK TABLE statement is another determinant of lock types. With this
statement, ALLBASE/SQL explicitly locks a table as a whole, making most
page or row locking unnecessary. You can lock tables in SHARE mode,
EXCLUSIVE mode, or in SHARE UPDATE mode. With SHARE locking (S locks),
other transactions may read pages in the table you have locked but not
update them. With EXCLUSIVE locking (X locks), no other transaction may
access the locked table until your transaction commits. With share
update locking (SIX locks), other transactions may read pages that are
not being updated. However, no other transaction can obtain an exclusive
lock until your transaction ends with a COMMIT WORK or ROLLBACK WORK
statement.
You can upgrade the implicit locking mode of a table to a more severe
level by using the LOCK TABLE statement. Thus, you can lock a
PUBLIC,[REV BEG] PUBLICROW, or PUBLICREAD[REV END] table in EXCLUSIVE
mode. However, you cannot downgrade the implicit locking mode. If you
attempt to lock a PRIVATE table in SHARE mode, the LOCK TABLE statement
has no effect.
Use the LOCK TABLE statement to reduce the following:
* The overhead of obtaining and maintaining locks.
* The potential for deadlock.
Choice of a Scan Type
Another factor that determines the kind of locking in a data access
transaction is the type of scan used to process a query. There are four
types of scan:
* Sequential scan.
* Index scan.
* TID scan.
* Hash scan.
A sequential scan (also known as a serial scan) is one in which
ALLBASE/SQL begins at 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 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 in the query's predicate.
[REV BEG]
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.
[REV END]
The choice of a plan of access to the data is made by the ALLBASE/SQL
optimizer; you cannot directly choose a scan type. However, 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.[REV BEG] 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 the "SQL Queries" chapter.
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 or row by using the LOCK TABLE statement in SHARE mode. This
makes it unnecessary for ALLBASE/SQL to lock individual pages or
rows.
[REV END]
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 minimize the chance of deadlocks.
The kind of lock obtained at different isolation levels depends on the
other factors that determine locks--scan type, kind of SQL statement, and
implicit table type. A simplified summary of locks obtained on PUBLIC
tables and their indexes appears in Table 2-3 . Hash and TID scans
are omitted.
Table 2-3. Locks Obtained on PUBLIC Tables with Different Isolation Levels
----------------------------------------------------------------------------------------------------
| | | | |
| Isolation Level | Read Operations | Read for Update1 | Write Operations |
| and Scan Type | (SELECT, FETCH) | | (UPDATE, INSERT, |
| | | | DELETE) |
| | | | |
----------------------------------------------------------------------------------------------------
| | | | |
| | Table Page | Table Page | Table Page |
| | | | |
| RR Sequential | S - | SIX - | SIX X |
| | | | |
| RR Index | IS S | IX SIX | IX X |
| | | | |
----------------------------------------------------------------------------------------------------
| | | | |
| CS Sequential | IS S2 | IX SIX2 | IX X |
| | | | |
| CS Index | IS S2 | IX SIX2 | IX X |
| | | | |
----------------------------------------------------------------------------------------------------
| | | | |
| RC Sequential | IS S3 | IX SIX | IX X |
| | | | |
| RC Index | IS S3 | IX SIX | IX X |
| | | | |
----------------------------------------------------------------------------------------------------
| | | | |
| RU Sequential | None None | IX SIX | IX X |
| | | | |
| RU Index | None None | IX SIX | IX X |
| | | | |
----------------------------------------------------------------------------------------------------
| |
| 1 Opening a cursor that was declared FOR UPDATE (RR and CS), or using REFETCH (RC |
| and RU). |
| |
| 2 Lock released at the end of the next read. |
| |
| 3 Lock released at the end of the current read. |
| |
----------------------------------------------------------------------------------------------------
[REV BEG]
A simplified summary of locks obtained on PUBLICROW tables appears in
Table 2-4 . Hash and TID scans are omitted.
Table 2-4. Locks Obtained on PUBLICROW Tables with Different Isolation Levels
--------------------------------------------------------------------------------------------------
| | | | |
| Isolation Level | Read Operations | Read for Update1 | Write Operations |
| and Scan Type | (SELECT, FETCH) | | (UPDATE, INSERT, |
| | | | DELETE) |
| | | | |
--------------------------------------------------------------------------------------------------
| | | | |
| | Table Page Row | Table Page Row | Table Page Row |
| | | | |
| RR Sequential | S - - | SIX - - | SIX IX X |
| | | | |
| RR Index | IS IS S | IX IX SIX | IX IX X |
| | | | |
--------------------------------------------------------------------------------------------------
| | | | |
| CS Sequential | IS IS2 S2 | IX IX2 SIX2 | IX IX X |
| | | | |
| CS Index | IS IS2 S2 | IX IX2 SIX2 | IX IX X |
| | | | |
--------------------------------------------------------------------------------------------------
| | | | |
| RC Sequential | IS IS3 S3 | IX IX3 SIX | IX IX X |
| | | | |
| RC Index | IS IS3 S3 | IX IX SIX | IX IX X |
| | | | |
--------------------------------------------------------------------------------------------------
| | | | |
| RU Sequential | None None None | IX IX SIX | IX IX X |
| | | | |
| RU Index | None None None | IX IX SIX | IX IX X |
| | | | |
--------------------------------------------------------------------------------------------------
| | |
| 1 Opening a cursor that was declared FOR UPDATE (RR and CS), | |
| or using REFETCH (RC and RU). | |
| | |
| 2 Lock released at the end of the next read. | |
| | |
| 3 Lock released at the end of the current read. | |
| | |
--------------------------------------------------------------------------------------------------
NOTE ALLBASE/SQL locks system catalog pages at the RR isolation level
when they are accessed or modified on behalf of an SQL statement.
Refer to the appendix "Locks Held on the System Catalog by SQL
Statements" in the ALLBASE/SQL Database Administration Guide for a
list of locks acquired for each SQL statement.
[REV END]
Updatability of Cursors or Views
When a transaction uses cursors or views to access and manipulate data,
the kinds of locks obtained depend partly on whether the cursors or views
are updatable according to the rules presented under "Updatability of
Queries" in the "ALLBASE/SQL Queries" chapter. Table 5-3 shows the locks
obtained on updatable views and on updatable cursors declared FOR UPDATE;
they are listed in the "Read for Update" column in the table. In
general, SIX, IX, and X locks will not be used unless the query that
underlies the view or cursor is updatable.
Use of Sorting
If a query involves a sort operation, locks are maintained only if the
transaction is at the RR isolation level. When there is an ORDER BY, a
GROUP BY, UNION, or DISTINCT clause in a query, or if the optimizer
decides to use the sort/merge join method for joins or nested queries,
the data in the tables is sorted and copied to a temporary table. The
user's cursor is really defined on this temporary table, which does not
require any locking since it is private to the user. Locks on the
original tables underlying the view or cursor are retained only if the
transaction was started at the RR isolation level. Locks obtained at the
CS or RC level are released; locks are not obtained at all at the RU
level.