Row Locks

The smallest piece of DB2 data that you can lock is the individual row. The types of row locks that DB2 can take are similar to the types of page locks that it can take. Refer to Table 23.8. S -locks allow data to be read concurrently but not modified. With an X -lock, you can modify data in that row (using 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.8. Row 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

Once again, concurrent row locks can be acquired but only with compatible row locks. Table 23.9 shows the compatibility matrix for row locks.

Table 23.9. Row 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 row locks taken? Row locks can be acquired when the DDL for the object requesting a lock specifies LOCKSIZE ROW. (Although it is theoretically possible for LOCKSIZE ANY to choose row locks, in practice I have yet to see this happen.) Row locking progresses as outlined in Table 23.10. The type of processing in the left column causes the indicated row lock to be acquired for the scope of rows identified in the right column. A row lock is held until it is released as specified by the ISOLATION level of the plan requesting the particular lock.

NOTE

Row 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. Later, the program can modify that row, causing the U -lock to be promoted to an X -lock.

Table 23.10. How Row Locks Are Acquired

Type of Processing	Row Lock Acquired	Rows Affected
SELECT/FETCH	S	Row by row as they are fetched
OPEN CURSOR for SELECT	S	All rows affected
SELECT/FETCH FOR UPDATE OF	U	Row by row as they are fetched
UPDATE	X	Row by row
INSERT	X	Row by row
DELETE	X	Row by row

Page Locks Versus Row Locks

The answer to the question of whether to use page locks or row locks is, of course, "It depends!" The nature of your specific data and applications determines whether page or row locks are most applicable .

The resources required to acquire, maintain, and release a row lock are just about the same as the resources required for a page lock. Therefore, the number of rows per page must be factored into the row-versus-page locking decision. The more rows per page, the more resources row locking will consume. For example, a table space with a single table that houses 25 rows per page can consume as much as 25 times more resources for locking if row locks are chosen over page locks. Of course, this estimate is very rough, and other factors (such as lock avoidance ) can reduce the number of locks acquired, and thereby reduce the overhead associated with row locking. However, locking a row-at-a-time instead of a page-at-a-time can reduce contention . Row locking almost always consumes more resources than page locking. Likewise, if two applications running concurrently access the same data in different orders, row locking might actually decrease concurrent data access.

You must therefore ask these questions:

• What is the nature of the applications that access the objects in question? Of course, the answer to this question differs not only from organization to organization, but also from application to application within the same organization.

• Which is more important, reducing the resources required to execute an application or increasing data availability? The answer to this question will depend upon the priorities set by your organization and any application teams accessing the data.

As a general rule of thumb, favor specifying LOCKSIZE PAGE , as page locking is generally the most practical locking strategy for most applications. If you're experiencing severe contention problems on a table space that is currently using LOCKSIZE PAGE , consider changing to LOCKSIZE ROW and gauging the impact on performance, resource consumption, and concurrent data access. Alternatively, you also might choose to specify LOCKSIZE ANY and let DB2 choose the type of locking to be performed.

NOTE

A possible alternative to row locking is to specify MAXROWS 1 for the table space and use LOCKSIZE PAGE (or LOCKSIZE ANY ), instead of LOCKSIZE ROW .