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:

1. The solution must provide a conceptual session lock per resource that is visible between applications.
2. It must guarantee that a session lock is freed, even if the application holding it terminates abnormally.
3. 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).
4. 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.

Back to top

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.

                

      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.

Back to top

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.

Back to top

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:

1. Start a child thread that evaluates the lock request.
2. Wait until the child thread signals that the lock request was evaluated and the result is available.
3. 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.
4. 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.

                
      
      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;
      }
      

Back to top

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.

                

      CLOSE C1;
      ROLLBACK WORK;
      

Back to top

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.

                

      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

• Check out developerWorks blogs and get involved in the developerWorks community.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

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.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Problem statement
• Pattern for a solution
• Create a DB2 table for resource locking
• Lock/unlock protocol to access a resource
• Implement the lock() function
• Implement the unlock() function
• Control the number of concurrent applications for a resource
• Summary
• Acknowledgements
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers