Page Locks

The types of page locks that DB2 can take are outlined in Table 23.5. S -locks allow data to be read concurrently but not modified. With an X -lock, data on a page can be modified (with INSERT , UPDATE , or DELETE ), but concurrent access is not allowed. U -locks enable X -locks to be queued, whereas S -locks exist on data that must be modified.

Table 23.5. Page Locks

Lock	Meaning	Access Acquired	Access Allowed to Others
S	SHARE	Read only	Read only
U	UPDATE	Read with intent to update	Read only
X	EXCLUSIVE	Update	No access

As with table space locks, concurrent page locks can be acquired but only with compatible page locks. The compatibility matrix for page locks is shown in Table 23.6.

Table 23.6. Page Lock Compatibility Matrix

Locks for PGM2	Locks for PGM1
	S	U	X
S	Yes	Yes	No
U	Yes	No	No
X	No	No	No

When are these page locks taken? Page locks can be acquired only under the following conditions:

• The DDL for the object requesting a lock specifies LOCKSIZE PAGE or LOCKSIZE ANY .

• If LOCKSIZE ANY was specified, the NUMLKTS threshold or the table space LOCKMAX specification must not have been exceeded. You learn more about these topics later in this section.

• If ISOLATION(RR) was used when the plan was bound, the optimizer might decide not to use page locking.

If all these factors are met, page locking progresses as outlined in Table 23.7. The type of processing in the left column causes the indicated page lock to be acquired for the scope of pages identified in the right column. DB2 holds each page lock until it is released as specified in the ISOLATION level of the plan requesting the particular lock.

NOTE

Page locks can be promoted from one type of lock to another based on the type of processing that is occurring. A program can FETCH a row using a cursor with the FOR UPDATE OF clause, causing a U -lock to be acquired on that row's page. Later, the program can modify that row, causing the U -lock to be promoted to an X -lock.

Table 23.7. How Page Locks Are Acquired

Type of Processing	Page Lock Acquired	Pages Affected
SELECT/FETCH	S	Page by page as they are fetched
OPEN CURSOR for SELECT	S	All pages affected
SELECT/FETCH FOR UPDATE OF	U	Page by page as they are fetched
UPDATE	X	Page by page
INSERT	X	Page by page
DELETE	X	Page by page