A relational database is required to ensure a number of transaction properties, including atomicity, consistency, integrity and durability (known as the ACID properties). To ensure this, Adaptive Server adheres to the following rules; it:
Writes all operations to the transaction log.
Writes log records before data or index pages are modified.
Writes log pages to disk when the transaction’s commit is issued.
Notifies the client application of the successful commit only after Adaptive Server has received notification of a successful write to disk from the underlying operating system and I/O subsystem.
set delayed_commit is a performance option suitable only for certain applications. It increases Adaptive Server performance for data manipulation language (DML) operation, (for example, insert, update, delete), but increases the risk of losing your data during a system failure. Performance gains depend on the application in use.
The types of applications that benefit from set delayed_commit typically include short transactions that are sent rapidly and serially to Adaptive Server. For example, a batch application that issues many insert statements, with each insert being a separate transaction.
Use the set command to enable delayed_commit for a session, or with sp_dboption for the database.
After you enable set delayed_commit, the client application is notified of a successful commit before the corresponding log records are written to disk. This improves performance because all but the last log page are written to disk, alleviating contention on the last and active log page.
Before you enable set delayed_commit, consider:
Issuing shudown with nowait can cause data durability issues unless you issue a checkpoint that finishes before the server shuts down.
Enabling set delayed_commit for a session affects only that session. All other sessions’ transactions have all their properties enforced, including their ACID properties. This also means other sessions’ physical logs write the last log page and the log records corresponding to a session with set delayed_commit enabled.
set delayed_commit is redundant on temporary databases and does not provide a performance improvement.
Use set delayed_commit only after careful consideration of both your application and operational requirements and your environment. While the risk to data durability may be very low, the options for recovery may be time-consuming if your database is large and your tolerance for missing data is low.
These are the changes to logging behavior when delayed_commit is enabled.
When a session implicitly or explicitly commits a transaction:
The user log cache (ULC) is flushed to the transaction log in memory.
The task issues writes on all non-written log pages except the last (which contains the commit).
The task notifies the client application of a successful commit without waiting for the I/O to complete.
This transaction’s “last log page” is
written:
By another transaction when it is no longer the “last log page.”
By another, non-delayed transaction when it completes.
By a checkpoint or the housekeeper buffer wash mechanism.
By implicit checkpoints causes (for example, shutdown, dump database, dump tran, sp_dboption truncate log on checkpoint).
The task is ready to continue with the next transaction.
When set delayed_commit is enabled, Adaptive Server notifies the client application before the actual physical disk write completes. Because of this, the application perceives that the transaction is complete whether or not the physical disk write is successful. In the event of a system failure (disk errors, system crash, and so on), transactions that were not written to disk (transactions whose commit records were on the last log page) are not present after recovery in spite of the application being notified they were committed.
Systems that require tight system interdependencies, such as through a messaging system using Real Time Data Services (RTDS), further complicate the consequences of using set delayed_commit.
There are two situations where applications can manage the risk:
The application maintains its own trace or log, and, after a system failure, ensures that the database state corresponds to its own trace or log.
You can restore the database to the state it was in before the application was run. This assumes you took a complete database backup before a batch-job type application is run. In case of failure, the database backup is loaded and the batch job is restarted
You can enable set delayed_commit for a database or for a session, with the session setting overruling the database setting. This means that a session that enables the option has delayed_commit enabled regardless of the database setting.
