How to Use DB2 V9.1for z/OS to implement application session lock?

The following articles mainly describe how to use DB2 V9.1for z/OS to implement application session lock, if you are interested in the actual operation steps of DB2 V9.1for z/OS to implement application session lock, you can click the following article to view it.

Application, session

A new feature of DB2 for z/OS V9.1, SKIP LOCKED DATA, enables users to implement application-level session locks and allows DB2 commit in the middle. The simple design mode described in this article is used to familiarize yourself with the details of this feature.

Introduction

Database locking policies are important for best Application Performance and Application behavior that ensures data integrity and consistency. In Paul Ilechko's article "Locking strategies for database Access" developerWorks, March 2006), he described the logical session Locking method for database Locking. In addition to transaction locking, this method can control the concurrency of applications at a higher level.

It is difficult to implement the bullet-proof method of passive session locking. However, using the new feature skip locked data introduced in DB2 Version 9.1 for z/OS was initially designed for another completely different purpose, that is, to avoid locking ), DB2 V9.1for z/OS provides a simple and reliable solution.

Problem description

In many scenarios, concurrency control at the application level is necessary, for example:

Ensure that the applications that work on a group of resources that cannot be shared are mutually exclusive. A real example is that a stored procedure synchronizes full-text indexes outside DB2 with the data stored in DB2. Here, the controlled resources are full-text indexes. It is identified by the index name, which is a parameter of the stored procedure. This stored procedure can be called concurrently only when the index name parameters are different.

Controls the maximum number of applications simultaneously performing operations on a resource set.

For passive session locking, the application requires two functions: lock (resource) and unlock ().

The following are some requirements for solutions that make it difficult to implement at the application level:

1. The solution must provide a conceptual session lock for each resource visible between applications.

2. Even if the application holding the session lock ends abnormally, the solution must ensure that the session lock is released.

3. The locking or unlocking mechanism must be independent of the DB2 transaction in the application. That is to say, Midway transaction commit cannot produce side effects of releasing the session lock ).

4. The solution must avoid session lock timeout and long wait locks. The lock () function must be checked immediately and return the non-blocking mode ).

Any session locking DB2 V9.1for z/OS based on DB2 transaction locks must solve the lock conflict issue. Transaction lock conflicts can cause the transaction to roll back sqlcode-911), or cause uncertain waiting for the lock. Without the concept of a database like skip locked data, this problem is very difficult to solve.

Solution Mode

As a solution, we recommend that you define a DB2 table that contains a list of resource identifiers and use the lock and unlock protocols when all applications access resources. The implementation of the lock () function uses the new DB2 function skipping locked data in an SQL fetch statement. This is the key to this solution.

Create a DB2 table for resource locking

Create a DB2 table, which is referred to as the lock table in the following article). It defines resources related to session locks. Assume that the logical session lock range is a resource identifier. In the above full-text index example, This identifier is the index name. Insert a row in this table for each resource.

Listing 1. SQL example of creating a lock table

 
 

  
  
CREATE TABLE LockTable(ResourceId CHAR(10));  
  
  
INSERT INTO LockTable VALUES('INDEX 1');  
  
  
INSERT INTO LockTable VALUES('INDEX 2');  
 
 

The preceding example shows how to fill two full-text index Resources in the lock table. Each index cannot be used by more than one application. This means that app 1 may use index 1, while app 2 uses index 2, but neither application is allowed to use index 1 at the same time.

Use lock/unlock protocols to access resources

Introduce a protocol consisting of two functions: lock (ResourceId) and unlock (). All applications must comply with this Protocol. The specific interface depends on the implementation language, which is irrelevant here. The key point is that all applications must call the lock (resourceId) before accessing the resources controlled by the session lock ). When you no longer need this resource, they should call unlock ().

Implement the lock () function

The implementation of the lock () function must ensure that the caller can immediately obtain the requested result to grant the lock or not grant the lock ). In addition, the locks granted must not be affected by DB2 transactions in the application. Therefore, the implementation of lock () must open an additional DB2 connection in a separate thread. Therefore, the DB2 V9.1for z/OS Implementation of the lock () function consists of the following steps:

1. Start a subthread that processes lock requests.

2. Wait for the sub-thread to send a signal indicating that the lock request has been processed, and the result is available.

3. In the child thread, open a new DB2 connection and obtain a row with the requested resource ID in the lock table. Here, the skip locked data feature is used to obtain only rows not LOCKED by DB2. For example, if another application holds the session lock on this resource, the row cannot be obtained ). DB2 does not wait for the get operation. See the sample code in Listing 2. Now, the request results must be available in the main thread. The subthread waits for the termination signal of the main thread. If a session lock is granted, it holds the DB2 update lock on a row in the lock table until the following conditions occur:

Call unlock () or

The application is terminated.

4. the main thread of the lock () function obtains the result from the subthread. If a lock is granted, the lock () function returns to the caller. Otherwise, the sub-thread is terminated.

List 2. SQL code for implementing lock () in the sub-thread DB2 V9.1for z/OS

 
 

  
  
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

Provides an unlock () function that terminates the sub-thread that still holds a row of the DB2 update lock in the lock table. The termination code in the sub-thread closes the SQL cursor and rolls back the transaction, as shown in listing 3. Therefore, the DB2 update lock for this row is released, and the row is displayed in the next SQL select statement in Listing 2.

Listing 3. SQL code implementing unlock () in child threads

 
 

  
  
CLOSE C1;  
  
  
ROLLBACK WORK;  
 
 

Control the number of applications simultaneously accessing a resource

By making a simple modification to the preceding method, you can control the number of applications simultaneously accessing a resource set:

If the lock table contains duplicate rows, you can use the resource concurrently. The number of rows of a resource in the lock table determines the maximum number of applications that can access the resource at the same time.

Listing 4. SQL example of filling the lock table to control the maximum number of applications simultaneously accessed

 
 

  
  
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');  
 
 

According to this Code, up to two applications can access 'index 1' at the same time, and up to three applications can access 'index 2' at the same time '.

Conclusion

There is a simple and reliable solution mode for implementing session locks at the application level DB2 V9.1for z/OS. It depends on the new feature skip locked data in DB2 Version 9.1 for z/OS and has been successfully applied to a DB2 development project.