Emulate RPG’s Pessimistic Locking in SQL

I’m writing a Java program to replace several RPG programs that access unjournaled database files. The current programs all use the same logic: obtain a lock on a record, allow the user to make zero or more changes on various “screens,” then save the changes (potentially not changing anything). I need to make my Java program behave like the original RPG programs.

I have always been able to use SQL, but no transactions, due to the lack of journaling. I thought I found a solution today, but it didn’t work as I implemented it. I tried setting the isolation level on a select to “RR.” However, I was still able to use RPG programs to open and modify that record.

–Mike

Mike,

A traditional RPG “green screen” program works like this:

a. The user requests information.

b. The RPG program obtains an UPDATE lock for the row in the database table. (Only one UPDATE lock is allowed on a row in a table; no two RPG programs can get the UPDATE lock simultaneously.)

c. The RPG program waits indefinitely for the user to make a change or cancel the operation.

d. The data is updated (if requested) and the row lock is released.

This type of locking is called “pessimistic” locking because the program assumes it has to keep the lock on the row even if the user doesn’t need to update it. Pessimistic locking can be problematic because a user can leave a row locked for hours, which can potentially block other processes on the system that need to update the same data from doing their job. The one good thing about pessimistic locking is that you as a programmer don’t have to worry that something else might change the data between the read and the update.

How do we implement pessimistic locking in SQL? Since journaling isn’t available in your environment, you cannot make full use of transaction processing in DB2 for i. However, you’re on the right track because you can use the REPEATABLE READ transaction isolation level in SQL to create a row lock that will last for the duration of the transaction. The problem with your attempt to use the SELECT statement’s isolation clause is that, when running without a transaction, the lock only lasts for the duration of the statement’s execution. In other words, the lock was let go as soon as the statement finished.

I’ll briefly outline the code steps to do create a pessimistic lock here. The task is to write a Java-based maintenance screen against the sample QIWS/QCUSTCDT table. When a customer number is requested by the Java program, no RPG programs can request an UPDATE lock.

Assuming your DB2 connection’s default isolation level is READ UNCOMMITTED:

1. User enters the customer number to edit.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
/* this will begin a transaction boundary and this isolation level 
will put read locks on the row(s) read */

2.

SELECT * FROM QIWS/QCUSTCDT WHERE CUSNUM=192837   
/* a read lock will be put on the record */

3. The user does his editing; an RPG program cannot get an UPDATE lock until this is done.

4.

COMMIT 
/* end the transaction and release the lock */

5.

UPDATE QIWS/QCUSTCDT SET column values WHERE CUSNUM=192837  
/* Save the changes */

If your program needs to hold locks on multiple rows in different tables, each additional SELECT should be done at step 2 so that all locks are held at once.

Before the COMMIT in step 4, the lock can be verified in another session by using the DSPRCDLCK command:

DSPRCDLCK FILE(QIWS/QCUSTCDT)

This command will show something like this for the locked record(s):

                          Display Member Record Locks 
                                                             System:   Z752X53
 File . . . . . . . . :   QCUSTCDT        Member . . . . . . . :   CUSTCDT
   Library  . . . . . :     QIWS

          Record                                            Lock
          Number  Job         User        Number  Status    Type
              11  QPADEV0001  MSANSOTERR  001449  HELD      READ

Unfortunately, since journaling is not active, the UPDATE statement can’t participate in the transaction and the read/update combination is not atomic in nature. Therefore, it is possible that another waiting process (like an RPG program) may be able to obtain its own UPDATE lock on the same row in between the end of the transaction (COMMIT) and the UPDATE statement.

For the record, I would not implement pessimistic locking in an SQL-based Java GUI application unless absolutely necessary. I can’t think of an instance where I’d ever implement pessimistic locking in a Web-based application.

In contrast, I would use “optimistic” locking, where a lock is not obtained until the data is going to be changed. The problem with optimistic locking is that the program doesn’t know if another change has been made on the system in between the time the data was read and the time the data is being updated. The program typically has to check every column to see if some data has changed which is often irritating to code and difficult to maintain.

However, implementing the use of “last changed” timestamps or a “version #” column (incremented by an application or a trigger every time a row changes) can provide a relatively safe way to implement optimistic locking without having to code a nightmare; the program can check if the “last changed” stamp has changed or if the version # has changed since the data was read and presented to the user. The big question here is do you already have these columns on your tables or can you add them without breaking everything else? In the long run, optimistic locking relieves the database from tracking a bunch of locks and prevents users from inadvertently locking rows for long periods of time.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot