Progress
Programming
Handbook


Using Locks to Avoid Record Conflicts

Take another look at the Shipping and Receiving scenario. Figure 13–2 shows how you can use locks to manage concurrent use of the same database record.

Figure 13–2: Multi-user Update Scenario with Locks

Here are the procedures that do this work. The numbers in the margin of the procedures correspond to the numbers in the list that follows the procedures:

p-lock1.p
              /* SHIPPING */ 
              DEFINE VARIABLE qty-shipped AS INTEGER 
                LABEL "Number Shipped". 
              REPEAT: 
/* 1 */            PROMPT-FOR item.item-num. 
/* 2 */            FIND item USING item-num EXCLUSIVE-LOCK. 
                   DISPLAY item-name on-hand. 
                   SET qty-shipped. 
/* 5 */           on-hand = on-hand - qty-shipped. 
                   DISPLAY on-hand. 
/* 6 */       END. 

p-lock2.p
              /* SHIPPING */ 
              DEFINE VARIABLE qty-shipped AS INTEGER 
                LABEL "Number Received". 
              REPEAT: 
/* 3 */            PROMPT-FOR item.item-num. 
/* 4, 7 */         FIND item USING item-num. 
                   DISPLAY item-name on-hand. 
                   SET qty-recvd. 
/* 8 */            on-hand = on-hand - qty-recvd. 
                   DISPLAY on-hand. 
              END. 

  1. The Shipping Department enters and ships an order for 20 parkas (item number 27).
  2. The Shipping Department gets the parka record from the item table, placing an EXCLUSIVE–LOCK on the record. That means that no other user can look at the record until Shipping is finished with it. In that record, the value of the on–hand field is 67.
  3. NOTE: You can read a locked record if you bypass all record locking using NO–LOCK. For more information, see the "Bypassing Progress Lock Protections" section.

  4. The Receiving Department receives and enters 10 parkas (item number 27) into inventory.
  5. The Receiving Department tries to get the parka record from the item table but cannot because Shipping has an EXCLUSIVE–LOCK on the record.
  6. The Shipping Department subtracts 20 from the on–hand value, and returns the record to the database.
  7. Once it returns the record to the database, the procedure releases the EXCLUSIVE–LOCK.
  8. The Receiving Department can now successfully read the record from the database. The on–hand value of the parka record in the database is now 47.
  9. The Receiving Department adds 10 to the on–hand value of 47 and returns the record to the database. Therefore, the final on–hand value for the parka record is 57–exactly what you would expect!

Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095