The role of the SQL Server log

Reliability of the database

In the relational database system, we need a reliable database, the so-called reliable is to meet the following two kinds of situations to ensure the consistency of the database:

Ensure database consistency in case of system crash/failure

Data cannot cause inconsistencies or data corruption when multiple DML statements modify data at the same time

In fact, the second case is the problem of concurrency, the traditional relational database, we use the lock to solve this problem, and for the memory database or with optimistic concurrency control of the database system, through multiple version concurrency control (MVCC) to solve this problem. Because the purpose of this article is to discuss logs rather than concurrency, the second scenario is not explained in detail.

We've mentioned consistency several times above, and before we begin to understand how the logs maintain consistency, we first need to understand what consistency is consistence. Consistency in the database system refers to a wide range of content, consistency requires not only the data in the database to meet a variety of constraints, such as unique constraints, primary key constraints, but also to meet the Database Designer's implicit constraints, simple business constraints such as gender this column allows only men or women, such implicit constraints pass They are often implemented using triggers or constraints, or are constrained in applications serviced by the database.

Here we reduce the scope of consistency to transactional consistency, and the concept of transactional consistency is academically interpreted as:

If there are no system or other transaction errors during the execution of a transaction, and the database is data consistent during the start of the transaction, we believe that the database still guarantees consistency at the end of the transaction.

Therefore, the extension of the transaction must satisfy the atomicity, that is, the transaction does not allow partial execution. Part of the execution of a transaction is equivalent to putting the database in an inconsistent situation. In addition, multiple transaction concurrent execution can also cause database inconsistencies unless the database system controls concurrency.

Explicit constraints on the above are implemented by the database system, such as statements that violate a consistency constraint and cause the database system to complain and refuse to execute. However, some implicit transaction constraints, such as the developer of the write statement, are not aware of the rules designed by the System designer, resulting in data modifications that violate the business rules, which is difficult to detect at the database end. But this kind of problem usually can rule to the domain of permission control, we think that give a user to modify the permission of specific data, think that this user should understand the implicit and explicit rule in the database.

In addition to these business data inconsistencies, we need to ensure consistency of data in situations such as system crashes, which can lead to inconsistencies in such data, including but not limited to the following:

Storage-system corruption, such as a corrupted byte level on a disk, can often be found on disk parity, and there are some larger problems, such as the entire storage system crashing. The fix for such problems depends on upfront work, such as backup strategy, high-availability architecture, SAN replication, and so on.

The overall damage to the engine room, this kind of problem is more extreme, only remote room disaster can be solved.

System failure, the process of modifying the data requires a transaction as a context, and as with other concepts, the transaction is stateful. And the transaction state is usually stored in the easily lost main memory, therefore, when a system failure, a process crash, and so on, can result in the loss of the transaction state, we cannot know which part of the transaction has been executed and which part has not been executed, and the rerun of the transaction does not solve the problem. Because it is possible that a part of a transaction can be repeatedly executed. So the way to solve this problem is to store the state of the transaction and the detailed steps of the database modification with the data in memory separately. and stored in stable media such as disk, when the system failure, and so on, we can through these records to restore the system to a consistent state, we have such storage, called Log.

Logs in SQL Server

Log to maintain consistency in SQL Server (of course, the log works very much, but consistency is the basic function of the log, other features can be seen as additional functionality). Typically, when we create a database, a log file with an extension of LDF is included. The log file is essentially a collection of log records. In SQL Server, we can look at the log's information through DBCC LOGINFO, as shown in Figure 1.

Figure 1. DBCC Loginfo