Transaction Log in SQL Server (2) -- Role of transaction log in data modification

This articleArticleThis is the second article in the series, in case you have not read the first article. The address of the previous article is as follows:

Transaction logs in SQL Server (I)-physical and logical architecture of transaction logs

 

Introduction

Each SQL Server database records the corresponding logs to the log files according to the order of the modified data (insert, update, delete. SQL Server uses the write-Ahead Logging Technology to ensure the atomicity and durability of transaction logs. this technology not only guarantees atomicity (A) and durability (d) in acid, but also greatly reduces Io operations, submit the data modification to the disk to lazy-writer and checkpoint. this article describes how SQL Server modifies data and related technologies.

 

Write-Ahead Logging (WAL ))

SQL Server uses Wal to ensure the atomicity and durability of transactions. In fact, not only SQL Server, but also mainstream relational databases including Oracle, MySQL, and DB2 use Wal technology.

The core idea of WAL is to write data to the log before writing data to the database.

Because every modification to the data is recorded in the log, it does not make much sense to write the modification to the disk in real time, even if the SQL server crashes unexpectedly, during the recovery (recovery) process, the data that should not be written to the disk will be rolled back ), the data that should be written to the disk but not written will be redone ). This ensures durability)

However, wal not only guarantees atomicity and durability. It also improves the performance.

The hard disk reads data through rotation. With Wal technology, transactions that modify data each time are not immediately reflected in the database, but recorded in logs. submit the data in the subsequent checkpoint and lazy writer. If there is no Wal technology, you need to write the data to the database each time you submit the data:

However, using Wal to merge write operations will greatly reduce disk I/O:

You may have doubts that the modified data will still be written into log files every time, which also consumes disk I/O. As mentioned in the previous article, each log write record is written in a sequential order, and the lsn with a given sequential number is written. The log is only written to the logical end of the log file. Unlike data, it may be written to various locations on the disk. Therefore, the overhead for writing logs is much lower than that for writing data.

SQL Server data modification procedure

SQL Server performs data modification in the following steps:

1. Write the "begin Tran" record in the SQL Server Buffer log

2. Write the information to be modified on the log page of the SQL Server Buffer.

3. Write the data to be modified in the SQL Server buffer to the data page

4. Write the "commit" record in the SQL Server Buffer log

5. Write the logs in the buffer zone to the log file.

6. Send the confirmation message (ACK) to the client (SMSs, ODBC, etc)

As you can see, transaction logs are not written to the disk step by step. instead, logs are written to the disk at one time after the buffer is written. in this way, logs can be written to the disk to reduce Io and ensure the log lsn order.

The above steps show that even if the transaction has reached the commit stage, it only writes the log page of the buffer into the log and does not write the data into the database. when will the data page to be modified be written to the database?

 

Lazy writer and checkpoint

As mentioned above, the SQL server data modification step does not include the process of actually writing data to the disk. In fact, writing pages in the buffer to the disk is achieved through one of the two processes:

The two processes are:

1. Checkpoint

2. lazy writer

Any page modified in the buffer zone will be marked as "dirty. Writing this dirty page to the data disk is a task of checkpoint or lazy writer.

When a transaction encounters a commit, it only writes all the log pages in the buffer zone to the log files on the disk:

 

When lazy writer or checkpoint is used, the data pages in the buffer zone are actually written to the disk file:

As mentioned above, the LSN in the log file can be compared. If lsn2> lsn1, it indicates that the occurrence time of lsn2 is later than the occurrence time of lsn1. Checkpoint or lazy writer compares the lsn at the end of the log file with the lsn of the data file in the buffer zone, only data with a buffer lsn less than the lsn at the end of the log file will be written to the database on the disk. Therefore, WAL is ensured (logs are written before data is written to the database ).

 

Lazy writer and checkpoint

Lazy writer and checkpoint are often confusing. Because lazy writer and checkpoint both write the dirty pages in the buffer to the disk file. But this is just the only point they share.

Lazy writer aims to manage the buffer zone. When the buffer zone reaches a critical value, lazy writer saves dirty pages in the buffer zone to disk files, releases unmodified pages, and recycles resources.

The significance of checkpoint is to reduce the server recovery time ). A checkpoint is an archive point as indicated by its name. checkpoint occurs periodically. to write the dirty pages in the buffer zone to the disk. Unlike lazy writer, checkpoint is not interested in SQL Server Memory Management. So the checkpoint means that all the modifications made before this point have been saved to the disk. note that the checkpoint will write dirty pages in all the buffers to the disk, regardless of whether the data in the dirty pages has been commit. This means that the "dirty pages" written to the disk may be rolled back later ). but don't worry. If data is rolled back, SQL Server will modify the pages in the buffer and write them to the disk.

The checkpoint operation mechanism shows that the length of the checkpoint interval may affect the performance. The interval of this checkpoint is a server-level parameter. You can configure through sp_config or in SSMs:

The default parameter of recovery interval is 0, which means that SQL server manages the reply interval. You need to define the recovery interval based on the actual situation. Shorter recovery intervals mean shorter recovery times and more disk Io, while longer recovery intervals mean less disk Io usage and longer recovery times.

In addition to automatic checkpoints, checkpoints also occur when alter database and the SQL Server server is shut down. Members of SysAdmin and db_backupoperator groups and db_owner can also use the checkpoint command to manually Save the checkpoint:

By specifying the parameters after the checkpoint, SQL Server completes the checkpoint process according to this time. If the time is short, SQL Server will use more resources to complete the checkpoint process first.

Normally, lazy writer writes dirty pages to a disk, which is much more than checkpoint.

 

Summary

This article briefly introduces the concept of Wal and the role of log when modifying database objects. We also introduced checkpoint and lazy writer respectively. Understanding these concepts is the basis for understanding the work of SQL Server DBA. The next article will describe the Log Mechanism in simple recovery mode.