DB2 Version 4.1
Row Level Locking

Row level locking, new to DB2 in version 4.1, provides a finer level of locking granularity than the older options of LOCKSIZE PAGE and LOCKSIZE TABLESPACE.

With row level locking, multiple update programs can update data on the same data page.



Row level locking is specified at the tablespace level, either at the time the tablespace is created, or subsequently by means of the ALTER TABLESPACE statement. If row level locking is specified for a tablespace, all indexes (if any) on the tablespace must be Type 2 Indexes.

Although it sounds like a wonderful feature, the benefits of row level locking are limited. If you have contention problems that row level locking will solve, then by all means give it a try. But if you aren't having contention problems, it makes no sense to use it. Our experience has been that the overhead of row level locking is noticeable but not as bad as some people claim. And modest additional CPU consumption is a small price to pay for the improved concurrency that row level locking offers in specific cases.

The table below shows the result of issuing various SQL statements against table DZ002.EMP after this update has been issued (assuming that the update process holds its lock long enough to cause timeouts):



SQL Statement
LOCKSIZE
ROW		LOCKSIZE
PAGE		LOCKSIZE
TABLESPACE
SELECT LASTNAME
FROM DZ002.EMP
WHERE EMPNO =
'900002'		FailFailFail
SELECT LASTNAME
FROM DZ002.EMP
WHERE EMPNO =
'900002'
WITH UR		OKOKOK
SELECT LASTNAME
FROM DZ002.EMP		FailFailFail
SELECT LASTNAME
FROM DZ002.EMP
WITH UR		OKOKOK
SELECT LASTNAME
FROM DZ002.EMP
WHERE EMPNO =
'900004'		OKFailFail


©Copyright 1996 Chuck Anesi all rights reserved