![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Locks and performance
Because DB2 implicitly acquires locks as they are needed, aside from using
the LOCK TABLE statement (and with DB2 for Linux,
UNIX, and Windows, the ALTER TABLE statement) to
force DB2 to acquire table-level locks, locking is pretty much out of your
control. There are several factors that can influence how locking
affects performance. These factors include:
- Lock compatibility
- Lock conversion
- Lock escalation
- Lock waits and timeouts
- Deadlocks
Knowing what these factors are and understanding how they affect performance can assist you in designing database applications that work well in multi-user database environments.
If the state of a lock placed on a data resource by one transaction is such that another lock can be placed on the same resource by another transaction before the first lock is released, the locks are said to be compatible. And any time one transaction holds a lock on a data resource and another transaction attempts to acquire a lock on the same resource; DB2 will examine each lock's state and determine whether they are compatible. Table 3 contains a lock compatibility matrix that identifies which locks are compatible.
Table 3. Lock compatibility matrix
| Lock requested by second transaction | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Lock State | IN | IS | NS | S | IX | SIX | U | X | Z | NW | ||
| Lock held by first transaction | IN | Yes | Yes | Yes | Yes | Yes | Yes | Yes | Yes | No | Yes | |
| IS | Yes | Yes | Yes | Yes | Yes | Yes | Yes | No | No | No | ||
| NS | Yes | Yes | Yes | Yes | No | No | Yes | No | No | Yes | ||
| S | Yes | Yes | Yes | Yes | No | No | Yes | No | No | No | ||
| IX | Yes | Yes | No | No | Yes | No | No | No | No | No | ||
| SIX | Yes | Yes | No | No | No | No | No | No | No | No | ||
| U | Yes | Yes | Yes | Yes | No | No | No | No | No | No | ||
| X | Yes | No | No | No | No | No | No | No | No | No | ||
| Z | No | No | No | No | No | No | No | No | No | No | ||
| NW | Yes | No | Yes | No | No | No | No | No | No | No | ||
| Yes — Locks are compatible. Lock request is granted immediately No — Locks are not compatible. Requesting transaction must wait for the held lock to be released or for a lock timeout to occur before the lock request can be granted. | ||||||||||||
| Lock states: IN — Intent None IS — Intent Share NS — Scan Share S — Share IX — Intent Exclusive SIX — Share With Intent Exclusive U — Update X — Exclusive Z — Super Exclusive NW — Next Key Weak Exclusive | ||||||||||||
| Adapted from Table 1, found under Lock type compatibility in the IBM DB2 10.1 Information Center for Linux, UNIX, and Windows. (http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.perf.doc/doc/r0005274.html) | ||||||||||||
If a transaction holding a lock on a resource needs to acquire a more restrictive lock on that resource, rather than release the old lock and acquire a new one, DB2 will attempt to change the state of the lock being held to the more restrictive state needed. The action of changing the state of an existing lock is known as lock conversion (DB2 for Linux, UNIX, and Windows) or lock promotion (DB2 for z/OS); lock conversion/promotion occurs because a transaction is only allowed to hold one lock on any given resource. Figure 7 illustrates how lock conversion/promotion works.
Figure 7. Lock conversion/promotion changes a lock being held
In most cases, lock conversion/promotion is performed on row-level locks, and the process is fairly straightforward. For example, if an Update (U) lock is held and an Exclusive (X) lock is needed, the Update (U) lock will be converted/promoted to an Exclusive (X) lock, but that's not always the case when it comes to Share (S) and Intent Exclusive (IX) locks. Since neither lock is considered more restrictive than the other, if one of these locks is held and the other is requested, the lock that is held is converted/promoted to a Share With Intent Exclusive (SIX) lock. With all other locks, the state of the current lock is changed to the lock state being requested—provided the lock state being requested is a more restrictive state. (Lock conversion/promotion only occurs if the lock that is held can increase its restriction.) Once a lock has been converted, it stays at the highest level attained until the transaction holding the lock is terminated and the lock is released.
When a connection to a database is first established, a specific amount of memory
is set aside to hold a structure that DB2 uses to manage locks. This structure,
known as the lock list, is where locks that are held by every active
transaction are stored after they are acquired. (The actual amount of memory
that gets set aside for the lock list is controlled through the
locklist database configuration parameter.)
Because a limited amount of memory is available, and because this memory must be
shared by every active transaction, DB2 imposes a limit on the amount of space
each transaction is allowed to consume in the lock list. (This limit is
controlled by way of the maxlocks database configuration parameter). To
prevent a database agent (working on behalf of a transaction) from
exceeding its lock list space limitations, a process known as lock
escalation is performed whenever too many locks (regardless of their
type) have been acquired on behalf of a single transaction. During lock
escalation, space in the lock list is freed by replacing several row-level locks
with a single table-level lock. Figure 8 illustrates
how lock escalation works.
Figure 8. Lock escalation replaces several individual row-level locks with a single table-level lock
So just how does lock escalation work? When a transaction requests a lock and a database's lock list is full, one of the tables associated with the transaction requesting the lock is selected, a table-level lock is acquired on behalf of the transaction, and all row-level locks for that table are released to create space in the lock list. The table-level lock is then added to the lock list, and if the lock list still does not have the storage space needed to acquire the request lock, another table is selected and the process is repeated until enough free space is made available — only then will the requested lock be acquired (at which point, the transaction will be allowed to continue). If the lock list space needed is still unavailable (after all of the transaction's row-level locks have been escalated), an error is generated, all changes made to the database by the transaction are rolled back, and the transaction is gracefully terminated.
NOTE: Use of the LOCK TABLE statement does not
prevent normal lock escalation from occurring, but it may reduce
the frequency at which lock escalations take place.
As we have seen, anytime a transaction holds a lock on a particular resource, other concurrently running transactions may be denied access to that resource until the transaction that holds the lock is terminated (in which case, all locks that were acquired on behalf of the transaction are released). Consequently, without some sort of lock timeout mechanism in place, one transaction might wait indefinitely for a lock that is held by another transaction to be released. And unfortunately, if either transaction were to be terminated prematurely by another user or application, data consistency could be compromised.
To prevent situations like these from occurring, an important feature known as
lock timeout detection has been incorporated into DB2. When used,
this feature prevents transactions from waiting indefinitely for a lock to be
released. By assigning a value to the locktimeout parameter in the
appropriate database configuration file, you can control when lock timeout
detection occurs. This parameter specifies the amount of time that any
transaction will wait to obtain a requested lock; if the desired lock is not
acquired within the time interval specified, all changes made to the database by
the transaction are rolled back and the transaction is gracefully
terminated.
NOTE: By default, the locktimeout configuration parameter is set to
-1, which means that transactions will wait
indefinitely to acquire the locks they need. In many cases, this value
should be changed to something other than the default value. In
addition, applications should be written such that they capture any
timeout (or deadlock) SQL return code returned by DB2 and respond
appropriately.
In many cases, the problem of one transaction waiting indefinitely for a lock can be avoided by using Currently Committed semantics and specifying a lock timeout. But that is not the case when lock contention results in a situation that is known as a deadlock. The best way to illustrate how a deadlock can occur is by example: Suppose Transaction 1 acquires an Exclusive (X) lock on Table A, and Transaction 2 acquires an Exclusive (X) lock on Table B. Now, suppose Transaction 1 attempts to acquire an Exclusive (X) lock on Table B, and Transaction 2 attempts to acquire an Exclusive (X) lock on Table A. We have already seen that processing by both transactions will be suspended until their second lock request is granted. Because neither lock request can be granted until one of the owning transactions releases the lock it currently holds (by performing a commit or rollback operation), and because neither transaction can perform a commit or rollback operation because they both are waiting to acquire locks, a deadlock situation has occurred. Figure 9 illustrates this scenario.
Figure 9. Deadlock
A deadlock is more precisely referred to as a deadlock cycle because the transactions involved form a circle of wait states. Each transaction in the circle waits for a lock held by another transaction in the circle to be released (see Figure 9). When a deadlock cycle occurs, all transactions involved will wait indefinitely for a lock to be released unless some outside agent steps in and breaks the cycle. With DB2, this agent is a background process known as the deadlock detector, and its sole responsibility is to locate and resolve any deadlocks found in the locking subsystem.
Each database has its own deadlock detector, which is activated as part of the database initialization process. Once activated, the deadlock detector stays "asleep" most of the time, but wakes up at preset intervals and examines the locking subsystem to determine whether a deadlock situation exists. Normally, the deadlock detector wakes up, sees that there are no deadlocks in the locking subsystem, and goes back to sleep. If the deadlock detector discovers a deadlock cycle, it randomly selects one of the transactions involved to roll back and terminate; the transaction chosen (referred to as the victim process) is then sent an SQL error code, and every lock it had acquired is released. The remaining transaction(s) can then proceed because the deadlock cycle has been broken. It is possible, but unlikely, that more than one deadlock cycle exists in a database's locking subsystem. If several deadlock cycles exist, the detector locates each one and terminates one of the offending transactions in the same manner, until all deadlock cycles have been broken. Eventually, the deadlock detector goes back to sleep, only to wake up again at the next predefined interval and examine the locking subsystem again.
While most deadlock cycles involve two or more resources, a special type of deadlock, known as a conversion deadlock, can occur on one individual resource. Conversion deadlocks occur when two or more transactions that already hold compatible locks on an object request new, incompatible locks on that same object. This typically takes place when two or more concurrent transactions search for rows in a table by performing an index scan, and then try to modify one or more of the rows retrieved.
