When a transaction is committed, the server normally writes the data to a transaction log file. The server might wait until it is less busy, or until multiple changes have accumulated, before writing the data to the database file.
If the server shuts down abnormally (for example, due to a power failure), changes since the last write to the database file are in the transaction log file. The server can read those changes from the log file and then use that information to update the database file.
Depending on the application requirements, you can balance safety and performance by controlling when data is written to the transaction log (transaction durability).
Note Transaction durability is not part of the ANSI standard for SQL-99.
There are two main transaction durability levels:
▪ Strict durability: Historically, the goal of most database servers is to maximize safety, that is, to make sure that data is not lost due to a power failure or other problems. For example, this approach is appropriate for accounting data, where it is unacceptable to lose track of even a single transaction.
In these cases, it is important that the data is written to the transaction log immediately when a transaction is committed, and that the acknowledgment that the transaction is committed is only sent to the application after the data is in the transaction log.
This form of transaction handling is called strict durability. If durability is strict, it is almost impossible to lose committed data unless the hard disk drive itself fails.
▪ Relaxed durability: Some database servers have been designed to maximize performance, with less regard for safety. Choosing performance over safety is acceptable in situations where it is not essential that you capture every transaction. For example, you might have an application that collects statistical data about computer performance. If you were to lose a few of the performance records, your system would still function and the conclusions drawn from analyzing the data would be unlikely to change.
To optimize performance, it might be appropriate for the server to send the transaction acknowledgment to the application immediately but wait until there are several transactions before writing the data to the transaction log.
This form of transaction handling is called relaxed durability. If durability is relaxed, the server might lose a few committed transactions if there is a power failure before the data is written to the transaction log.