About Paul Guggenheim & Associates

1. Transaction and Record ScopingandHow They Relate to Record Locksby Paul GuggenheimPaul Guggenheim & Associates, Inc.(847) 926-9800www.pgasmarts.compaul@pgasmarts.com

2. AboutPaul Guggenheim & Associates • Working in Progress since 1984 and training Progress programmers since 1986, consulting in MFG/PRO since 1989 • Designed six comprehensive Progress training courses covering all levels of expertise • TailorPro Partner • Tools4Progress Partner • Jargon Authorized Reseller • Major consulting clients include American Academy of Orthopaedic Surgeons, Foxwoods Casino, New York Times and Textron Fastening Systems

3. Progress Record Lock Types • There are 3 types of record locks: • No-Lock • Share-Lock • Exclusive-lock

4. Progress Record Lock Types

5. Progress Record Lock Types • A NO-LOCK record cannot be updated to an EXCLUSIVE-LOCK. • A SHARE-LOCK record can be updated to an EXCLUSIVE-LOCK.

6. Acquiring Record Locks • By default, all record reads except a browse acquire a SHARE-LOCK. • A browse acquires a NO-LOCK record. • Record reads occur on the FOR, FIND and GET statements.

7. Acquiring Record Locks • When a record is updated, an EXCLUSIVE-LOCK is always acquired. • This occurs as the record is written to the record buffer on the assign portion of an INSERT, UPDATE, SET and ASSIGN statements.

8. Update vs. Set and Assign • With the UPDATE statement, the lock is raised to EXCLUSIVE-LOCK only if field values are changed. • With the SET and ASSIGN statements, the EXCLUSIVE-LOCK is always raised, since Progress doesn’t know at that point whether data in the screen buffer has changed.

9. Releasing Record Locks • EXCLUSIVE-LOCKS are released at the end of the transaction block. • SHARE-LOCKS are release at the end of the transaction block OR the end of the record scope, whichever is larger or more encompassing. • The RELEASE statement affects when the SHARE-LOCK is released…more on that later.

10. What is a transaction? • A transaction is a unit of work that must be done completely or not at all. • Transactions are designed to ensure data integrity. • All transactions are scoped to blocks.

11. Default Transaction • The default transaction block is the outermost block with transaction properties that contains statements that modify the database. • Or, more simply, the default transaction block is the outermost block that updates the database.

12. Default Transaction • Which blocks have transaction properties by default? • FOR • REPEAT • PROCEDURE

13. Default Transaction • What statements modify the database? • INSERT, CREATE, DELETE a database record • ASSIGN, SET, UPDATE a database field • Record reading statements that contain the keyword EXCLUSIVE-LOCK.

14. Overriding Default Transactions • Use the transaction keyword on the following blocks to override the default transaction: • FOR • REPEAT • DO

15. More Transaction Rules • Each Progress session can have at most one transaction. • Each iteration of a transaction block is considered a complete transaction. • Once Progress has committed a transaction, that transaction cannot be undone.

16. Testing Transactions • Test transactions using the STOP key (Unix = CTRL-C, Windows = CTRL-BREAK). • Do NOT use the END-ERROR key (Unix = F4, Windows = ESC). • Using the END-ERROR key may undo a sub-transaction instead.

17. Simple Transaction Example /* b-trn1.p */ For each customer with 1 column: display cust-num balance. update customer except cust-num balance. End. /* for each */

18. Simple Transaction Example • Of the two blocks, PROCEDURE and FOR, the FOR each block is the outer most block that contains statements that update the database (update statement). Therefore, the FOR block is the transaction block.

19. Ensuring Integrity with related records def var extprice like item.price. for each order: display order-num. update order-date promise-date ship-date. for each order-line of order: display order-line except order-num extended-price backorder. display price * qty @ extprice label "Extension". set price qty disc. display price * qty @ extprice. end. /* for each order-line */ end. /* for each order */

20. Nested blocks with related records example • The FOR each order block is the transaction block because it is the outer most block that updates the database. • Therefore, a single order and its related order-lines constitute one transaction.

21. Creating Larger Transactions do transaction: for each customer with 1 column: display cust-num balance. update customer except cust-num balance. end. /* for each */ end. /* do transaction */

22. Creating Larger Transactions • The DO block by virtue of the transaction keyword, overrides the FOR each customer block as the transaction block. • Therefore, this transaction won’t be committed until the program reaches the end statement of the do block.

23. The effects of Large Transactions • Large transactions possess some undesirable characteristics: • Locks large numbers of records • Before image files grow large • Crash recovery takes longer • Transactions should only be large enough to satisfy the requirements of the application. • For example, ensuring data integrity among related records

24. Creating Smaller Transactions /* b-trn4.p */ def var extprice like item.price. for each order: display order-num. do transaction: update order-date promise-date ship-date. end. /* do transaction */ for each order-line of order transaction: display order-line except order-num extended-price backorder. display price * qty @ extprice label "Extension". set price qty disc. display price * qty @ extprice. end. /* for each order-line */ end. /* for each order */

25. Creating Smaller Transactions • The DO TRANSACTION block can also be used to create smaller transactions. • Smaller transactions are appropriate when updating related records are not dependent on each other.

26. Creating Smaller Transactions • The outer FOR EACH is not the transaction block, since it contains no statements that update the database. • If the transaction in the inner FOR EACH block is undone, only the last order-line is undone since each iteration of a transaction block is a transaction. The previous order-lines will be committed, since those iterations completed.

27. Beware of Exclusive-Lock For each customer exclusive-lock: display cust-num name. for each order of customer: display order-num. update order-date ship-date. end. /* for each order */ End. /* for each customer */

28. Beware of Exclusive-Lock • Be careful to only use EXCLUSIVE-LOCK when you are sure that you will be updating that record. • The EXCLUSIVE-LOCK keyword counts as updating the database, thus causing that block to become the transaction block.

29. Undoing Variables in Transactions /* undone.p */ def var undone as log init yes. repeat: undone = no. undo,leave. end. display undone.

30. Undoing Variables in Transactions • Changes made to variables outside of transactions and subtransactions are never undone. • By default, changes made to variables are undone within a transaction or subtransaction. • Use the NO-UNDO keyword in the DEFINE VARIABLE, DEFINE WORK-TABLE, and DEFINE TEMP-TABLE statements to prevent variables, work tables, and temp tables from being undone in transactions and subtransactions.

31. Transactions in Procedures and Triggers • Any transactions active when another procedure is called or a trigger is activated remain active. • Use the TRANSACTION function to determine if a transaction is currently active.

32. Record Scope Definition • Record scope is the section of a program during which a record buffer can exist for a given record. • In general, a record is scoped to the outermost block that references that record. • Record scope effects: • How long the record is held in the record buffer • When changes to the record are written back to the database • How long record locks are held

33. Record Scope Definition /* rs1.p */ for each customer: display cust-num name. for each order of customer: display order-num order-date promise-date. for each order-line of order, item of order-line: display line-num item-name qty order-line.price. end. /* for each order-line of order */ end. /* for each order of customer */ end. /* for each customer */

34. Weak Record Scope • A record has weak scope if it is referenced in a FOR, PRESELECT, procedure, or trigger block. • PROGRESS will automatically raise the scope of a weak scoped record to the next outermost containing block if there is a free reference to that record. • A free reference is a reference to a record in certain statements or functions in a containing block.

35. Weak Record Scope • Some statements and functions that count as free references are: • DISPLAY • FIND • AVAILABLE • RECID

36. Weak Record Scope • Some statements and functions that don’t count as free references are: • CAN-FIND • ACCUM • PROMPT-FOR • ENABLE

37. Strong Record Scope • A record has strong scope if it is referenced in a REPEAT FOR or DO FOR block. • Blocks that have strong scope will not allow a reference to that record in the containing block of that strong scoped block.

38. Fickle Record Scope • A REPEAT block without a FOR keyword is a fickle scope block. • It will have record scope if it is next to a strong scoped block. • It will not have record scope if it is next to a weak scoped block or another REPEAT block without a FOR keyword.

39. Record Lock Duration and the Release Statement To Review: • EXCLUSIVE-LOCKS are released at the end of the transaction block. • SHARE-LOCKS are release at the end of the transaction block OR the end of the record scope, whichever is larger or more encompassing.

40. Share-Lock Release Scenarios Example: For each customer: update customer. End.

41. Share-Lock Release Scenarios Example: Do transaction: for each customer: update customer. end. end.

42. Share-Lock Release Scenarios Example: For each customer: do transaction: update customer. end. end.

43. Release Statement • A RELEASE statement does the following: • Writes the record to disk, if changed • Clears the record buffer • Outside a transaction, downgrades a SHARE-LOCK to a NO-LOCK for a record • Within a transaction, acts as a flag which causes the record to go NO-LOCK at the end of a transaction

44. Get/Find Current No-Lock Statements • Use GET or FIND CURRENT statements with the NO-LOCK keyword to re-read the record with NO-LOCK after updating the record. • Like the RELEASE statement, reading the record NO-LOCK will downgrade the lock outside the transaction and flag for downgrading the lock inside the transaction. • Unlike the RELEASE statement, the record buffer is not cleared, allowing the record to continue to be referenced in the program.

45. Event Driven Locking Examples • Example 1 – re-read record no-lock on choose of bupd do: if available customer then do with 1 column view-as dialog-box title "Customer Update": find CURRENT customer exclusive-lock. display cust-num balance. update customer except cust-num balance comments. run dispcust. find CURRENT customer no-lock. end. end.

46. Event Driven Locking Examples • Example 1 – re-read record NO-LOCK • The scope of the customer record is the procedure block. • The trigger block is the transaction block. • If the customer record is not re-read, the EXCLUSIVE-LOCK is downgraded to a SHARE-LOCK at the end of the trigger block because the record scope is larger (procedure block).

47. Event Driven Locking Examples • Example 1 – re-read record NO-LOCK • By re-reading the record NO-LOCK inside the transaction, flags the record to be downgraded to NO-LOCK outside the transaction without clearing the record buffer.

48. Event Driven Locking Examples • Example 2 – use a named buffer on choose of bupd do: def buffer custbuf for customer. if available customer then do with 1 column view-as dialog-box title "Customer Update": find custbuf where custbuf.cust-num = customer.cust-num exclusive-lock. display custbuf.cust-num custbuf.balance. update custbuf except cust-num balance comments. run dispcust. end. end.

49. Event Driven Locking Examples • Example 2 – use a named buffer • The custbuf record buffer is scoped to the trigger block. • When the trigger block ends, not only does the transaction end, but the custbuf scope ends, therefore releasing the lock. • Meanwhile, the customer record buffer remains in NO-LOCK status.

50. Resolving a Deadly Embrace