Locking issues in a database often lead to finger-pointing between DBAs and Application developers. Application folks will complain that their application is performing poorly because of database locking issues. DBAs will respond that it’s the application design issue that’s causing the locks. In fact, the locking issues can be resolved by the combined effort of DBAs and Application folks.
DBAs have tools to diagnose the locking issue and explain to application folks the root cause. Then DBAs and Application folks can work together to analyze the transaction logic that’s causing the locking issue and make corrections if possible. Sometimes, the locking issues can be resolved or reduced by database level changes (e.g. changing maxlocks and locklist database parameters).
Now, let’s see what are the locking issues in DB2 and how can a DBA diagnose them. DB2 can have mainly three types of locking issues
· Lock escalation
· Lock wait and lock timeout
As a DBA, you should periodically check the occurrences of above locking issues through Database snapshot and make sure that there are not many occurrences of these locking issues. I will briefly describe here each of the three locking issues and also provide pointers for further details.
A deadlock is created when two applications are each locking data needed by the other, resulting in a situation when neither application can continue execution. DB2 deadlock detector is responsible for handling deadlocks. When a deadlock is detected, the deadlock detector will choose a victim that will be automatically rolled back and issued an SQL0911 error message with a return code of 2. By rolling back the victim, the lock conflict is removed and the application can continue processing. Database parameter ‘dlchktime’ controls the time interval for checking deadlocks. DB2 also creates an event monitor called db2detaildeadlock by default to capture the relevant info about the deadlock which can be used to identify the root cause of the deadlock. Check the link “Deadlocks” at Information Center for more details.
Lock escalation occurs when database manager will attempt to free up memory allocated to locking by obtaining table locks and releasing existing row locks. The desired effect is to make more lock memory available for additional applications. The following two database parameters have a direct effect on lock escalation:
· locklist- amount of storage (in units of 4k pages) that is allocated to hold the list of all locks held by all applications concurrently connected to the database.
· maxlocks- the allowable percentage of locklist that can be used by an application.
Both the above database parameters are automatic configurable by default in version 9. Though the above two database parameters influence the lock escalation, we should not always change them to resolve the lock escalation. Check the link “Correcting lock escalation problems” at DB2 Information Center for a step wise approach to resolve the lock escalation issue.
Lock wait and lock timeout
Finally, what’s lock wait and lock timeout? An application, that makes a request for a lock that is not compatible with the existing locks on the object, will have to wait to acquire the lock and this phenomenon is called lock wait. There is a database parameter called locktimeout that determines how long the application in lock wait state should continue waiting before it times out. If the timeout period is exceeded, the waiting application receives an SQL0911 error message with a return code 68 and the application’s unit of work (transaction) is rolled back by the database manager.
Note that the default value of locktimeout parameter is -1, which causes the lock timeout to be disabled. That is, an application that is waiting for a lock will continue to wait indefinitely or until the lock is released. To avoid such a scenario, it’s recommended to set a positive value for locktimeout (specially in transactional environment). In transactional environment, the recommended starting value for locktimeout is 30 seconds. However, tuning may be necessary in order to find the more appropriate value for this parameter.
You can find out the current lock waits in database using lock wait snapshot. Check the link SNAP_GET_LOCKWAIT table function at DB2 Information Center for more details about lock wait snapshot.
Diagnosing the lock timeout is little involved. Through the snapshot monitors, you would not have the details of applications involved in lock contention that resulted in the lock timeout. Prior to version 9, you could capture such details using event monitors. However, the event monitors to capture the transactions and statements may cause considerable performance impact and hence that might not be an option in your environment. DB2 v9.1 FP4 introduced lock timeout reporting feature to help the diagnosis of lock timeouts. There is a registry variable called DB2_CAPTURE_LOCKTIMEOUT, which controls the lock timeout reporting function. When this registry variable is set to ON, it captures basic information about each lock timeout that happens within the DB2 instance and a lock timeout report is created under the instance diagnostic path (diagpath). Check the following links at DB2 Information Center for more details.
Hope the above info will help to reduce locking issues in your environment. As mentioned in the beginning, DBAs can help to diagnose the locking issues. However in most of the cases, the locking issue has to be resolved from application side (correcting the application logic, using the proper isolation level etc.).