Chapter 11

A1:

Increasing MAXLOCKS (the percentage of the lock list that an application can use before escalation occurs) and increasing LOCKLIST can reduce the chances of getting lock escalations.

A2:

When a user performs a read operation, by default DB2 attempts to lock the rows with share locks. Once a share lock is acquired, concurrent users will not be able to make changes to the rows. However, concurrent read is allowed. In this example, Sam may have set the isolation level as UR, so DB2 will not acquire any row lock. This may result in uncommitted data within the same transaction as described here.

A3:

Databases:	CONNECT TO dbname IN EXCLUSIVE MODE
Table spaces:	QUIESCE TABLESPACE FOR TABLE tabname INTENT FOR UPDATE
Tables:	LOCK TABLE tabname IN EXCLUSIVE MODE

A4:

U lock is compatible with S and NS lock only. (Refer to the Row lock mode compatibility chart in Figure 11.23.)

A5:

It means that the current transaction has been rolled back because of a lock timeout.

A6:

It means that the current transaction has been rolled back because of a deadlock.

A7:

There are two database configuration parameters that can cause locks to be escalated. Make sure LOCKLIST is sufficiently large. If LOCKLIST is full, lock escalation will occur. Next check if MAXLOCKS is set appropriately. This value defines the percentage of the total LOCKLIST permitted to be allocated to a single application. If any application holds locks more than this percentage, lock escalation will also occur. If both values are set appropriately, you may want to check the isolation level used in the application or maybe the application design.

A8:

DB2 comes with various troubleshooting and diagnosing tools. Those that are particularly useful for locking-related information are the list applications command, the Snapshot Monitor, snapshot table functions, Event Monitors, Activity Monitors, and the Health Center.

A9:

Application handle 14 is currently in a lock-wait status. It has been waiting for locks for 6507 ms. The dynamic SQL statement that this application is executing is SELECT * FROM org. It is requesting an IS lock on the table org.

A10:

By default, DB2 acquires row-level locking. Unless a lock escalation is required, table lock will not be requested. In this case, it is most likely that the table employee was altered to perform table-level locking rather than row-level locking. If row-level locking is the desired behavior, Mike can issue the following statement:

 ALTER TABLE employee LOCKSIZE ROW 

A11:

D. Cursor With Hold is not a DB2 isolation level.

A12:

B. DB2 does not obtain page-level locks. If lock escalation occurs, a number of row-level locks will be turned into a table-level lock.

A13:

B. If you do not specify the isolation level for your application, DB2 defaults to cursor stability.

A14:

D. Since repeatable read must guarantee the same result set within the same unit of work, it retains locks on all rows required to build the result sets. Therefore, this typically causes many more locks to be held than the other isolation levels.

A15:

A. Uncommitted read obtains an intent none (IN) table-level lock, but does not obtain row-level locks while scanning your data. This allows DB2 to return uncommitted changes since it does not have to wait for locks on rows.

A16:

A. Uncommitted read allows access to changed data that has not been committed.

A17:

C. The read stability isolation level allows new rows to become part of the result set, but does not allow rows to be deleted that are part of the result set until the transaction completes.

A18:

B. The WITH isolation clause will change the isolation level for the statement to the specified value.

A19:

C and E. You can set the lock size for a table to be either an individual row or the whole table. For batch operations that update a large number of rows in a table, it is sometimes beneficial to set the lock size to the table level first.

A20:

B. The option NOT WAIT specifies that the application will not wait for locks that cannot be obtained immediately.