Using Stored Procedures to Lock Application Objects

John Maenpaa

We live in a massively multithreaded world. Lots of actions are occurring around us. We are jumping from one task to another. Every now and then, we have to slow down and stop what we are doing to let another task make its way in the stream of activities. That task isn’t necessarily ours.

In practical terms, we often need to keep our applications from processing two transactions for the same customer at the same time. In particular, we need to ensure that only one person is editing an application object at a time. A customer service representative, for example, could be editing critical information about a customer and they want to ensure no one else is changing anything about that customer at the same time.

This type of locking will likely need to span different application implementations and programming languages. To span these gaps, we want to store the locks in the database. To make sure each programming language uses the table the same way, we will use stored procedures to maintain the content of the table.

In our case, the customer service representative may need several days to complete their work. The locks we take on their behalf need to persist while they are off doing other tasks.

But we also want the locks to expire after a certain period of time. In this example, we’ll use a 7 day period from the last change made to the object (as measured from the last time we check to make sure they have the lock).

The Application Lock Table

Our system uses DB2 for z/OS, so the samples here are appropriate to that version of DB2. They should be easily adaptable to DB2 for LUW as well. The table we’ll use is fairly simple. We have a CUSTOMER_ID as the primary key along with two columns to contain the OPERATOR_ID and their DEPARTMENT_ID.

The LOCK_TS adds the ability to expire the lock. For the most part, we’ll leave the expired rows in the table. We don’t want to wait for a background process to get around to deleting expired locks. But we will do a periodic clean up program to keep really old expired locks from cluttering up the database. Our Database Administrators will be most appreciative.

The contents of the table will often grow and shrink as our representatives complete work and begin new tasks. I’ve added the VOLATILE keyword here so that DB2 will be aware of this and act accordingly. On DB2 for z/OS, the use of VOLATILE causes the DB2 optimizer to prefer index access paths.

-- Lock Table (partial DDL)

CREATE TABLE APPL_LOCK

( CUSTOMER_ID BIGINT NOT NULL

, OPERATOR_ID CHAR(8) NOT NULL

, DEPARTMENT_ID CHAR(8) NOT NULL

, LOCK_TS TIMESTAMP NOT NULL

, PRIMARY KEY (CUSTOMER_ID)

)

VOLATILE ;

CREATE UNIQUE INDEX APPL_LOCK_IX1

ON APPL_LOCK

( CUSTOMER_ID )

;

Acquire the Lock

We want a stored procedure that will attempt to obtain our application lock. If we already have the lock, we want to update the timestamp to extend the expiration for another 7 days. If we cannot obtain the lock, then we want to find out who has the lock.

The stored procedure contains several SQL statements because the situation could vary:

The object is not locked and we obtain it for our user

Our user already has the lock and we update the timestamp

Someone else has the lock

Someone else has an expired lock

The first step of the stored procedure is to attempt to insert the row for the lock. If we succeed at this we have successfully inserted the lock for our user and we can return. If the insert received an SQLCODE of -803 for a duplicate row, then we have more work to do.

The second statement tries to update the existing row. The two conditions handle the cases where our user already has the lock or a prior lock has expired. We don’t care who had the prior lock. If we receive an SQLCODE of +100 then we failed to update the lock timestamp, indicating that someone else has a current lock on the customer.

The last statement in the stored procedure retrieves the information about the current owner of the lock. We return this to the caller to inform the user. That way our business users can coordinate their changes and provide better customer service than if we kept them in the dark.

CREATE PROCEDURE GET_APPL_LOCK

( IN P_CUSTOMER_ID BIGINT

, IN P_OPERATOR_ID CHAR(8)

, IN P_DEPARTMENT_ID CHAR(8)

, OUT P_LOCK_FL CHAR(1)

, OUT P_OLD_OPERATOR_ID CHAR(8)

, OUT P_OLD_DEPARTMENT_ID CHAR(8) )

VERSION V1 LANGUAGE SQL

NOT DETERMINISTIC MODIFIES SQL DATA

DISALLOW DEBUG MODE WITH EXPLAIN

QUALIFIER <SCHEMA> PACKAGE OWNER <SCHEMA> VALIDATE BIND

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0 ;

DECLARE V_SQLCODE INTEGER DEFAULT 0 ;

DECLARE V_OLD_OPERATOR_ID CHAR(8) DEFAULT ' ' ;

DECLARE V_OLD_DEPARTMENT_ID CHAR(8) DEFAULT ' ' ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND

SET V_SQLCODE = SQLCODE ;

-- Initialize

SET P_OLD_OPERATOR_ID = ' ' ;

SET P_OLD_DEPARTMENT_ID = ' ' ;

SET P_LOCK_FL = 'N' ;

-- Insert new lock

INSERT INTO APPL_LOCK

( CUSTOMER_ID

, OPERATOR_ID

, DEPARTMENT_ID

, LOCK_TS )

VALUES

( P_CUSTOMER_ID

, P_OPERATOR_ID

, P_DEPARTMENT_ID

, CURRENT_TIMESTAMP );

CASE V_SQLCODE

WHEN 0 THEN

SET P_LOCK_FL = 'Y' ;

RETURN ;

WHEN -803 THEN

SET P_LOCK_FL = ' ' ;

SET V_SQLCODE = 0 ; -- for next stmt

ELSE

SIGNAL SQLSTATE VALUE '38001'

SET MESSAGE_TEXT = 'ERROR IN #001 ' ||

CHAR(V_SQLCODE) || ' ' || CHAR(P_CUSTOMER_ID) ;

END CASE ;

-- Update our timestamp (if it is ours)

UPDATE APPL_LOCK

SET OPERATOR_ID = P_OPERATOR_ID

, DEPARTMENT_ID = P_DEPARTMENT_ID

, LOCK_TS = CURRENT_TIMESTAMP

WHERE CUSTOMER_ID = P_CUSTOMER_ID

AND ( OPERATOR_ID = P_OPERATOR_ID OR

LOCK_TS < ( CURRENT_TIMESTAMP - 7 DAYS ) )

SKIP LOCKED DATA;

CASE V_SQLCODE

WHEN 0 THEN

SET P_LOCK_FL = 'Y' ;

RETURN ;

WHEN +100 THEN

SET P_LOCK_FL = 'N' ;

SET V_SQLCODE = 0 ;

ELSE

SIGNAL SQLSTATE VALUE '38001'

SET MESSAGE_TEXT = 'ERROR IN #002 ' ||

CHAR(V_SQLCODE) || ' ' || CHAR(P_CUSTOMER_ID) ;

END CASE ;

-- Who has it?

SELECT OPERATOR_ID, DEPARTMENT_ID

INTO V_OLD_OPERATOR_ID, V_OLD_DEPARTMENT_ID

FROM APPL_LOCK

WHERE CUSTOMER_ID = P_CUSTOMER_ID

WITH UR ;

CASE V_SQLCODE

WHEN 0 THEN

SET P_OLD_OPERATOR_ID = V_OLD_OPERATOR_ID ;

SET P_OLD_DEPARTMENT_ID = V_OLD_DEPARTMENT_ID ;

WHEN +100 THEN

SET P_OLD_OPERATOR_ID = ' ' ;

SET P_OLD_DEPARTMENT_ID = ' ' ;

ELSE

SIGNAL SQLSTATE VALUE '38001'

SET MESSAGE_TEXT = 'ERROR IN #003 ' ||

CHAR(V_SQLCODE) || ' ' || CHAR(P_CUSTOMER_ID) ;

END CASE ;

RETURN ;

Error Handling

Did you notice the condition handler?

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND

SET V_SQLCODE = SQLCODE ;

This handler sets the V_SQLCODE whenever we receive an SQL exception, warning, or a not found condition. It does not set the variable if the SQLCODE was 0. We must be careful to ensure the V_SQLCODE contains a value of 0 prior to the execution of any of our CASE statements that use it.

This is not the way condition handlers were designed to work in DB2, but the method does work.

Issues with the original stored procedure

When the original stored procedure was coded, there were a few things that we identified as needing changes.

First, the definition used DETERMINISTIC instead of NOT DETERMINISTIC. Can you guess what happened there? Yes, indeed, we had completely inconsistent locking behavior and rarely hit the -803 condition on the INSERT. DB2 can bypass the call to a DETERMINISTIC stored procedure and return the same results as a prior call. We can’t stress enough the importance of getting this right. The problem is unlikely to show up in your unit testing. It’ll look as though the stored procedure works. You may even get into production thinking your procedure is doing the right thing. But one day, when you are trying to figure out why your objects are corrupted due to multiple simultaneous updates, you will find this. Code reviews can catch this, use them… please.

Second, the UPDATE statement was waiting on locks. In this case, we don’t need to wait. If another unit of work has the lock on our row then we’ll fail in obtaining the lock. So, we can SKIP LOCKED DATA and still get the answer we need. But, can we be sure it is safe and that we’ll get the right answer? We have two predicates in the UPDATE statement. The first:

CUSTOMER_ID = P_CUSTOMER_ID

would be expected to use our primary key index. This is where skipping the locked rows comes into play. First, we need to make sure we have row level locking set on this table. If we don’t then we’ll receive failures that should have succeeded. The use of the VOLATILE keyword makes sure we are indexing directly to our target row. Or at least we expect that it does. Maybe we’d better verify with an EXPLAIN? If we had a tablespace scan with an isolation of REPEATABLE READ, then one thread would lock all of the rows and the second wouldn’t see any of them.

The second predicate:

AND ( OPERATOR_ID = P_OPERATOR_ID

OR LOCK_TS < ( CURRENT_TIMESTAMP - 7 DAYS ) )

checks to see if we have the lock or if the current lock has expired. We expect that if we have the lock, then we would also have the DB2 lock on the data row (if there is one) and this row would not have been skipped.

The SELECT statement would also wait on locks. The quick addition of WITH UR lets us get the data and return to our user quickly. This helps when there are multiple simultaneous threads running and the other user beat us to the lock.

Release the Lock

When the business user has completed their work on a customer and saved it, we want to release the application lock. A second stored procedure provides for this.

In this stored procedure, we make sure the lock was held by this operator by adding that predicate to the DELETE statement. If the row was not found, we retrieve the current holder of the lock.

When the SELECT statement finds no row then there is no lock entry for the customer. We go ahead and indicate the release of the lock was successful.

CREATE PROCEDURE RELEASE_APPL_LOCK

( IN P_CUSTOMER_ID BIGINT

, IN P_OPERATOR_ID CHAR(8)

, OUT P_RETURN_CD CHAR(1)

, OUT P_OLD_OPERATOR_ID CHAR(8)

, OUT P_OLD_DEPARTMENT_ID CHAR(8) )

VERSION V1 LANGUAGE SQL

NOT DETERMINISTIC MODIFIES SQL DATA

DISALLOW DEBUG MODE WITH EXPLAIN

QUALIFIER <SCHEMA> PACKAGE OWNER <SCHEMA> VALIDATE BIND

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0 ;

DECLARE V_SQLCODE INTEGER DEFAULT 0 ;

DECLARE V_OLD_OPERATOR_ID CHAR(8) DEFAULT ' ' ;

DECLARE V_OLD_DEPARTMENT_ID CHAR(8) DEFAULT ' ' ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND

SET V_SQLCODE = SQLCODE ;

-- Initialize

SET P_OLD_OPERATOR_ID = ' ' ;

SET P_OLD_DEPARTMENT_ID = ' ' ;

SET P_RETURN_CD = 'N' ;

-- Delete (if it is ours)

DELETE FROM APPL_LOCK

WHERE CUSTOMER_ID = P_CUSTOMER_ID

AND OPERATOR_ID = P_OPERATOR_ID ;

CASE V_SQLCODE

WHEN 0 THEN

SET P_RETURN_CD = 'Y' ;

RETURN ;

WHEN +100 THEN

SET P_RETURN_CD = 'N' ;

SET V_SQLCODE = 0 ;

ELSE

SIGNAL SQLSTATE VALUE '38001'

SET MESSAGE_TEXT = 'ERROR IN #001 ' ||

CHAR(V_SQLCODE) || ' ' || CHAR(P_CUSTOMER_ID) ;

END CASE ;

-- Who has it now?

SELECT OPERATOR_ID, DEPARTMENT_ID

INTO V_OLD_OPERATOR_ID, V_OLD_DEPARTMENT_ID

FROM APPL_LOCK

WHERE CUSTOMER_ID = P_CUSTOMER_ID

WITH UR ;

CASE V_SQLCODE

WHEN 0 THEN

SET P_OLD_OPERATOR_ID = V_OLD_OPERATOR_ID ;

SET P_OLD_DEPARTMENT_ID = V_OLD_DEPARTMENT_ID ;

WHEN +100 THEN

SET P_RETURN_CD = 'Y' ;

ELSE

SIGNAL SQLSTATE VALUE '38001'

SET MESSAGE_TEXT = 'ERROR IN #002 ' ||

CHAR(V_SQLCODE) || ' ' || CHAR(P_CUSTOMER_ID) ;

END CASE ;

RETURN ;

Inquire on the Lock

Since we’re using stored procedures to maintain our application locks, we’ll create one for the inquiry requirement as well. This fairly straightforward SELECT statement does the job well and we ignore expired locks.

Additionally, we may want to consider a List stored procedure that would retrieve all of the locks held by an operator or a department. That would be a good case for returning a result set cursor from our stored procedure. I have not included this here.

CREATE PROCEDURE INQUIRE_APPL_LOCK

( IN P_CUSTOMER_ID BIGINT

, OUT P_RETURN_CD CHAR(1)

, OUT P_OPERATOR_ID CHAR(8)

, OUT P_DEPARTMENT_ID CHAR(8)

, OUT P_LOCK_TS TIMESTAMP )

VERSION V1 LANGUAGE SQL

NOT DETERMINISTIC MODIFIES SQL DATA

DISALLOW DEBUG MODE WITH EXPLAIN

QUALIFIER <SCHEMA> PACKAGE OWNER <SCHEMA> VALIDATE BIND

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0 ;

DECLARE V_SQLCODE INTEGER DEFAULT 0 ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND

SET V_SQLCODE = SQLCODE ;

-- Initialize

SET P_OPERATOR_ID = ' ' ;

SET P_DEPARTMENT_ID = ' ' ;

SET P_RETURN_CD = 'N' ;

-- Get the current lock holder information

SELECT OPERATOR_ID

, DEPARTMENT_ID

, LOCK_TS

INTO P_OPERATOR_ID

, P_DEPARTMENT_ID

, P_LOCK_TS

FROM APPL_LOCK

WHERE CUSTOMER_ID = P_CUSTOMER_ID

AND LOCK_TS > ( CURRENT_TIMESTAMP - 7 DAYS ) ;

CASE V_SQLCODE

WHEN 0 THEN

SET P_RETURN_CD = 'Y' ;

WHEN +100 THEN

SET P_RETURN_CD = 'N' ;

ELSE

SIGNAL SQLSTATE VALUE '38001'

SET MESSAGE_TEXT = 'ERROR IN #001 ' ||

CHAR(V_SQLCODE) || ' ' || CHAR(P_CUSTOMER_ID) ;

END CASE ;

RETURN ;

Transfer the Lock

Occasionally, the person working on updating a customer record cannot complete the task. They might not have the right experience or maybe they had to leave for the day. We want the ability to transfer their work to another person.

Who initiates the transfer and how that works can be a complicated matter. Supervisors should be able to transfer work to themselves or others. The user interface needs to inquire on the locks and then take the appropriate action based on the user’s authority.

CREATE PROCEDURE TRANSFER_APPL_LOCK

( IN P_CUSTOMER_ID BIGINT

, IN P_FROM_OPERATOR_ID CHAR(8)

, IN P_TO_OPERATOR_ID CHAR(8)

, IN P_TO_DEPARTMENT_ID CHAR(8)

, OUT P_RETURN_CD CHAR(1)

, OUT P_OLD_OPERATOR_ID CHAR(8)

, OUT P_OLD_DEPARTMENT_ID CHAR(8) )

VERSION V1 LANGUAGE SQL

NOT DETERMINISTIC MODIFIES SQL DATA

DISALLOW DEBUG MODE WITH EXPLAIN

QUALIFIER <SCHEMA> PACKAGE OWNER <SCHEMA> VALIDATE BIND

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0 ;

DECLARE V_SQLCODE INTEGER DEFAULT 0 ;

DECLARE V_OLD_OPERATOR_ID CHAR(8) DEFAULT ' ' ;

DECLARE V_OLD_DEPARTMENT_ID CHAR(8) DEFAULT ' ' ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND

SET V_SQLCODE = SQLCODE ;

-- Initialize

SET P_OLD_OPERATOR_ID = ' ' ;

SET P_OLD_DEPARTMENT_ID = ' ' ;

SET P_RETURN_CD = 'N' ;

-- Do the Transfer (if it is ours)

UPDATE APPL_LOCK

SET OPERATOR_ID = P_TO_OPERATOR_ID

, DEPARTMENT_ID = P_TO_DEPARTMENT_ID

, LOCK_TS = CURRENT_TIMESTAMP

WHERE CUSTOMER_ID = P_CUSTOMER_ID

AND OPERATOR_ID = P_FROM_OPERATOR_ID ;

CASE V_SQLCODE

WHEN 0 THEN

SET P_RETURN_CD = 'Y' ;

RETURN ;

WHEN +100 THEN

SET P_RETURN_CD = 'N' ;

SET V_SQLCODE = 0 ;

ELSE

SIGNAL SQLSTATE VALUE '38001'

SET MESSAGE_TEXT = 'ERROR IN #001 ' ||

CHAR(V_SQLCODE) || ' ' || CHAR(P_CUSTOMER_ID) ;

END CASE ;

-- Who has it now?

SELECT OPERATOR_ID, DEPARTMENT_ID

INTO V_OLD_OPERATOR_ID, V_OLD_DEPARTMENT_ID

FROM APPL_LOCK

WHERE CUSTOMER_ID = P_CUSTOMER_ID

WITH UR ;

CASE V_SQLCODE

WHEN 0 THEN

SET P_OPER_ID = V_OPERATOR_ID ;

WHEN +100 THEN

SET P_OPER_ID = ' ' ;

ELSE

SIGNAL SQLSTATE VALUE '38001'

SET MESSAGE_TEXT = 'ERROR IN #002 ' ||

CHAR(V_SQLCODE) || ' ' || CHAR(P_CUSTOMER_ID) ;

END CASE ;

RETURN ;

Conclusion

These stored procedures do not implement any authorization checking or validation of the incoming data. We rely on the application business layer to do that. We are simply facilitating that work by taking advantage of the power of SQL.

The GET_APPL_LOCK procedure is coded to return success as quickly as possible. When the less frequent conflicts occur then the additional statements are executed. The expectation is that we are considering performance. If the UPDATE statement would be more successful than the INSERT statement then we’d want to consider reorganizing the way the procedure works.

The examples here were taken from our current development environment. I modified them to be more generic than they really are, so please don’t expect them to work as is. And, be careful to verify that my assumptions on how this works are correct for your application and environment.

With Db2 for z/OS customers expanding their business presence into the mobile client world, solution developers are seeking more efficient ways to leverage existing data and business logic to support...

This article will help DB2 DBAs and Users to proactively monitor their most critical and live systems on continuous basis without any impact to production and analyze the data on key points to identif...