This option controls how quickly a transaction commit is secured on disk. It greatly affects the performance of the database server. For example, if a single user commits two transactions in quick sequence the database server may use a single I/O to secure both transactions when delayed commit is on.
Transactions are never reordered by using the delayed commit option. I.e. it is not possible for a later transaction to be secured on disk before an earlier one. The database is thus always returned to a consistent state after a machine crash. However, if a transaction has been committed but not yet written to disk it will be lost if the database server or machine goes down in an uncontrolled fashion.
Transactions that use the XA transaction protocol are automatically committed with delay commit disabled.
Delayed commit option can be set to one of the following:
| Delayed Commit Option | Description |
|---|---|
| Default off | in this mode delayed commit is not used unless a transaction is set to use delayed commit by the application. This is the default. |
| Default on | in this mode all transactions where the delay mode has not been explicitly set are delayed. The transaction will be secured within the timeout period specified. If other transactions are committed before the timeout occurs the transactions may be combined into a single I/O to boost performance. |
| Disabled | in this mode all transactions are secured to disk immediately and the application will not regain control after a commit until the transaction has been secured. This option overrides any application settings for delay commit. |
| Timeout (ms) | this specifies the number of milliseconds to wait before the transaction is written to disk. If a value of zero is specified transactions are not flushed until the server determines that the commit set page is full. In general, this is not recommended as transactions are likely to be lost if there is an uncontrolled machine stop. Default is 100 milliseconds. |