Level: Intermediate Claus Kempfert (kempfert@de.ibm.com), DB2 Text Search Development, IBM
Michael Haid (hde@de.ibm.com), DB2 Intelligent Miner Development, IBM
01 Feb 2007 A new feature for DB2 for z/OS V9.1, SKIP LOCKED DATA, gives you the capability to implement application-level session locking, allowing intermediate DB2 commits. Become familiar with the details by following the easy design pattern presented in this article.
Introduction
Database locking strategies are critical for gaining the best possible application performance, while ensuring data integrity and consistent application behavior. In his article
"Locking strategies for database access" (developerWorks, March 2006), Paul Ilechko describes the logical session locking approach to database locking. In addition to transaction locking, this approach
is needed to control the concurrency of applications on a higher level.
A bullet-proof implementation of a pessimistic session locking approach has been difficult to implement.
However, with the new feature SKIP LOCKED DATA introduced in DB2 Version 9.1 for z/OS (originally intended for a completely different purpose, namely lock avoidance), an easy and reliable solution is possible.
Problem statement
Concurrency control at the application level is necessary in a variety of scenarios, for example:
- Ensuring the mutual exclusion of applications that work on a set of resources that cannot be shared.
A real-life example is a stored procedure for synchronizing a full-text index that is external to DB2 with data stored in DB2.
Here, the controlled resource is the full-text index. It is identified by an index name that is a parameter to
the stored procedure. Parallel invocations of the stored procedure are only allowed if the index name parameters are different.
- Controlling the maximum number of applications working concurrently on a resource set.
For pessimistic session locking, the application needs two functions, lock(resource) and unlock(), that are to be used by convention.
Here is an example set of requirements that make an implementation difficult on the application level:
- The solution must provide a conceptual session lock per resource that is visible between applications.
- It must guarantee that a session lock is freed, even if the application holding it terminates abnormally.
- The lock or unlock mechanism must be independent of DB2 transactions in the application
(that is, intermediate transaction commits must not free a session lock as a side effect).
- The solution must avoid session lock timeouts and long wait for locks. The lock() function must immediately check and return (non-blocking).
Any session locking implementation that is based on DB2 transaction locks has to solve the problem of conflicting locks.
A transaction lock conflict could either lead to rolling back a transaction (sqlcode -911), or to waiting indefinitely for the lock.
Without a database concept such as SKIP LOCKED DATA, this problem is difficult to solve.
Pattern for a solution
For a solution, it is suggested that you define a DB2 table holding a list of resource identifiers
and use a lock/unlock protocol in all applications accessing the resources.
The implementation of the lock() function exploits the new DB2 capabilities
of skipping locked data in an SQL fetch statement. This is key to the solution.
Create a DB2 table for resource locking
Create a DB2 table (hereafter referred to as lock table) to define the resources related to session locking.
Assume that the scope of a logical session lock is some kind of resource identifier.
In the full-text index example above, this identifier would be the index name.
For every resource, insert one row into this table.
Listing 1. Sample SQL to create a lock table
CREATE TABLE LockTable(ResourceId CHAR(10));
INSERT INTO LockTable VALUES('INDEX 1');
INSERT INTO LockTable VALUES('INDEX 2');
|
The example above shows how to fill the lock table for two full-text index resources.
Each index must not be used by more than one application.
That means application 1 may use INDEX 1, while application 2 uses INDEX 2,
but concurrent use of INDEX 1 in both applications is not allowed.
Lock/unlock protocol to access a resource
Introduce a protocol consisting of two functions, lock(ResourceId) and unlock(), that all applications need to follow.
The exact interfaces depend on the implementation language and do not matter here.
It is crucial that all applications call lock(resourceId) before accessing a resource controlled by session locking.
They should call unlock() when they do not need the resource any more.
Implement the lock() function
The implementation of the lock() function has to assure that the result of the request (lock is granted or not granted) is immediately available to the caller.
Furthermore, a granted lock must not be affected by DB2 transactions within the application.
Therefore, the lock() implementation has to open an additional connection to DB2 in a separate thread.
So the implementation of the lock() function consists of the following steps:
- Start a child thread that evaluates the lock request.
- Wait until the child thread signals that the lock request was evaluated and the result is available.
- Within the child thread, open a new connection to DB2 and fetch a row from the lock table for the requested resource ID.
Here the feature
SKIP LOCKED DATA is exploited to get only rows that are not locked by DB2
(for example, by another application holding a session lock on the resource). DB2 does not wait on the fetch operation.
See sample code in Listing 2.
Now the result of the request must be made available to the main thread.
The child thread waits for a signal of the main thread to terminate.
If the session lock was granted, it holds a DB2 update lock on a row in the lock table until:
- unlock() is called or
- the application terminates.
- The lock() function main thread gets the result from the child thread.
If the lock was granted, the lock() function returns to the caller.
Otherwise, the child thread is terminated.
Listing 2. SQL code for implementing lock() in the child thread
DECLARE C1 CURSOR FOR
SELECT ResourceId FROM LockTable WHERE ResourceId=:resourceId
FOR UPDATE WITH CS SKIP LOCKED DATA;
OPEN C1;
FETCH C1;
if (sqlca.sqlcode==NO_DATA_FOUND) {
result=indexAlreadyLocked;
} else {
result=lockGranted;
}
|
Implement the unlock() function
Provide an unlock() function that terminates the child thread
that still holds a DB2 update lock on a lock table row.
The termination code within the child thread closes the SQL cursor and rolls back the transaction, as shown in Listing 3.
As a result, the DB2 update lock for the row is released
and this row is found by the next SQL select statement in Listing 2.
Listing 3. SQL code for implementing unlock() in the child thread
Control the number of concurrent applications for a resource
A simple modification of the presented approach allows you to control the number of concurrent applications for a set of resources:
When having duplicate rows in the lock table (see the Create a DB2 table for resource locking section),
concurrent use of the resource is possible.
The maximum number of applications allowed to access a resource concurrently is defined by the number of rows
for this resource in the lock table.
Listing 4. Sample SQL to fill a lock table for controlling a maximum number of concurrent applications
INSERT INTO LockTable VALUES('INDEX 1');
INSERT INTO LockTable VALUES('INDEX 1');
INSERT INTO LockTable VALUES('INDEX 2');
INSERT INTO LockTable VALUES('INDEX 2');
INSERT INTO LockTable VALUES('INDEX 2');
|
Here a maximum number of two applications can concurrently access 'INDEX 1', and a maximum number of three applications can concurrently access 'INDEX 2'.
Summary
A simple and reliable solution pattern can be used to implement
session locking on the application level. This solution relies on the new feature SKIP LOCKED DATA available in DB2 Version 9.1 for z/OS.
The solution pattern was applied successfully within a DB2 development project.
Acknowledgements
Thanks to Erik Hassold, Namik Hrle, and Albert Maier for reviewing the technical idea presented in this article.
Resources Learn
-
"Locking strategies for database access" (developerWorks, March 2006): Read general concepts, the basic issues, common strategies, and general recommendations on when and how to use different locking strategies.
- Visit the
DB2 for z/OS Web site for
the latest information on the DB2 product family, especially on version 9.1 for z/OS.
-
In the DB2 for z/OS area on developerWorks, get the resources you need to advance your skills on DB2 for z/OS.
-
developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
-
Stay current with developerWorks
technical events and webcasts.
Get products and technologies
-
Build your next development project with
IBM
trial software, available for download directly from developerWorks.
Discuss
About the authors  | 
| | Claus Kempfert is a senior developer and architect at the IBM Germany Development Lab in Boeblingen. Since 2000, he has been involved in integration of database and text search technologies. |
| 
| | Michael Haide has worked several years in DB2 Net Search Extender as team lead and architect at the IBM Germany Development Lab in Boeblingen. He was involved in several performance critical search applications. At present, Michael is working on Intelligent Miner, a component of DB2 Data Warehouse Edition. |
Rate this page
| 
