Locking

The one thing that all four of the isolation levels available have in common is that they all acquire one or more locks. But just what is a lock? A lock is a mechanism that is 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-which can be one or more table spaces, 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 either 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-5 illustrates the principles of transaction/resource locking.

Figure 7-5: How DB2 9 prevents uncontrolled concurrent access to a resource through the use of locks.

Lock Attributes and Lock States

All locks used by DB2 have the following basic attributes:

• Object: This attribute identifies the data resource that is being locked. The DB2 Database Manager implicitly acquires locks on data resources (specifically, table spaces, tables, and rows) whenever they are needed.

• Size: This attribute identifies the physical size of the portion of the data resource that is being locked. A lock does not always have to control an entire data resource. For example, rather than giving an application exclusive control over an entire table, the DB2 Database Manager can elect to give an application exclusive control over one or more specific rows within a table.

• Duration: This attribute identifies the length of time a lock is held. The isolation level used has a significant impact on the duration of a lock. (For example, the lock acquired for a Repeatable Read transaction that accesses 500 rows is likely to have a long duration if all 500 rows are to be updated; on the other hand, the lock acquired for a Cursor Stability transaction is likely to have a much shorter duration.)

• State (or Mode): This attribute identifies the type of access allowed for the lock owner, as well as the type of access permitted for concurrent users of the locked data resource. Table 7-2 shows the various lock states available (along with their effects) in order of increasing control.

Table 7-2: Lock States
Open table as spreadsheet

Lock State (Mode)	Applicable Objects	Lock Owner Access	Concurrent Transaction Access	Other Locks Acquired
Intent None (IN)	Table spaces, Tables	Lock owner can read all data, including uncommitted data, stored in the locked resource; however, lock owner cannot modify data stored in the locked resource. Intent None locks are typically acquired for read-only transactions that have no intention of modifying data (thus, additional locks will not be acquired on the transaction's behalf).	Other transactions can read and modify data stored in the locked resource, however, they cannot delete data stored in the locked resource.	None
Intent Share (IS)	Table spaces, Tables	Lock owner can read all data (excluding uncommitted data) stored in the locked resource; however, lock owner cannot modify data stored in the locked resource. Intent Share locks are typically acquired for transactions that do not convey the intent to modify data (transactions that execute SELECT FOR UPDATE, UPDATE WHERE, or INSERT statements convey the intent to modify data).	Other transactions can read and modify data stored in the locked resource.	If the lock is held on a table, a Share (S) or a Next Key Share (NS) lock is acquired on each row read from that table.
Next Key Share (NS)	Rows	Lock owner can read all data (excluding uncommitted data) stored in the locked resource; however, lock owner cannot modify data stored in the locked resource. Next Key Share locks are typically acquired in place of a Share (S) lock for transactions that are running under the Read Stability (RS) or Cursor Stability (CS) isolation level.	Other transactions can read all data (excluding uncommitted data) stored in the locked resource; however, they cannot modify data stored in the locked resource.	None
Share (S)	Tables, Rows	Lock owner can read all data (excluding uncommitted data) stored in the locked resource; however, lock owner cannot modify data stored in the locked resource. Share locks are typically acquired for transactions that do not convey the intent to modify data (transactions that execute SELECT FOR UPDATE, UPDATE WHERE, or INSERT statements convey the intent to modify data) that are running under the Repeatable Read (RR) isolation level.	Other transactions can read all data (excluding uncommitted data) stored in the locked resource; however, they cannot modify data stored in the locked resource.	Individual rows in a table can be Share (S) locked, provided the table itself is not Share (S) locked. (If the table is Share (S) locked, row-level locks cannot be acquired.)
Intent Exclusive (IX)	Table spaces, Tables	Lock owner can read and modify data stored in the locked resource. Intent Exclusive locks are typically acquired for transactions that convey the intent to modify data (transactions that execute SELECT FOR UPDATE, UPDATE WHERE, or INSERT statements convey the intent to modify data).	Other transactions can read and modify data stored in the locked resource.	When the lock owner works with an Intent Exclusive (IX)-locked table, a Share (S) or a Next Key Share (NS) lock is acquired on every row read from that table, and both an Update (U) and an Exclusive (X) lock is acquired on every row to be modified.
Share With Intent Exclusive (SIX)	Tables	Lock owner can read and modify data stored in the locked resource. Share With Intent Exclusive locks are typically acquired when a transaction holding a Share (S) lock on a resource attempts to acquire an Intent Exclusive (IX) lock on the same resource (or vice versa).	Other transactions can read all data (excluding uncommitted data) stored in the locked resource; however, they cannot modify data stored in the locked resource.	When the lock owner works with a Share With Intent Exclusive (SIX) locked table, an Exclusive (X) lock is acquired on every row in that table that is to be modified.
Update (U)	Tables, Rows	Lock owner can modify all data (excluding uncommitted data) stored in the locked resource; however, lock owner cannot read data stored in the locked resource. Update locks are typically acquired for transactions that modify data with INSERT, UPDATE, or DELETE statements.	Uncommitted data) stored in the locked resource; however, they cannot modify data stored in the locked resource.	An Update (U) locked table, an Exclusive (X) lock is acquired on every row to be modified in that table.
Next Key Weak Exclusive (NW)	Rows	Lock owner can read all data (excluding uncommitted data) stored in the locked resource; however, lock owner cannot modify data stored in the locked resource. Next Key Weak Exclusive locks are typically acquired on the next available row in a table whenever a row is inserted into any index of a noncatalog table.	Other transactions can read all data (excluding uncommitted data) stored in the locked resource; however, they cannot modify data stored in the locked resource.	None
Exclusive (X)	Tables, Rows	Lock owner can read and modify data stored in the locked resource. Exclusive locks are typically acquired for transactions that retrieve data with SELECT statements and then modify the data retrieved with INSERT, UPDATE, or DELETE statements.	Transactions using the Uncommitted Read isolation level can read all data, including uncommitted data, stored in the locked resource; however they cannot modify data stored in the locked resource. All other transactions can neither read, nor modify data stored in the locked resource.	Individual rows in a table can be Exclusive (X) locked, provided the table itself is not Exclusive (X) locked.
Weak Exclusive (WE)	Rows	Lock owner can read and modify data stored in the locked resource. Weak Exclusive locks are typically acquired on a row when it is inserted into a nonsystem catalog table.	Transactions using the Uncommitted Read isolation level can read all data, including uncommitted data, stored in the locked resource; however, they cannot modify data stored in the locked resource. All other transactions can neither read nor modify data stored in the locked resource.	None
Super Exclusive (Z)	Table spaces, Tables	Lock owner can read and modify data stored in the locked resource. Super Exclusive locks are typically acquired on a table whenever the lock owner attempts to alter that table, drop that table, create an index for that table, drop an index that has already been defined for that table, or reorganize the contents of the table (while the table is offline) by running the REORG utility.	Other transactions can neither read nor modify data stored in the locked resource.	None
Adapted from Table 4 on pages 60–61 of the IBM DB2 Version 9 for Linux, UNIX, and Windows Performance Guide.

How Locks Are Acquired

Except for occasions where the Uncommitted Read isolation level is used, it is never necessary for a transaction to request a lock explicitly. That's because the DB2 Database Manager implicitly acquires locks as they are needed; 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 attempt to 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:

 ALTER TABLE [TableName] LOCKSIZE [ROW | TABLE] 

where:

TableName

Identifies the name of an existing table for which the level of locking that all transactions are to use when accessing it is to be specified.

For example, when executed, the SQL statement

 ALTER TABLE employee LOCKSIZE ROW 

will force the DB2 Database Manager to acquire row-level locks for every transaction that accesses a table named EMPLOYEE. (This is the default behavior.) On the other hand, if the SQL statement

 ALTER TABLE employee LOCKSIZE TABLE 

is executed, the DB2 Database Manager will attempt to acquire table-level locks for every transaction that accesses the EMPLOYEE table.

But what if you don't want every transaction that works with a particular table to acquire table-level locks? What if, instead, you want one specific transaction to acquire table-level locks and all other transactions to acquire row-level locks when working with that particular table? In this case, you leave the default locking behavior alone (row-level locking) and 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:

TableName

Identifies the name of an existing table to be locked.

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, 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 statement

 LOCK TABLE employee IN EXCLUSIVE MODE 

were executed, a table-level Exclusive (X) lock would be acquired, and no other transaction would be allowed to read or modify data stored in the EMPLOYEE table until the owning transaction is terminated.

Lock Granularity and Concurrency

When it comes to deciding whether to use row-level locks or table-level locks, it is important to keep in mind that 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), often there is considerably less overhead involved when a single table-level lock is acquired, rather than several individual row-level locks.

To a certain extent, lock granularity (row-level locking versus table-level locking) can be controlled through the use of the ALTER TABLE and LOCK TABLE SQL statements-the ALTER TABLE statement controls granularity at a global level, while the LOCK TABLE statement controls granularity at an individual transaction level. 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 lookup table table that is to be accessed by multiple concurrent transactions. Forcing the DB2 Database Manager to acquire Share (S) table-level locks globally for every transaction that attempts to access this table might improve overall performance, since the locking overhead required would be greatly reduced. On the other hand, suppose you have a table that needs 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 at the transaction level whenever the maintenance transaction executes makes more sense than forcing the DB2 Database Manager to acquire Exclusive (X) table-level locks globally for every transaction that needs to access the table. If this approach is used, the read-only transactions are locked out of the table only when the maintenance transaction runs; in all other situations, they can access the table concurrently while requiring very little locking overhead.

Which Locks Are Acquired?

Although 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:

• Read-Only

• Intent-to-Change

• Change

• Cursor-Controlled

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.

Table 7-3: Types of Transactions Available and Their Associated Locks
Open table as spreadsheet

Type Of Transaction …	Description…	Locks Acquired …
Read-Only	Transactions that contain SELECT SQL statements (which are intrinsically read-only), SELECT SQL statements that have the FOR READ ONLY clause specified, or SQL statements that are ambiguous, but are presumed to be read-only because of the BLOCKING option specified as part of the precompile and/or bind process	Intent Share (IS) and/or Share (S) locks for table spaces, tables, and rows
Intent-to-Change	Transactions that contain SELECT SQL statements that have the FOR UPDATE clause specified or SQL statements that are ambiguous, but are presumed to be intended for change because of the way they are interpreted by the SQL precompiler	Share (S), Update (U), and Exclusive (X) locks for tables; Update (U), Intent Exclusive (IX), and Exclusive (X) locks for rows
Change	Transactions that contain INSERT, UPDATE, or DELETE SQL statements but not UPDATE WHERE CURRENT OF or DELETE WHERE CURRENT OF SQL statements	Intent Exclusive (IX) and/or Exclusive (X) locks for table spaces, tables, and rows
Cursor-Controlled	Transactions that contain UPDATE WHERE CURRENT OF or DELETE WHERE CURRENT OF SQL statements	Intent Exclusive (IX) and/or Exclusive (X) locks for table spaces, tables, and rows

It is important to keep in mind that in some cases, a single transaction will consist of multiple 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. Because of this, locks needed for the resources referenced in the subquery are determined using the rules for Read-Only transactions, while the locks needed for the target table of the insert operation are determined using the rules for Change transactions.

Locks and Performance

Although 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 influence how locking affects performance. These factors include:

• Lock compatibility

• Lock conversion

• Lock escalation

• Lock waits and timeouts

• Deadlocks

• Concurrency and granularity

Knowing what these factors are and understanding how they can affect overall performance can assist you in designing database applications that work well in multi-user database environments and, indirectly, give you more control over how locks are used.

Lock Compatibility

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 acquired is released, the locks are said to be compatible. Any time 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 each lock's state and determine whether they are compatible. Table 7-4 contains a lock compatibility matrix that identifies which locks are compatible and which are not.

Table 7-4: Lock Compatibility Matrix
Open table as spreadsheet

		Lock Requested by Second Transaction
	Lock State	IN	IS	NS	S	IX	SIX	U	NW	X	WE	Z
Lock Held by First Transaction	IN	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No
	IS	Yes	Yes	Yes	Yes	Yes	Yes	Yes	No	No	No	No
	NS	Yes	Yes	Yes	Yes	No	No	Yes	Yes	No	No	No
	S	Yes	Yes	Yes	Yes	No	No	Yes	No	No	No	No
	IX	Yes	Yes	No	No	Yes	No	No	No	No	No	No
	SIX	Yes	Yes	No	No	No	No	No	No	No	No	No
	U	Yes	Yes	Yes	Yes	No	No	No	No	No	No	No
	NW	Yes	No	Yes	No	No	No	No	No	No	Yes	No
	X	Yes	No	No	No	No	No	No	No	No	No	No
	WE	Yes	No	No	No	No	No	No	Yes	No	No	No
	Z	No	o	No	No	No	No	No	No	No	No	No
		Yes	Locks are compatible; therefore, the lock request is granted immediately.
		No	Locks are not compatible; therefore, the 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					U	Update
		IS	Intent Share					NW	Next Key Weak Exclusive
		NS	Next Key Share					X	Exclusive
		S	Share					WE	Weak Exclusive
		IX	Intent Exclusive					Z	Super Exclusive
		SIX	Share With Intent Exclusive
Adapted from Table 5 on page 72 of the IBM DB2 Version 9 for Linux, UNIX, and Windows Performance Guide.

Lock Conversion

If a transaction holding a lock on a resource 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. 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-6 illustrates a simple lock conversion process.

Figure 7-6: A simple lock conversion scenario-in this example, a Share (S) lock is converted to an Exclusive (X) lock.

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.

As a result, 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 the lock 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.

Lock Escalation

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 the locks held by every application 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 each transaction is allowed to use in the lock list to store its own locks. (This limit is determined by the maxlocks database configuration parameter). To prevent a specific database agent 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 converting several row-level locks into a single table-level lock. Figure 7-7 illustrates a simple lock escalation process.

Figure 7-7: 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 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 acquired is then 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 continue execution. If however, the lock list space needed is still unavailable after all of 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.

Tip 

Use of the ALTER TABLE SQL statement or the LOCK TABLE SQL statement does not prevent normal lock escalation from occurring. However, it may reduce the frequency with which lock escalations take place.

Lock Waits and Timeouts

Any time a transaction holds a lock on a particular resource (table space, table, or row), other transactions may be denied access to that resource until the owning transaction terminates and frees all locks it has acquired. Thus, without some sort of lock timeout detection mechanism in place, a transaction might wait indefinitely for a lock to be released. For example, suppose a transaction in one user's application is waiting for a lock being held by a transaction in another user's application to be released. If the other user leaves his or her workstation without performing some interaction that will allow the application to terminate and release all locks held, the application waiting for the lock to be released will be unable to continue processing for an indeterminable amount of time. Unfortunately, it would also be impossible to terminate the application 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. 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 specifies 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 has been rolled back, the waiting application will, by default, be terminated. (This behavior prevents data inconsistency from occurring.)

Tip 

By default, the locktimeout configuration is set to -1, which means that applications 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 the DB2 Database Manager and respond appropriately.

Deadlocks

In most cases, the problem of one transaction waiting indefinitely for a lock to be released can be resolved by establishing lock timeouts. However, that is not the case when lock contention creates a situation 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. 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 has occurred. Figure 7-8 illustrates this deadlock scenario.

Figure 7-8: 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-8). 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, however, 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 very 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 repeat the process.

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.