Managing transaction size

Another way to minimize record locks is to manage the size of the transaction.

For this discussion, a transaction is interactive. (Commitment control can also be used for batch applications, which often can be considered a series of transactions.) Many of the same considerations apply to batch applications, which are discussed in Commitment control for batch applications.

You can lock a maximum of 500 000 000 records during a transaction for each journal associated with the transaction. You can reduce this limit by using a Query Options File (QAQQINI). Use the QRYOPTLIB parameter of the Change Query Attributes (CHGQRYA) command to specify a Query Options File for a job to use. Use the COMMITMENT_CONTROL_LOCK_LEVEL value in the Query Options File as the lock limit for the job. The lock limit value is cached internally for each commitment definition the first time a journaled resource is placed under commitment control. If the lock limit is changed after that point, the cached value must be refreshed for it to become effective for that commitment definition. Any call to the Retrieve Commit Information (QTNRCMTI) API refreshes the cached value in the calling job. The new value will not apply to transactions that started before the cache is refreshed.

When choosing the lock level for your records, consider the size of your transactions. Use size to determine how long records are locked before a transaction ends. You must decide if a commit or rollback operation for commitment control is limited to a single use of the Enter key, or if the transaction consists of many uses of the Enter key.

For example, for an order entry application, a customer might order several items in a single order requiring an order detail record and an inventory master record update for every item in the order. If the transaction is defined as the entire order and each use of the Enter key orders an item, all records involved in the order are locked for the duration of the entire order. Therefore, often used records (such as inventory master records) might be locked for long periods of time, preventing other work from progressing. If all items are entered with a single Enter key using a subfile, the duration of the locks for the entire order is minimized.

In general, the number and duration of locks must be minimized so several workstation users can access the same data without long waiting periods. You can do this by not holding locks while the user is entering data on the display. Some applications might not require more than one workstation user accessing the same data. For example, in a cash posting application with many open item records per customer, the typical approach is to lock all the records and delay them until a workstation user completes posting the cash for a given receipt.

If the workstation user presses the Enter key several times for a transaction, it is possible to perform the transaction in a number of segments. For example:

• The first segment is an inquiry in which the workstation user requests the information.
• The second segment is a confirmation of the workstation user's intent to complete the entire transaction.
• The third segment is retrieval and update of the affected records.

This approach allows record locking to be restricted to a single use of Enter.

This inquiry-first approach is normally used in applications where a decision results from information displayed. For example, in an airline reservation application, a customer might want to know what flight times, connecting flights, and seating arrangements are available before making a decision on which flight to take. After the customer makes a decision, the transaction is entered. If the transaction fails (the flight is now full), the rollback function can be used and a different request entered. If the records are locked from the first inquiry until a decision is made, another reservation clerk will be waiting until the other transaction is complete.