OpenEdge Record Lock Anti-pattern

November 30, 2007 · Filed Under Development, Performance Tuning 

Everyone knows that if you need an exclusive lock on a database record in the 4gl you do this:

find customer exclusive-lock where customer.id eq 1 no-error.

However, this will not wait forever. It waits for lkwtmo, which defaults to 30 minutes, then raises the STOP condition. Well, what if you really want to wait longer than 30 minutes (or whatever lkwtmo is set to)? We have occasionally seen developers do this (gasp!):

find customer where customer.id eq 1 exclusive-lock no-wait no-error.
do while locked customer: 

  find customer where customer.id eq 1 exclusive-lock no-wait no-error.

end.

This will certainly work. However, if there is actually a lock on that row, this code will SLOW the database AND saturate the CPU of the client machine on which this code is running. That is an unbridled loop that will iterate thousands of times a second. Each iteration will cause the database to do work as well slowing all access (each lock attempt will show in promon as 1 Commit and as 3 DBRequests).
For example, on a 2.1ghz CPU that loop will iterate 40,000 times per second.

There are a number of good alternatives to the above design. One would be the following:

getLock: do on stop undo getLock, retry getLock:
  if retry then do:/* you may want to log something here 
                      or possibly put an upper limit on the wait time
                      by counting the number of times it hits this code
                      The below will cause an error to be returned after
                      60 minutes assuming the default lkwtmo */

     cnt = cnt + 1.
     if cnt eq 2 then return error “Unable to get a lock on customer”.
  end.

  find customer exclusive-lock where customer.id eq 1 no-error.

end.

As you can see, it is much, much better to let the Progress VM deal with getting the lock in this scenario.

Tags:

Comments

Leave a Reply

You must be logged in to post a comment.