What Determines Lock Types

ALLBASE/SQL locks one or more of the following three 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 are obtained at the row level, page level, or at the 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 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 “Locking Behavior Determined by CREATE TABLE Statement” 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)

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.

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.

The implicit locking structure of a table can be changed by using the ALTER TABLE statement.

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, PUBLICROW, or PUBLICREAD 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:

Serial scan
Index scan
Hash scan
TID 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 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 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 5-3 “Locks Obtained on PUBLIC Tables with Different Isolation Levels”. Hash and TID scans are omitted.

Table 5-3 Locks Obtained on PUBLIC Tables with Different Isolation Levels

Isolation Level and Scan Type	Read Operations (SELECT, FETCH)		Read for Update^[1]		Write Operations (UPDATE, INSERT, DELETE)
Isolation Level and Scan Type	Table	Page	Table	Page	Table	Page
RR Sequential	S	-	SIX	-	SIX	X
RR Index	IS	S	IX	SIX	IX	X
CS Sequential	IS	S^[2]	IX	SIX	IX	X
CS Index	IS	S	IX	SIX	IX	X
RC Sequential	IS	S^[3]	IX	SIX	IX	X
RC Index	IS	S	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.

A simplified summary of locks obtained on PUBLICROW tables appears in Table 5-4 “Locks Obtained on PUBLICROW Tables with Different Isolation Levels” Hash and TID scans are omitted.

Table 5-4 Locks Obtained on PUBLICROW Tables with Different Isolation Levels

Isolation Level and Scan Type	Read Operations (SELECT, FETCH)			Read for Update^[1]			Write Operations (UPDATE, INSERT, DELETE)
Isolation Level and Scan Type	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^[2]
CS Sequential	IS	IS^[3]	Sc	IX	IXc	SIXc	IX	IX	X
CS Index	IS	ISc	Sc	IX	IXc	SIXc	IX	IX	Xb
RC Sequential	IS	IS^[4]	Sd	IX	IXd	SIX	IX	IX	X
RC Index	IS	ISd	Sd	IX	IX	SIX	IX	IX	Xb
RU Sequential	None	None	None	IX	IX	SIX	IX	IX	X
RU Index	None	None	None	IX	IX	SIX	IX	IX	Xb
^[1]Opening a cursor that was declared `FOR UPDATE` (RR and CS), or using REFETCH (RC and RU). ^[2]Next higher key's data row is locked for an insert or delete, and the next two higher key's data rows are locked for an update. ^[3]Lock released at the end of the next read. ^[4]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.

Neighbor Locking

Neighbor locking is a way indexes are maintained. More than one object is locked within a Publicrow. SQLMon is the best tool to get the kind of locks held on SQL objects.

During an index scan, "weak" (IS, IX) locks are placed on index and data pages. A tuple (page) lock will be placed on the qualifying tuple(s). In order to insure RR (Repeatable Read), an additional tuple (page) lock is placed on the data tuple corresponding to the higher key next to the qualifying key. During a RR/CS/RC index scan, the qualifying data tuple are locked in S. During inserts and deletes, the higher key's tuple is locked in X for uniqueness and to insure RR for readers. Of course, the updated tuple is locked in X also. During an update where the key is updated, we end up with two higher key locks because the update corresponds to an index delete followed by an index insert. What should you lock if there is no higher? Lock an imaginary tuple which has the highest possible key. Note that locks are placed at the tuple level for PUBLICROW or at the page level for PUBLIC tables.

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 Chapter 3 “SQL Queries” Table 5-3 “Locks Obtained on PUBLIC Tables with Different Isolation Levels” 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.