ALLBASE/SQL Reference Manual (36216-90216)

182 Chapter5
Concurrency Control through Locks and Isolation Levels
What Determines Lock Types
• 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 the system catalog 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 5-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.)
Table 5-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)
Page Share (S)
Table Intent Exclusive (IX)
Page Exclusive (X)
PUBLICROW Table Intent Share (IS)
Page Intent Share (IS)
Row Share (S)
Table Intent Exclusive (IX)
Page Intent Exclusive (IX)
Row Exclusive (X)