12.14 Specifying the WITHOUT ROLLBACK option

When a table is updated (including addition and deletion processing) while the WITHOUT ROLLBACK option is in effect, the updated rows are released immediately from locked status, so that the rows become no longer subject to rollback.

Organization of this section
(1) Effects of specifying the WITHOUT ROLLBACK option
(2) Criteria
(3) Notes

(1) Effects of specifying the WITHOUT ROLLBACK option

The effects of specifying the WITHOUT ROLLBACK option for a table are discussed as follows.

Improved performance
There are fewer occurrences of locked status because lock control is released upon completion of update processing.

(2) Criteria

This option is suitable for a table that is subject to concentrated update processing, such as when numbering is performed.

A numbering application, such as one that handles form numbers or document numbers, may manage a table by assigning numbers and incrementing the assigned numbers. If processing is concentrated, such an application may be placed frequently in lock-release wait status because the lock status cannot be released until COMMIT is issued. If the WITHOUT ROLLBACK option is specified for the table in this case, the lock status is released when increment processing is completed, thereby reducing occurrences of lock-release wait status. The following figure shows an example of a numbering application.

Figure 12-23 Example of a numbering application

[Figure]

This example manages one type of numbers with one row.

Following is an example of defining the numbering management table shown in Figure 12-23:

CREATE TABLE numbering-management-table (type NCHAR(4),
                        numbering INT)
                   :
            WITHOUT ROLLBACK

Because a missing number may occur for the following reason, this option should be used only with applications that can handle missing numbers:

(3) Notes