This is the sixth day of my participation in Gwen Challenge

“This article has participated in the weekend learning plan, click to see details”

One, foreword

Scenario: The storage engine completes an update statement.

InnoDB’s important memory structure: Buffer Pool

This will cache a lot of data, so that later when the query, if there is data in the memory buffer pool, do not have to look up the disk.

As shown in figure:





Two, for example:

What happens when you execute the update SQL?

UPDATE user SET name = 'donaldy' WHERE id=10;
Copy the code

Steps:

  1. Buffer pool data operations
  2. undoLog file: Saves old values
  3. redo log buffer: Saves the new value
  4. Background threads periodically flush data from memory to disk files.


Why not just update the data on disk?

Because the execution performance is terrible. While it is technically possible to do a random read/write directly to a disk file and then update the data in the disk file, it results in poor performance in performing the request.

This is why MySQL has designed a complex mechanism to refresh data in memory, write redo logs, and commit transactions. Background threads periodically refresh data to disk files.


1) Buffer pool data manipulation

Summary: Data is read from the disk and stored in memory. After the operation is complete, data is written to the disk

The detailed steps are as follows:

  1. To viewid=10Whether the row is in the buffer pool
  2. If the buffer pool has one, an exclusive lock is placed on the row
  3. If not, it will be loaded directly from disk into the buffer pool
  4. After the operation is complete, write the data to the disk

Question: Why exclusive lock?

Guaranteed atomic operation


2)undoLog file: Saves old values

As shown in figure:

Old value: the value before the update

# original data: id= 10, name = 'gege'# to performSQL
UPDATE user SET name = 'donaldy' WHERE id=10; # so'gege'Is the old valueCopy the code

Why save old values?

The old value is restored to facilitate later rollback.


3)redo log buffer: Saves the new value

What if the system goes down and the data in memory is lost?

Write the changes in memory to a Redo Log Buffer. This is an in-memory Buffer that stores the Redo logs.

Function: Is actually used inMySQLUsed to recover updated data in the event of a sudden outage.

Problem: If the transaction has not been committed,MySQLWhat if it goes down?

There are three cases of this problem:

  • Before the transaction commits
  • Transaction committed
  • Transaction Committed successfully


  1. Before the transaction commits:

If the transaction is not committed, the MySQL database crashes and the Redo logs written to the Redo Log Buffer are lost. If the transaction is not committed, it means that the transaction is not successfully executed. In this case, the data in the memory is lost due to MySQL downtime, but the data on the disk is still unchanged. Therefore, if MySQL is down at this time, there is no problem.

  1. Transaction committed:

Prelog: A transaction is committed and redo logs are flushed from the redo log buffer to a disk file according to a policy.

This policy is configured using innodb_flush_log_at_trx_COMMIT.

  • If the parameter value is 0, theredo log bufferAt this point, the transaction is committed.MySQLWhen the system is down, all data in the memory is lost.
  • When the parameter value is 1, the value ofredo logBrush from memory to disk file, as long as the transaction commits successfully, thenredo logIt has to be on the disk.
  • When the value of the parameter is 2, the transaction is committedredoLogs are written to disk files corresponding toos cacheIn memory cache, no actual access to disk files, in case the machine goes down at this point, thenos cacheIn theredo logWill be lost; That is, the commit transaction succeeds and the data is lost.

As shown in figure:

So depending on the parameter value, it’s going to be different.

  1. Transaction committed successfully:

The innodb_flush_log_at_trx_COMMIT parameter has different results