Example 9 33   Resizing the transactional log

db2 update db cfg for sample using LOGFILSIZ 5120

db2 update db cfg for sample using LOGPRIMARY 20

Locking

 is the mechanism that the database manager uses to control concurrent 

access to data in the database by multiple applications. 

Each database has its own list of locks (a structure stored in memory, which 

contains the locks held by all applications concurrently connected to the 

database). 

The size of the lock list is controlled by the 

LOCKLIST

 database parameter. The 

default storage for 

LOCKLIST

 is 50 pages (200 KB) for Windows and 100 pages 

(400 KB) for Linux and

UNIX. On 32 bit platforms, each lock requires 36 or 72 

bytes of the lock list, depending on whether other locks are held on the object or 

not. For the default values, the maximum of 5688 (Windows) or 11377 (Linux and 

UNIX) locks can be allocated as shown in Figure 9 8.

LOCKLIST   default value

Page size

100 * 4096 / 36 = 11377

Minimum space needed 

to acquire 1 lock.

Maximum number of locks. Keep in 

mind that database manager may 

also acquire locks for internal 

use.

Figure 9 8   Maximum number of locks available for default settings on Linux

When the maximum number of lock requests has been reached the database 

manager will replace existing row level locks with table locks (

lock escalation

). 

This operation will reduce the requirements for locks space, because 

transactions will hold only one lock on the entire table instead of many locks on 

every row. Lock escalation has a negative performance impact because it 

reduces concurrency on shared objects. Other transactions must wait until the 

transaction holding the table lock commits or rollbacks work.

304 

MySQL to DB2 UDB Conversion Guide