ALLBASE/SQL Reference Manual (36216-90216)

Chapter 5 177

Concurrency Control through Locks and Isolation Levels

Details of Locking

To promote the greatest concurrency, ALLBASE/SQL supports a variety of granularities

and lock types. Granularity is the size of the object locked. Lock type is the severity of

locking provided. Compatibility refers to the ability of different transactions to hold locks

at the same time on the same object.

Lock Granularities

The use of different granularities of locking promotes a high level of concurrency. There

are three levels of granularity in ALLBASE/SQL:

• Row (tuple) level

• Page level

• Table level

Although some system operations use row level locking internally, system operations

acquire page locks by default. User-created tables can be locked at the row, page, or table

level, depending on the table type. B-tree and constraint indexes are locked with weak

locks at the page level for update operations and are not locked at all on reads. Table, page,

and row level locking are illustrated in Figure 5-3. and Figure 5-4. Figure 5-3. portrays a

query that accesses two pages of a table.

Figure 5-3. Page Versus Table Level Locking

With page level locking, pages containing data scanned for the query are locked. All other

pages can be locked by other transactions. With table level locking, the same query locks

the table as a whole, whether or not the individual pages are being used for a query. This

means that when a table has an exclusive lock on it, no other transaction can obtain any

locks on the table or any data page in it until the transaction holding the page lock

terminates.