11.5. DB2 Locking DB2 uses various levels of locking to provide concurrent data access and at the same time protect the data. Depending on the operations requested, the database manager can acquire locks on databases, buffer pools, table spaces, tables, table blocks, and table rows. Locks are acquired implicitly by DB2 according to the semantics defined by the isolation level. 11.5.1. Lock Attributes During normal data manipulation processing, DB2 uses row-level locking by default. You can override this rule to acquire table-level locking instead. The ALTER TABLE statement with the LOCKSIZE option forces DB2 to place a table lock whenever the table is accessed. The statement will look like this: ALTER TABLE employee LOCKSIZE TABLE
This setting is retained until you execute: ALTER TABLE employee LOCKSIZE ROW
The only objects that you can explicitly lock are databases, tables, and table spaces. To explicitly lock a database, use the CONNECT statement with the appropriate lock mode. For example: CONNECT TO sample IN EXCLUSIVE MODE
This causes an exclusive lock to be applied to the database. It prevents concurrent application from executing any operations at the database. This lock mode is useful when exclusive administrative tasks must be performed. You can also connect to the database in SHARE MODE, which allows other concurrent connections to the database but prevents other users from connecting in exclusive mode. NOTE When you need to perform exclusive administrative tasks at the instance, rather than the database level, use the start database manager admin mode command as explained in Chapter 5, Understanding the DB2 Environment, DB2 Instances, and Databases.
Table spaces of a particular table can be quiesced. Quiesing a table space is like locking a table space so that administrative tasks (e.g., a load operation) can be performed. With the different quiesce modes, shown in Figure 11.18, DB2 obtains different types of locks for the table and its associated table space(s). The syntax diagram of the quiesce tablespaces for table command is presented in Figure 11.18. Figure 11.18. Syntax diagram of the quiesce tablespaces for table command >>-QUIESCE TABLESPACES FOR TABLE--+-tablename--------+----------> '-schema.tablename-' >--+-SHARE------------+---------------------------------------->< +-INTENT TO UPDATE-+ +-EXCLUSIVE--------+ '-RESET------------'
If you have quiesced the table spaces with a restrictive mode, access to tables within those tablespaces are not allowed. For example, this command: quiesce tablespaces for table employee exclusive
puts superexclusives lock on the table space where table employee is stored, and on the table employee. The state of the table space changes to QUIESCED EXCLUSIVE. No other access to the table spaces is allowed. This means that access to another table that is stored in the same table space is not allowed. You will receive the following error. SQL0290N Table space access is not allowed SQLSTATE=55039
To unquiesce the table space, issue the same quiesce tablespaces for table command but with the reset option. You can also lock a table explicitly with the LOCK TABLE statement. Similarly, different lock modes are available as shown in Figure 11.19. The LOCK TABLE statement locks the specified table until the transaction is completed. Figure 11.19. Syntax diagram of the LOCK TABLE statement >>-LOCK TABLE--+-table-name-+--IN--+-SHARE-----+--MODE--------->< '-nickname---' '-EXCLUSIVE-'
Each lockable object can be locked in a different mode; this represents the type of access allowed for the lock owner. They also control the type of access permitted for concurrent users of the locked object. 11.5.1.1 Table-Level Lock Modes Table and row locks are the most commonly used types of locks. Figure 11.20 shows the table-level lock modes. The table lock modes IN, IS, IX, and SIX are used to support row-level locking. An application requires an IN lock on the table before it can perform an uncommitted read. The IS, IX, and SIX locks permit row-level locking while preventing more exclusive locks on the table by other applications. Figure 11.20. Table Lock Mode Compatibility Chart 
The other table lock modesS, U, X, and Zare strict table locking and do not use row-level locking. For example, if an application holds an X lock on a table, the lock owner can read or update any data in the table but cannot obtain a row lock. Refer to Table 11.4 for a summary of all table lock modes. Table 11.4. Lock Modes SummaryLock Mode | Buffer Pool | Table Space | Table Block | Table | Row | Description |
|---|
IN (Intent None) | | Y | Y | Y | | The lock owner can read any data in the object, including uncommitted data, but cannot update any of it. Other concurrent applications can read or update the table. | IS (Intent Share) | | Y | Y | Y | | The lock owner can read data in the locked object but cannot update its data. Other applications can read or update the object. | NS (Next Key Share) | | | | | Y | The lock owner and all concurrent applications can read, but not update, the locked row. This lock is acquired on rows of a table where the isolation level of the application is either RS or CS. NS lock mode is not used for next-key locking. It is used instead of S mode during CS and RS scans to minimize the impact of next-key locking on these scans. | S (Share) | | | Y | Y | Y | The lock owner and all concurrent applications can read but not update the locked data. | IX (Intent eXclusive) | | Y | Y | Y | | The lock owner and concurrent applications can read and update data. Other concurrent applications can both read and update the table. | SIX (Share with Intent eXclusive) | | | Y | Y | | The lock owner can read and update data. Other concurrent applications can read the table. | U (Update) | | | Y | Y | Y | The lock owner can update data. Other units of work can read the data in the locked object but cannot update it. | NW (Next Key Weak Exclusive) | | | | | Y | When a row is inserted into an index, an NW lock is acquired on the next row. The lock owner can read but not update the locked row. This lock mode is similar to an X lock, except that it is also compatible with W and NS locks. | X (eXclusive) | Y | | Y | Y | Y | The lock owner can both read and update data in the locked object. Only uncommitted read applications can access the locked object. | W (Weak Exclusive) | | | | | Y | This lock is acquired on the row when a row is inserted into a table. The lock owner can change the locked row. This lock is used during insertion into a unique index to determine if a duplicate value is found. This lock is similar to an X lock except that it is compatible with the NW lock. Only uncommitted read applications can access the locked row. | Z (Super Exclusive) | | Y | | Y | | This lock is acquired on a table in certain conditions, such as when the table is altered or dropped, an index on the table is created or dropped, and for some types of table reorganization. No other concurrent application can read or update the table. |
11.5.1.2 Row Lock Modes Row lock modes require support of some kind of table lock. The minimum table locks DB2 must acquire before obtaining a row lock are listed in Figure 11.21. For example, an application can lock a row in Share mode if it also holds an IS lock on the table. Figure 11.21. Row Lock Mode Compatibility Chart 
Besides table and row locks, there are other types of objects DB2 locks. Table 11.4 presents a summary of lockable objects and lock modes. Y means that the lock mode applies to that type of object; a dash means that it does not apply. NOTE If you use multidimensional clustering (MDC) tables, you can impose table block locks.
Figures 11.22 and 11.23 (from the DB2 manual Administration Guide: Performance) present lock mode compatibility charts for table and row locks respectively. NO means the requesting application must wait for the lock to be released and YES means the lock can be granted. Figure 11.22. Table lock mode compatibility chart 
Figure 11.23. Row lock mode compatibility chart 
Let's use an example to demonstrate how to use the charts. Assume that application A is holding an IX lock on a table. Looking at the compatibility chart in Figure 11.22, you can see that another application can only lock the same table in IN, IS, or IX mode as highlighted with the circles in the figure. If application B requests an IS lock at the table level and tries to read some rows in the table, use the row lock chart in Figure 11.23 to determine the compatibility of concurrent data access. As long as application A holds locks that are compatible with the lock mode application B is requesting, both applications can work concurrently with each other. For example, if application A is holding a U lock on a row, application B can only obtain an S or NS lock (refer to compatibility values circled in Figure 11.23). Otherwise, application B must wait for application A to complete its transaction. 11.5.2. Lock Waits A discussion of DB2 locking mechanisms is not really complete if lock wait and deadlock scenarios are not covered. As the number of concurrent applications increases, the possibility of running into situations with incompatible locks is relatively higher. In the examples used to describe the behavior of the different isolation levels, you saw how an application might have to wait for a lock. This is known as lock wait. Deadlocks are discussed in the next section. It is generally not possible to totally avoid lock wait as concurrency increases. After all, DB2 relies on the locking mechanism to keep data integrity. However, you should minimize lock waits and each wait length as much as possible. They put a hold on processing the statements, hence, they affect performance. Note that you should minimize lock waits and the duration of each wait. You can use the database configuration parameter called LOCKTIMEOUT to define how long an application is going to wait for a lock. By default, LOCKTIMEOUT is set to -1, which stands for infinite wait. We recommended setting it to a finite number that works well with your application and business requirement. If an application reaches the LOCKTIMEOUT value, it receives the following message: [View full width] SQL0911N The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".
Reason code 68 indicates the transaction is rolled back due to a lock timeout. LOCKTIMEOUT applies to any application connecting to the database. In some cases, you may want to set the timeout duration for a given application rather than providing the same value for all applications. You can directly control how long an individual application will wait for a lock using the set current lock timeout command. This command overrides the LOCKTIMEOUT parameter and stores the new value in the DB2 special register CURRENT LOCK TIMEOUT. This would be useful, for example, in a system where there is a mixed workload of long-running reports as well as update batch jobs. Figure 11.24 gives the syntax of the command. Figure 11.24.  Syntax diagram of the set current lock timeout command .-CURRENT-. .-=-. >>-SET--+---------+--LOCK TIMEOUT--+---+------------------------> >--+-WAIT-----------------------+------------------------------>< +-NOT WAIT-------------------+ +-NULL-----------------------+ | .-WAIT-. | +-+------+--integer-constant-+ '-host-variable--------------'
You can set the lock timeout period to the following. WAIT specifies that the application will wait infinitely for a lock. NOT WAIT specifies that the application will not wait for locks that cannot be obtained. NULL specifies that the application will use the value of the LOCKTIMEOUT database configuration parameter as the duration to wait for locks. WAIT integer_constant specifies an integer value of how long the application will wait for a lock. The value -1 will have the same behavior as WAIT (without an integer value). A value of 0 is equivalent to specifying NOT WAIT.
To validate the value of the CURRENT LOCK TIMEOUT special register, you can use the VALUES statement: VALUES CURRENT LOCK TIMEOUT
11.5.3. Deadlocks There is another undesirable lock scenario to avoid: deadlock. Deadlock is a situation when two applications are waiting for locks that the other is holding. Consider the situation in Figure 11.25. Figure 11.25. Example of a deadlock situation 
(1) App A starts a transaction and updates the record where empno = 100. The record is locked by App A with an X lock. (2) App B starts a transaction and updates the record where empno = 105. The record is locked by App B with an X lock. (3 and 4) In the same transaction as (1), App A queries the table and scans for empno = 106. Assume that DB2 chooses to use a table scan to read each empno and see if it is 106. To perform a read, App A needs an S lock on every row. An S lock cannot be obtained for empno = 105 because the row is locked by App B with an incompatible lock, X. (5 and 6) Similarly, App B is executing the same program to search for empno = 101. Assume that it also has to scan all the rows. App B will stop and wait for empno = 100 that is being locked by App A. Apps A and B in this example now encounter a deadlock situation. DB2 has a deadlock detector running around the clock to identify any deadlock. Once one is detected, it will randomly pick a victim and roll back its transaction. By rolling back, all the locks that particular application is holding will be released. This allows the other application that is involved in the deadlock to complete its processing. The application that is rolled back will receive the message: [View full width] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2".
Reason code 2 means that the transaction is rolled back due to a deadlock. The failed user application is then responsible to report the error and retry the transaction if necessary. The deadlock detector is activated periodically as determined by the DLCHKTIME database configuration parameter. The default value for this parameter is 10,000 milliseconds (10 seconds). To avoid deadlocks or any unnecessary lock waits, you need to understand your application. Design the application and tune the database in a way that the application will only read the data it requires. Figure 11.25 shows an example of two applications manipulating data on different rows. Why would it still encounter a deadlock? The key to this problem is that DB2 scans every empno value to see if the row qualifies the queries. If only a portion of the values are scanned, the applications may not run into a deadlock. This can be achieved by creating proper indexes and maintaining current database statistics so DB2 can choose a more efficient data access plan. A deadlock may still occur even with proper indexing and database maintenance. In that case, you can make use of a new feature lock deferral, which is discussed next. 11.5.4. Lock Deferral You can enable lock deferral for CS or RS isolation level scans with the DB2_EVALUNCOMMITTED registry variable. DB2 evaluates the row before trying to lock it. To enable this feature issue the command: db2set DB2_EVALUNCOMMITTED=YES
To disable it issue: db2set DB2_EVALUNCOMMITTED=
Figure 11.26 shows that lock deferral no longer requires App A to put an S lock on empno = 105. App A can then read the empno = 106 row. Similar logic applies to App B. Figure 11.26. Deadlock problem resolved with EVALUNCOMMITTED enabled 
11.5.5. Lock Escalation When DB2 acquires a lock on an object, it allocates memory for each lock from the database shared memory area called the locklist. A tunable database configuration parameter by the same name lets you indicate the maximum storage allowed for locks in each database. To resize the locklist, update the LOCKLIST parameter with a new value in units of 4K. Regardless of the type of lock, each lock uses about 36 bytes of memory on 32-bit DB2 instances and 56 bytes of memory on 64-bit DB2 instances. As the number of locks being held by all applications connected to the database increases, it is possible that the locklist will get full. When this happens, DB2 attempts to free memory by allocating a table lock and releasing the row locks. This internal operation is called lock escalation. Lock escalation degrades performance because it can significantly reduce database concurrency. When you monitor your database, you should ideally see very few to no escalations. It is important to tune the LOCKLIST parameter appropriately so that lock escalations are avoided. The MAXLOCKS database configuration parameter also has a direct effect on lock escalation. MAXLOCKS defines the percentage of the total locklist permitted to be allocated to a single application. Proper configuration of MAXLOCKS prevents any one application from using up all the memory available in the locklist. When the amount of locks an application holds reaches the MAXLOCKS percentage, DB2 escalates the row locks of the particular application to a table lock. The table with the most row locks is escalated first. Lock escalation continues until the percentage of the locklist held is below the value of MAXLOCKS. The database manager determines which locks to escalate by looking through the locklist for the application and finding the table with the most row locks. If after replacing these with a single table lock, the MAXLOCKS value is no longer exceeded, lock escalation will stop. If not, escalation continues until the percentage of the locklist held is below the value of MAXLOCKS. The MAXLOCKS parameter multiplied by the MAXAPPLS parameter cannot be less than 100. As the number of row locks being held increases, the chance of locking escalations occurring also increases. Take this into consideration when choosing isolation levels. For example, the RR isolation level locks all the rows in the result set as well as the rows referenced to build the result set. With this isolation level you should choose an appropriate value for your MAXLOCKS and LOCKLIST parameters. | 