The all-or-nothing commitment that a DBMS makes for the statements in a transaction seems almost like magic to a new SQL user. How can the DBMS possibly back out the changes made to a database, especially if a system failure occurs during the middle of a transaction? The actual techniques used by brands of DBMS vary, but almost all of them are based on a transaction log, as shown in Figure 12-5.
Here is how the transaction log works, in simplified, conceptual form. When a user executes a SQL statement that modifies the database, the DBMS automatically writes a record in the transaction log showing two copies of each row affected by the statement. One copy shows the row before the change, and the other copy shows the row after the change. Only after the log is written does the DBMS actually modify the row on the disk. If the user subsequently executes a COMMIT statement, the end-of-transaction is noted in the transaction log. If the user executes a ROLLBACK statement, the DBMS examines the log to find the “before” images of the rows that have been modified since the transaction began. Using these images, the DBMS restores the rows to their earlier state, effectively backing out all changes to the database that were made during the transaction.
If a system failure occurs, the system operator typically recovers the database by running a special recovery utility supplied with the DBMS. The recovery utility examines the end of the transaction log, looking for transactions that were not committed before the failure. The utility rolls back each of these incomplete transactions, so that only committed transactions are reflected in the database; transactions in process at the time of the failure have been rolled back.
The use of a transaction log obviously imposes an overhead on updates to the database. In practice, the mainstream commercial DBMS products use much more sophisticated logging techniques than the simple scheme described here to minimize this overhead. In addition, the transaction log is usually stored on a fast disk drive, different from the one that stores the database, to minimize disk access contention.
Some personal computer DBMS brands allow you to disable transaction logging to increase the performance of the DBMS.
Specialized databases, such as in-memory databases or cached database copies, may also use this log-free architecture. This may also be an acceptable alternative in specialized production databases, for example, where the database contents are replicated on a duplicate computer system. In most common production databases, however, a logging scheme and its overhead are an integral part of the database operation.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.