Learning Visually with Examples [Electronic resources] نسخه متنی

Understanding DB2® Learning Visually with Examples
By Raul F. Chong, Clara Liu, Sylvia F. Qi, Dwaine R. Snow

Publisher	: Prentice Hall PTR
Pub Date	: January 25, 2005
ISBN	: 0-13-185916-1
Pages	: 928

11.5. DB2 Locking

DB2 uses various levels of locking to provide concurrent data access and at the same time protect the data. Depending on the operations requested, the database manager can acquire locks on databases, buffer pools, table spaces, tables, table blocks, and table rows. Locks are acquired implicitly by DB2 according to the semantics defined by the isolation level.

11.5.1. Lock Attributes

During normal data manipulation processing, DB2 uses row-level locking by default. You can override this rule to acquire table-level locking instead. The

ALTER TABLE statement with the

LOCKSIZE option forces DB2 to place a table lock whenever the table is accessed. The statement will look like this:

ALTER TABLE employee LOCKSIZE TABLE

This setting is retained until you execute:

ALTER TABLE employee LOCKSIZE ROW

The only objects that you can explicitly lock are databases, tables, and table spaces. To explicitly lock a database, use the

CONNECT statement with the appropriate lock mode. For example:

CONNECT TO sample IN EXCLUSIVE MODE

This causes an exclusive lock to be applied to the database. It prevents concurrent application from executing any operations at the database. This lock mode is useful when exclusive administrative tasks must be performed. You can also connect to the database in SHARE MODE, which allows other concurrent connections to the database but prevents other users from connecting in exclusive mode.

Chapter 5, Understanding the DB2 Environment, DB2 Instances, and Databases.

Table spaces of a particular table can be quiesced.

Quiesing a table space is like locking a table space so that administrative tasks (e.g., a load operation) can be performed. With the different quiesce modes, shown in Figure 11.18, DB2 obtains different types of locks for the table and its associated table space(s). The syntax diagram of the

quiesce tablespaces for table command is presented in Figure 11.18.

Figure 11.18. Syntax diagram of the quiesce tablespaces for table command


>>-QUIESCE TABLESPACES FOR TABLE--+-tablename--------+---------->
'-schema.tablename-'
>--+-SHARE------------+----------------------------------------><
+-INTENT TO UPDATE-+
+-EXCLUSIVE--------+
'-RESET------------'

If you have quiesced the table spaces with a restrictive mode, access to tables within those tablespaces are not allowed. For example, this command:

quiesce tablespaces for table employee exclusive

puts superexclusives lock on the table space where table

employee is stored, and on the table

employee . The state of the table space changes to QUIESCED EXCLUSIVE. No other access to the table spaces is allowed. This means that access to another table that is stored in the same table space is not allowed. You will receive the following error.


SQL0290N Table space access is not allowed SQLSTATE=55039

To unquiesce the table space, issue the same

quiesce tablespaces for table command but with the

reset option.

You can also lock a table explicitly with the

LOCK TABLE statement. Similarly, different lock modes are available as shown in Figure 11.19. The

LOCK TABLE statement locks the specified table until the transaction is completed.

Figure 11.19. Syntax diagram of the LOCK TABLE statement


>>-LOCK TABLE--+-table-name-+--IN--+-SHARE-----+--MODE---------><
'-nickname---'      '-EXCLUSIVE-'

Each lockable object can be locked in a different mode; this represents the type of access allowed for the lock owner. They also control the type of access permitted for concurrent users of the locked object.

11.5.1.1 Table-Level Lock Modes

Table and row locks are the most commonly used types of locks. Figure 11.20 shows the table-level lock modes. The table lock modes IN, IS, IX, and SIX are used to support row-level locking. An application requires an IN lock on the table before it can perform an uncommitted read. The IS, IX, and SIX locks permit row-level locking while preventing more exclusive locks on the table by other applications.

Figure 11.20. Table Lock Mode Compatibility Chart

The other table lock modesS, U, X, and Zare strict table locking and do not use row-level locking. For example, if an application holds an X lock on a table, the lock owner can read or update any data in the table but cannot obtain a row lock. Refer to Table 11.4 for a summary of all table lock modes.

Table 11.4. Lock Modes Summary
Lock Mode	Buffer Pool	Table Space	Table Block	Table	Row	Description
IN ( I ntent N one)		Y	Y	Y		The lock owner can read any data in the object, including uncommitted data, but cannot update any of it. Other concurrent applications can read or update the table.
IS ( I ntent S hare)		Y	Y	Y		The lock owner can read data in the locked object but cannot update its data. Other applications can read or update the object.
NS ( N ext Key S hare)					Y	The lock owner and all concurrent applications can read, but not update, the locked row. This lock is acquired on rows of a table where the isolation level of the application is either RS or CS. NS lock mode is not used for next-key locking. It is used instead of S mode during CS and RS scans to minimize the impact of next-key locking on these scans.
S ( S hare)			Y	Y	Y	The lock owner and all concurrent applications can read but not update the locked data.
IX ( I ntent e X clusive)		Y	Y	Y		The lock owner and concurrent applications can read and update data. Other concurrent applications can both read and update the table.
SIX ( S hare with I ntent e X clusive)			Y	Y		The lock owner can read and update data. Other concurrent applications can read the table.
U ( U pdate)			Y	Y	Y	The lock owner can update data. Other units of work can read the data in the locked object but cannot update it.
NW ( N ext Key W eak Exclusive)					Y	When a row is inserted into an index, an NW lock is acquired on the next row. The lock owner can read but not update the locked row. This lock mode is similar to an X lock, except that it is also compatible with W and NS locks.
X (e X clusive)	Y		Y	Y	Y	The lock owner can both read and update data in the locked object. Only uncommitted read applications can access the locked object.
W ( W eak Exclusive)					Y	This lock is acquired on the row when a row is inserted into a table. The lock owner can change the locked row. This lock is used during insertion into a unique index to determine if a duplicate value is found. This lock is similar to an X lock except that it is compatible with the NW lock. Only uncommitted read applications can access the locked row.
Z (Super Exclusive)		Y		Y		This lock is acquired on a table in certain conditions, such as when the table is altered or dropped, an index on the table is created or dropped, and for some types of table reorganization. No other concurrent application can read or update the table.

11.5.1.2 Row Lock Modes

Row lock modes require support of some kind of table lock. The minimum table locks DB2 must acquire before obtaining a row lock are listed in Figure 11.21. For example, an application can lock a row in Share mode if it also holds an IS lock on the table.

Figure 11.21. Row Lock Mode Compatibility Chart

Besides table and row locks, there are other types of objects DB2 locks. Table 11.4 presents a summary of lockable objects and lock modes.

Y means that the lock mode applies to that type of object; a dash means that it does not apply.

NOTE

If you use multidimensional clustering (MDC) tables, you can impose table block locks.

Figures 11.22 and 11.23 (from the DB2 manual

Administration Guide: Performance ) present lock mode compatibility charts for table and row locks respectively.

NO means the requesting application must wait for the lock to be released and

YES means the lock can be granted.

Figure 11.22. Table lock mode compatibility chart

[View full size image]

Figure 11.23. Row lock mode compatibility chart

Let's use an example to demonstrate how to use the charts. Assume that application A is holding an IX lock on a table. Looking at the compatibility chart in Figure 11.22, you can see that another application can only lock the same table in IN, IS, or IX mode as highlighted with the circles in the figure.

If application B requests an IS lock at the table level and tries to read some rows in the table, use the row lock chart in Figure 11.23 to determine the compatibility of concurrent data access. As long as application A holds locks that are compatible with the lock mode application B is requesting, both applications can work concurrently with each other. For example, if application A is holding a U lock on a row, application B can only obtain an S or NS lock (refer to compatibility values circled in Figure 11.23). Otherwise, application B must wait for application A to complete its transaction.

11.5.2. Lock Waits

A discussion of DB2 locking mechanisms is not really complete if lock wait and deadlock scenarios are not covered. As the number of concurrent applications increases, the possibility of running into situations with incompatible locks is relatively higher. In the examples used to describe the behavior of the different isolation levels, you saw how an application might have to wait for a lock. This is known as

lock wait . Deadlocks are discussed in the next section.

It is generally not possible to totally avoid lock wait as concurrency increases. After all, DB2 relies on the locking mechanism to keep data integrity. However, you should minimize lock waits and each wait length as much as possible. They put a hold on processing the statements, hence, they affect performance.

Note that you should minimize lock waits and the duration of each wait. You can use the database configuration parameter called LOCKTIMEOUT to define how long an application is going to wait for a lock. By default, LOCKTIMEOUT is set to -1, which stands for infinite wait. We recommended setting it to a finite number that works well with your application and business requirement.

If an application reaches the LOCKTIMEOUT value, it receives the following message:

[View full width]

SQL0911N The current transaction has been rolled back because of a deadlock or timeout.
Reason code "68".

Reason code 68 indicates the transaction is rolled back due to a lock timeout. LOCKTIMEOUT applies to any application connecting to the database. In some cases, you may want to set the timeout duration for a given application rather than providing the same value for all applications. You can directly control how long an individual application will wait for a lock using the

set current lock timeout command. This command overrides the LOCKTIMEOUT parameter and stores the new value in the DB2 special register CURRENT LOCK TIMEOUT. This would be useful, for example, in a system where there is a mixed workload of long-running reports as well as update batch jobs. Figure 11.24 gives the syntax of the command.

Figure 11.24.
Syntax diagram of the set current lock timeout command


.-CURRENT-.                .-=-.
>>-SET--+---------+--LOCK TIMEOUT--+---+------------------------>
>--+-WAIT-----------------------+------------------------------><
+-NOT WAIT-------------------+
+-NULL-----------------------+
| .-WAIT-.                   |
+-+------+--integer-constant-+
'-host-variable--------------'

You can set the lock timeout period to the following.

WAIT specifies that the application will wait infinitely for a lock.
NOT WAIT specifies that the application will not wait for locks that cannot be obtained.
NULL specifies that the application will use the value of the LOCKTIMEOUT database configuration parameter as the duration to wait for locks.
WAIT
integer_constant specifies an integer value of how long the application will wait for a lock. The value -1 will have the same behavior as WAIT (without an integer value). A value of 0 is equivalent to specifying NOT WAIT.

To validate the value of the CURRENT LOCK TIMEOUT special register, you can use the

VALUES statement:

VALUES CURRENT LOCK TIMEOUT

11.5.3. Deadlocks

There is another undesirable lock scenario to avoid: deadlock. Deadlock is a situation when two applications are waiting for locks that the other is holding. Consider the situation in Figure 11.25.

Figure 11.25. Example of a deadlock situation

[View full width]

SQL0911N The current transaction has been rolled back because of a deadlock or timeout.
Reason code "2".

Reason code 2 means that the transaction is rolled back due to a deadlock. The failed user application is then responsible to report the error and retry the transaction if necessary. The deadlock detector is activated periodically as determined by the DLCHKTIME database configuration parameter. The default value for this parameter is 10,000 milliseconds (10 seconds).

To avoid deadlocks or any unnecessary lock waits, you need to understand your application. Design the application and tune the database in a way that the application will only read the data it requires. Figure 11.25 shows an example of two applications manipulating data on different rows. Why would it still encounter a deadlock? The key to this problem is that DB2 scans every

empno value to see if the row qualifies the queries. If only a portion of the values are scanned, the applications may not run into a deadlock. This can be achieved by creating proper indexes and maintaining current database statistics so DB2 can choose a more efficient data access plan.

A deadlock may still occur even with proper indexing and database maintenance. In that case, you can make use of a new feature lock deferral, which is discussed next.

11.5.4. Lock Deferral

You can enable lock deferral for CS or RS isolation level scans with the DB2_EVALUNCOMMITTED registry variable. DB2 evaluates the row before trying to lock it. To enable this feature issue the command:


db2set DB2_EVALUNCOMMITTED=YES

To disable it issue:


db2set DB2_EVALUNCOMMITTED=

Figure 11.26 shows that lock deferral no longer requires App A to put an S lock on

empno = 105. App A can then read the

empno = 106 row. Similar logic applies to App B.

Figure 11.26. Deadlock problem resolved with EVALUNCOMMITTED enabled

11.5.5. Lock Escalation

When DB2 acquires a lock on an object, it allocates memory for each lock from the database shared memory area called the

locklist . A tunable database configuration parameter by the same name lets you indicate the maximum storage allowed for locks in each database. To resize the locklist, update the LOCKLIST parameter with a new value in units of 4K.

Regardless of the type of lock, each lock uses about 36 bytes of memory on 32-bit DB2 instances and 56 bytes of memory on 64-bit DB2 instances. As the number of locks being held lock escalation.

Lock escalation degrades performance because it can significantly reduce database concurrency. When you monitor your database, you should ideally see very few to no escalations. It is important to tune the LOCKLIST parameter appropriately so that lock escalations are avoided.

The MAXLOCKS database configuration parameter also has a direct effect on lock escalation. MAXLOCKS defines the percentage of the total locklist permitted to be allocated to a single application. Proper configuration of MAXLOCKS prevents any one application from using up all the memory available in the locklist. When the amount of locks an application holds reaches the MAXLOCKS percentage, DB2 escalates the row locks of the particular application to a table lock. The table with the most row locks is escalated first. Lock escalation continues until the percentage of the locklist held is below the value of MAXLOCKS.

The database manager determines which locks to escalate by looking through the locklist for the application and finding the table with the most row locks. If after replacing these with a single table lock, the MAXLOCKS value is no longer exceeded, lock escalation will stop. If not, escalation continues until the percentage of the locklist held is below the value of MAXLOCKS. The MAXLOCKS parameter multiplied by the MAXAPPLS parameter cannot be less than 100.

As the number of row locks being held increases, the chance of locking escalations occurring also increases. Take this into consideration when choosing isolation levels. For example, the RR isolation level locks all the rows in the result set as well as the rows referenced to build the result set. With this isolation level you should choose an appropriate value for your MAXLOCKS and LOCKLIST parameters.



•	Index