Developing a locking strategy in the CICS DB2 environment

DB2® uses a lock mechanism to allow concurrency while maintaining data integrity.

In a CICS® environment, concurrency is likely to be high. To give maximum concurrency, you should use row level locking or page locking instead of table space locking. You can do this by defining LOCKSIZE(PAGE), LOCKSIZE(ROW) or LOCKSIZE(ANY) when creating the table space, and by defining the isolation level as cursor stability at BIND time. For more information, see DB2 Universal Database™ for OS/390® and z/OS® SQL Reference.

Specifying LOCKSIZE(ANY) allows DB2 to decide if lock escalation can take place for the table space. The DB2 parameter NUMLKTS is the number of concurrent locks for a table space. If the number of locks exceeds NUMLKTS, lock escalation takes place. NUMLKTS should then be set to a value so high that lock escalation does not take place for normal CICS operation.

If a table space lock is achieved and the plan was bound with RELEASE(DEALLOCATE), the table space is not released at COMMIT time, as only page locks are released. This can mean that a thread and plan monopolizes use of the table space.

Using ANY instead of PAGE gives DB2 the option to use lock escalation for programs that require many page locks before committing. This is typically the case in batch programs. DB2 also provides the ability to lock at the row level rather than the page or tablespace level, thus providing better granularity and reducing lock contention.

You can override DB2 rules for choosing initial lock attributes by using the SQL statement LOCK TABLE in an application program. However, you should avoid using the LOCK TABLE statement, unless it is strictly necessary. If the LOCK TABLE statement is used in an online program, it can prevent the use of RELEASE(DEALLOCATE) and of protected threads. If you do use a LOCK TABLE statement, your plan should use the bind option RELEASE(COMMIT).

In general, it is recommended that you design CICS programs so that:

 Related concepts
Application design and development considerations for CICS DB2
Designing the relationship between CICS applications and DB2 plans and packages
SQL, threadsafe and other programming considerations for CICS DB2 applications
Using JDBC and SQLJ to access DB2 data from Java programs and enterprise beans written for CICS
[[ Contents Previous Page | Next Page Index ]]