Locking
| The one thing that all four isolation levels have in common is that they control how data is accessed by concurrent transactions through the use of locks. So just what is a lock? A lock is a mechanism used to associate a data resource with a single transaction, for the sole purpose of controlling how other transactions interact with that resource while it is associated with the transaction that has it locked. (The transaction that has a data resource associated with it is said to "hold" or "own" the lock.) Essentially, locks in a database environment serve the same purpose as they do in a house or a car: They determine who can and cannot gain access to a particular resource ”in the case of a data resource, this is one or more tablespaces, tables, and/or rows. The DB2 Database Manager imposes locks to prohibit "owning" transactions from accessing uncommitted data that has been written by other transactions and to prevent other transactions from making data modifications that might adversely affect the owning transaction. When an owning transaction is terminated (by being committed or by being rolled back), any changes made to the resource that was locked are made permanent or removed, and all locks on the resource that had been acquired by the owning transaction are released. Once unlocked, a resource can be locked again and manipulated by another active transaction. Figure 7-1 illustrates the principles of transaction/ resource locking. Figure 7-1. How DB2 Universal Database prevents uncontrolled concurrent access to a resource through the use of locks. Lock Attributes and Lock StatesAll DB2 Universal Database locks have the following basic attributes:
How Locks Are AcquiredExcept for occasions where the Uncommitted Read isolation level is used, it is never necessary for a transaction to explicitly request a lock. That's because the DB2 Database Manager implicitly acquires locks as they are needed (and once acquired, these locks remain under the DB2 Database Manager's control until they are no longer needed). By default, the DB2 Database Manager always attempts to acquire row-level locks. However, it is possible to control whether the DB2 Database Manager will always acquire row-level locks or table-level locks on a specific table resource by executing a special form of the ALTER TABLE SQL statement. The syntax for this form of the ALTER TABLE statement is: Table 7-2. Lock States
ALTER TABLE [ TableName ] LOCKSIZE [ROW TABLE] where:
For example, if executed, the SQL statement ALTER TABLE EMPLOYEE LOCKSIZE TABLE would force the DB2 Database Manager to acquire table-level locks for every transaction that accesses the table named EMPLOYEE. On the other hand, if the SQL statement ALTER TABLE EMPLOYEE LOCKSIZE ROW was executed, the DB2 Database Manager would attempt to acquire row-level locks (which is the default behavior) for every transaction that accesses the table named EMPLOYEE. But what if you don't want every transaction that works with a particular table to acquire table-level locks? What if you instead only want one specific transaction to acquire table-level locks, and you want all other transactions to acquire row-level locks when working with that table? In this case, you leave the default locking behavior alone (in which case row-level locking is used), and you use the LOCK TABLE SQL statement to acquire a table-level lock for the appropriate individual transaction. The syntax for the LOCK TABLE statement is: LOCK TABLE [ TableName ] IN [SHARE EXCLUSIVE] MODE where:
As you can see, the LOCK TABLE statement allows a transaction to acquire a table-level lock on a particular table in one of two modes: SHARE mode and EXCLUSIVE mode. If a table is locked using the SHARE mode, a table-level Share (S) lock is acquired on behalf of the requesting transaction, and other concurrent transactions are allowed to read, but not change the data stored in the locked table. On the other hand, if a table is locked using the EXCLUSIVE mode, a table-level Exclusive (X) lock is acquired, and other concurrent transactions can neither access nor modify data stored in the locked table. For example, if executed, the SQL statement LOCK TABLE EMPLOYEE IN SHARE MODE would acquire a table-level Share (S) lock on the EMPLOYEE table on behalf of the current transaction (provided no other transaction holds a lock on this table), and other concurrent transactions would be allowed to read, but not change, the data stored in the table. On the other hand, if the SQL statement LOCK TABLE EMPLOYEE IN EXCLUSIVE MODE were executed, a table-level Exclusive (X) lock would be acquired, and no other concurrent transaction would be allowed to read or modify data stored in the EMPLOYEE table. Which Locks Are AcquiredAlthough it is possible to control whether the DB2 Database Manager will acquire row-level locks or table-level locks, it is not possible to control what type of lock will actually be acquired for a given transaction. Instead, the DB2 Database Manager implicitly makes that decision by analyzing the transaction to determine what type of processing it has been designed to perform. For the purpose of deciding which particular type of lock is needed for a given situation, the DB2 Database Manager places all transactions into one of the following categories:
The characteristics used to assign transactions to these categories, along with the types of locks that are acquired for each, are shown in Table 7-3. It is important to keep in mind that some transactions are actually composed of two or more transaction types. For example, a transaction that contains an SQL statement that performs an insert operation against a table using the results of a subquery actually does two different types of processing: Read-Only and Change. Thus, the locks needed for the resources referenced in the subquery are determined by the rules for Read-Only transactions, while the locks needed for the target table of the insert operation are determined by the rules for Change transactions. Table 7-3. Types of Transactions Available and Their Associated Locks
Locks and PerformanceAlthough the DB2 Database Manager implicitly acquires locks as they are needed and, aside from using the ALTER TABLE and LOCK TABLE SQL statements to force the DB2 Database Manager to acquire table-level locks, locking is out of your control, there are several factors that can affect locking that you need to be aware of. These factors include:
Knowing what these factors are and understanding how they can affect overall performance can assist you in designing database applications that work well in multiuser database environments and, indirectly, give you more control over how locks are used. Lock compatibilityIf 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 acquired is released, the two locks (or lock states) are said to be compatible. Anytime one transaction holds a lock on a data resource and another transaction attempts to acquire a lock on the same resource, the DB2 Database Manager will examine the two lock states to determine if they are compatible. If both locks are compatible, the second lock is acquired on behalf of the requesting transaction. On the other hand, if the locks are incompatible, the requesting transaction must wait for the owning transaction to release the lock it holds before it can gain access to the resource and acquire the lock that is needed. This is known as a lock wait event. When a lock wait event occurs, the transaction attempting to access the locked resource simply stops execution until the owning transaction terminates (and releases the incompatible lock) or until a lock timeout event occurs (we will look at lock timeout events shortly). Table 7-4 contains a lock compatibility matrix that identifies which locks are compatible and which are not. Lock conversionIf a transaction holds a lock on a resource and needs to acquire a more restrictive lock on the same resource, the DB2 Database Manager will attempt to change the state of the existing lock to the more restrictive state, rather than acquire a second lock. The action of changing the state of an existing lock to a more restrictive state is known as lock conversion. Lock conversion occurs because a transaction can hold only one lock on a specific data resource at any given time. Figure 7-2 illustrates a simple lock conversion process. Figure 7-2. A simple lock conversion scenario ”a Share (S) lock is converted to an Exclusive (X) lock. Table 7-4. Lock Compatibility Matrix
In most cases, lock conversion is performed on row-level locks, and the conversion 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 to an Exclusive (X) lock. However, Share (S) locks and Intent Exclusive (IX) locks are special cases, since neither lock is considered more restrictive than the other; if one of these locks is held and the other is requested, the held lock is converted to a Share With Intent Exclusive (SIX) lock. With all other conversions, the lock 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 only occurs if a held lock 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. Lock escalationWhen a connection to a database is first established, a specific amount of memory is set aside to hold a structure that DB2 UDB uses to manage locks. This structure is called the lock list, and this is where the locks held by all applications concurrently connected to a database are stored after they are acquired. (The actual amount of memory that gets set aside for the lock list is determined by the locklist database configuration parameter.) Because a limited amount of memory is available, and because this memory must be shared by everyone, the DB2 Database Manager imposes a limit on the amount of space in the lock list each transaction can use for its own locks (which is defined by the maxlocks database configuration parameter). To prevent a specific database agent from exceeding its 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 converting several row-level locks into a single table-level lock. Figure 7-3 illustrates a simple lock escalation process. Figure 7-3. Lock escalation ”several individual row-level locks are changed to a single table-level lock. So just how does lock escalation work? When a transaction requests a lock and the database's lock list is full, one of the tables associated with the transaction is selected, a table-level lock is acquired, all row-level locks for that table are released to create space, and the table-level lock acquired is added to the lock list. If this process does not free up the storage space needed to acquire the lock that was requested, another table is selected and the process is repeated until enough free space is made available ”only then will the requested lock be acquired and the transaction be allowed to resume execution. However, if the lock list space needed is still unavailable after all the transaction's row-level locks have been escalated, an SQL error code is generated, all changes that have been made to the database since the transaction was initiated are rolled back, and the transaction is gracefully terminated. If a database is configured properly, lock escalation events rarely occur.
Lock waits and timeoutsAny time a transaction holds a lock on a particular resource (tablespace, table, or row), other transactions may be denied access to that resource until the owning transaction terminates and frees all locks it has acquired. Because of this behavior, without some sort of lock timeout detection mechanism in place, a transaction might wait indefinitely for a lock to be released. For example, if a transaction in one user's application was waiting for a lock being held by a transaction in another user's application to be released, and the other user left their workstation without performing some interaction that would have allowed their application to terminate the owning transaction and release all locks held, the application waiting for the lock to be released would be unable to continue processing for an indeterminable amount of time. Unfortunately, it would also be impossible to terminate the application that was waiting for the lock to be released without compromising data consistency. To prevent situations like these from occurring, an important feature known as lock timeout detection has been incorporated into the DB2 Database Manager. When used, this feature prevents applications from waiting indefinitely for a lock to be released in an abnormal situation. By assigning a value to the locktimeout configuration parameter in the appropriate database configuration file, you can control when lock timeout detection occurs. This parameter controls the amount of time that any transaction will wait to obtain a requested lock; if the requested lock is not acquired before the time interval specified in the locktimeout configuration parameter has elapsed, the waiting application receives an error message, and the transaction requesting the lock is rolled back. Once the transaction requesting the lock has been rolled back, the waiting application will, by default, be terminated. (This behavior prevents data inconsistency from occurring.)
DeadlocksWe just saw how one transaction can make another transaction wait indefinitely for a lock to be released and how this situation can be resolved by establishing lock timeouts. Unfortunately, there is one situation where contention for locks by two or more transactions cannot be resolved by a lock timeout. This situation is known as a deadlock, and 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. However, 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 have been suspended (and are waiting on locks), a deadlock situation has occurred. Figure 7-4 illustrates this deadlock scenario. Figure 7-4. A deadlock scenario ”Transaction 1 is waiting for Transaction 2 to release its lock on Table B, and Transaction 2 is waiting for Transaction 1 to release its lock on Table A; however, neither transaction can release their respective locks because they have been suspended and are waiting to acquire other locks. 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 7-4). 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 deadlock cycle. With DB2 Universal Database, this agent is a background process, known as the deadlock detector, whose 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, however, the deadlock detector discovers a deadlock cycle in the locking subsystem, it randomly selects one of the transactions in the cycle to roll back and terminate. The transaction chosen receives 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 very unlikely , that more than one deadlock cycle exists in a database's locking subsystem. However, 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 reexamine the locking subsystem. 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. Concurrency and granularityAs we have already seen, any time a transaction holds a lock on a particular resource, other transactions may be denied access to that resource until the owning transaction is terminated. Therefore, row-level locks are usually better than table-level locks, because they restrict access to a much smaller resource. However, because each lock acquired requires some amount of storage space (to hold) and some degree of processing time (to manage), there is usually considerably less overhead involved when a single table-level lock is used, rather than several individual row-level locks. Earlier, we saw that the granularity of locks (i.e. whether row-level locks or table-level locks are used) can be controlled through the use of the ALTER TABLE and LOCK TABLE SQL statements ”the ALTER TABLE statement is used to control granularity at a global level, while the LOCK TABLE statement is used to control granularity at an individual transaction level. (In both cases, only Share (S) locks or Exclusive (X) locks are acquired whenever explicit table-level locking is performed.) So when is it more desirable to control granularity at the global level rather than at an individual transaction level? It all depends on the situation. Suppose you have a read-only table lookup table that is to be accessed by multiple concurrent transactions. Forcing the DB2 Database Manager to globally acquire Share (S) table-level locks for every transaction that attempts to access this table could improve overall performance without negatively influencing other concurrent transactions. On the other hand, suppose you have a table that is to be accessed frequently by read-only transactions and periodically by a single transaction designed to perform basic maintenance. Forcing the DB2 Database Manager to only acquire an Exclusive (X) table-level lock whenever the maintenance transaction executes makes more sense than forcing the DB2 Database Manager to globally acquire Exclusive (X) table-level locks for every transaction that needs to access the table. If the former approach is used, the read-only transactions are locked out of the table whenever the maintenance transaction runs, but in all other situations, they can access the table concurrently while requiring very little locking overhead. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 68