This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money.

Preface:

For Mysql transactions, we all know that transactions have four properties of ACID, respectively:

  • Atomicity,
  • Consistency,
  • Isolation,
  • Persistence.

Based on the above four features, we can sum up the transaction multiple operations either succeed together or fail together. After the transaction is committed, the drop disk is stored. Next, we will look at how InnoDB implements transactions.

For Innodb, it mainly consists of two transaction log files redoLog and undoLog to ensure atomicity, consistency and persistence of transactions. Isolation is controlled by locks such as gap locks, exclusive locks;

Redo Log:

What is?

Redolog is a physical log file used in InnoDB to record transaction commits. It records the physical changes of the data page, not the changes of a row or rows. It is mainly used to restore the physical data page after the commit (restore the data page, and only to the last commit location).

What does RedoLog do after the transaction is committed?

I/O occurs every time InnoDB writes data. I/O occurs every time InnoDB changes data, which is definitely not allowed in terms of performance. So we introduced the concept of transaction log cache, which is called redolog_buffer. Instead, the data in the buffer is written to the log file only after an event is triggered. That is, a write operation inside the InnoDB engine actually happens like this:

Write operation –>redoLog operation –> Write redoLog buffer –> write redoLog file –> local drop disk

The RedoLog RedoLog is used to implement persistence in Mysql transactions. It consists of two parts:

  • Redo log Buffer
  • Redo log File (disk)

It can be seen from the above that redoBuffer is stored in memory, which means that redoBuffer must be high performance, but it is also easy to lose memory files in case of machine failure. While redoFile exists on disk, IO is generated when writing to disk, which means its storage performance is poor, but data is persistent;

A:

Conceptually, InnoDB implements transaction persistence through the force log at commit mechanism, that is, before a transaction commits, all transaction logs must be written to the redo log file and undo log file on disk for persistence. To ensure that each log is written to the transaction log file, the operating system’s fsync operation (the fsync() system call) is called each time the log from the log buffer is written to the log file. The purpose of the fsync() call is to flush the logs from buffer to the log file on disk.

Fsync is an operation that users can interfere with because fsync is performed every time a committed transaction is committed, which affects database performance. Innodb_flush_log_at_trx_commit controls the flushing of redo logs to disk.

  • innodb_flush_log_at_trx_commit=1(the default)

When set to 1, each transaction commit writes the log buffer to the OS buffer and calls fsync() to flush the log file. This way, no data is lost even if the system crashes, but IO performance is poor because every commit is written to disk.

  • innodb_flush_log_at_trx_commit=0

When set to 0, instead of writing logs from the log buffer to the OS buffer when a transaction commits, the OS buffer is written every second and fsync() is called to the log file. This means that 0 is written to disk (approximately) every second, and when the system crashes, 1 second of data is lost.

  • innodb_flush_log_at_trx_commit=2

When set to 2, each commit is only written to the OS buffer, followed by a call to fsync() every second to write the logs from the OS buffer to the log file.

Undo Log (rollback Log, provide rollback operation)

What is?

UndoLog is InnoDB’s rollback log, which is used to provide the rollback capability of transactions, and multi-row versioning (MVCC). UndoLog is a logical log, which is recorded on a per-row basis.

Both are used to provide data recovery logs compared to RedoLog. The difference is that redoLog is a physical log, while undoLog is a logical log. For example, when we perform an update, undoLog records the reverse of the update, whereas redoLog only records the changed data value. And undoLog itself needs persistence support, so undoLog also produces redoLog.

A:

UndoLog mainly provides the following two functions:

  • Implement transaction rollback
  • Implement MVCC

Undolog’s drop flow can be seen in the first picture above

Initial data line:Figure 1-5 shows the corresponding data. The next three implied fields are the transaction number and rollback pointer to the row, respectively;

Transaction 1 changes the values of each field in the row:

  1. Lock the row with an exclusive lock
  2. Record the redo log
  3. Write the value of the line before modification to undo log
  4. Change the value of the current row and fill in the transaction number so that the rollback pointer points to the previous row in the undo log.

Transaction 2 changes the value of this line: The same process as transaction 1 is executed, but there are two lines in undoLog and they are joined together by a rollback pointer

When rollback is performed, the logical records in the Undo log can be read and rolled back. Sometimes, row versioning is also implemented through undo log: When a row is locked by another transaction, it can analyze the previous data of the row from the Undo log, so as to provide the version information of the row, so that users can achieve non-locked consistent read.

In InnoDB there are concepts like pages, extents, segments, and table Spaces, and segments are divided into these

  • Data segment: Stores data
  • Index segment: Stores indexes
  • Rollback segment: Stores rollback logs

Innodb uses rollback segments to manage undoLog

MVCC

MVCC is typically implemented by saving a snapshot of the data at a point in time. This means that a transaction can see a consistent view of the data within the same transaction no matter how long it runs. Depending on when the transaction starts, it also means that different transactions may see different data in the same table at the same time.

  • Each row of data has a version that is updated each time the data is updated.
  • When modifying, Copy out the current version and modify at will. There is no interference between transactions.
  • Compare the version number when saving, and overwrite the original record if it succeeds (COMMIT). Aborting copy (rollback)

Ok! The content of this period ends here, do you learn waste, I hope you can help, there is wrong place I hope you can put forward, grow up together;

Neat makes for great code, and there’s only so much detail