Progress
Programming
Handbook
Choosing Between Sequences and Control Tables
Sequences are objects, like fields, that you can create and maintain in any Progress database. Unlike fields, sequences reside in a separate database block independent of application tables. Figure 9–7 shows the basic relationship between database fields and sequences.
Figure 9–7: Database Fields and Sequences Comparison
![]()
Each sequence has a name and a value, similar to a field. But unlike a field, a sequence also has different schema attributes that determine how the sequence value can change. These include:
Progress provides a dedicated set of 4GL functions and statements to access and increment sequences strictly according to the defined attributes.
Sequences vs. Control Tables
Before Progress Version 7, the only way Progress could generate sequential values was to maintain integer fields in a table record created specifically for this purpose. This is often done in a control table, separate from all other application tables.
For example, you might have a control table named syscontrol that contains the field last–cus–num. This field holds the value of the Cust–num field for the last Customer record. The following code fragment generates a new Customer record with a unique customer number:
Note that access to the syscontrol table must be made within a small transaction to avoid lock contention problems.
Sequences provide a built-in means for generating incremental values, but they are not suitable for all applications. In certain situations (described in the following sections), you might need to use a control table, instead.
Table 9–7 compares sequences and control tables.
Performance vs. Capabilities
In general, sequences provide a much faster and more automated mechanism to generate sequential values than control tables. Sequences are faster because their values are all stored together in a single, table-independent, database block, and they do not participate in transactions. As a result, the principal limitations of sequences for certain applications include:
- Because of transaction independence, sequences do not roll back when transactions are undone, but maintain their last value for all UNDOs. However, fields defined in control tables do roll back to their pretransaction values, as long as all updates to control tables respect transaction boundaries.
- You cannot define more than 100 sequence objects at a time in a database; whereas the number of fields in a control table are limited only by the table size.
Transaction Independence
Transaction independence guarantees that each subsequent sequence value is incremented (positively or negatively) beyond its previous value, but does not guarantee the extent of the increment. In other words, with sequences you can have incremental gaps in the sequential values actually used in your application. These gaps result when sequences are incremented during a transaction that is subsequently undone, leaving the sequences set to their latest values. If the transaction restarts, it uses these latest values, not those generated for the previously undone transaction. Figure 9–8 shows how two overlapping transactions with rollback can create records and commit consecutive sequence values with gaps.
Figure 9–8: Sequences and Overlapping Transactions
![]()
Both transactions start out with the sequence set to 1, which has already been used in the database. Transaction T1 increments the sequence first, assigns, and then rolls back leaving the sequence value at 2. Then transaction T2 increments, assigns, and commits the sequence value 3 to a database field. Transaction T1 then increments, assigns, and rolls back a second time, leaving the sequence value at 4. Finally, transaction T1 increments, assigns, and commits the sequence value 5 to the same database field in a different record. Thus, the sequence values 2 and 4 are skipped and never used in the database. All subsequent transactions increment and commit sequence values greater than 5.
NOTE: Transaction independence is provided for standard sequence increment/decrement operations, but not for operations that set the sequence value directly. Setting the value directly is a special operation intended only for maintenance purposes. For more information, see the "Using the CURRENT–VALUE Statement" section.Control tables, however, obey the same transaction rules as any other table, and can ensure that their field values are reset for undone transactions. If it is essential to generate sequence values without incremental gaps, you must use a control table rather than a sequence.
Storage Limits
If you need to maintain more than 100 sequence objects at a time, you must either add another database to define more sequences or use a control table to generate incremental values in your application. The number of incremental fields you can maintain in a control table is limited only by the number of integer fields you can store in the table.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |