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 States

All DB2 Universal Database locks 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 , tablespaces, 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 in 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.

How Locks Are Acquired

Except 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

Lock State (Mode)	Applicable Objects	Lock Owner Access	Concurrent Transaction Access	Other Locks Acquired
Intent None (IN)	Tablespaces, 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)	Tablespaces, 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, and/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, and/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)	Tablespaces, 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 , and/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 , and/or DELETE statements.	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 an Update (U) locked table, an Exclusive (X) lock is acquired on every row to be modified in that table.
Next Key Exclusive (NX)	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 Exclusive locks are typically acquired on the next available row in a table whenever a row is deleted from or inserted into any index associated with the 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
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 non-catalog 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 , and/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 can-not 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)	Tablespaces, 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 3 on Pages 62 - 63 of the IBM DB2 Administration Guide “ Performance manual.

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

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 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 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.

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

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 tablespaces, 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 , and/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 tablespaces, tables, and rows.
Cursor-Controlled	Transactions that contain UPDATE WHERE CURRENT OF and/or DELETE WHERE CURRENT OF SQL statements.	Intent Exclusive (IX) and/or Exclusive (X) locks for tablespaces, tables, and rows.

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 affect locking that you need to be aware of. 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 multiuser 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 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 conversion

If 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

Lock State

Lock Held by First Transaction

IN

IS

NS

S

IX

SIX

U

NX

NW

X

WE

Z

Lock Requested by Second Transaction

IN

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

IS

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

No

No

No

No

NS

Yes

Yes

Yes

Yes

No

No

Yes

Yes

Yes

No

No

No

S

Yes

Yes

Yes

Yes

No

No

Yes

No

No

No

No

No

IX

Yes

Yes

No

No

Yes

No

No

No

No

No

No

No

SIX

Yes

Yes

No

No

No

No

No

No

No

No

No

No

U

Yes

Yes

Yes

Yes

No

No

No

No

No

No

No

No

NX

Yes

No

Yes

No

No

No

No

No

No

No

No

No

NW

Yes

No

Yes

No

No

No

No

No

No

No

Yes

No

X

Yes

No

No

No

No

No

No

No

No

No

No

No

WE

Yes

No

No

No

No

No

No

No

Yes

No

No

No

Z

No

No

No

No

No

No

No

No

No

No

No

No

Yes

Locks are compatible; therefore, the lock request is granted

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

IN

Intent None

IS

Intent Share

NS

Next Key Share

S

Share

IX

Intent Exclusive

SIX

Share With Intent Exclusive

U

Update

NX

Next Key Exclusive

NW

Next Key Weak Exclusive

X

Exclusive

WE

Weak Exclusive

Z

Super Exclusive

Adapted from Table 4 on Page 73 of the IBM DB2 Administration Guide “ Performance manual.

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 escalation

When 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.

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 (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.)

By default, the locktimeout configuration is set to “1, which means that applications will wait forever to acquire the locks they need. In many cases, this value should be changed to something other than the default. 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

We 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 granularity

As 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.