MySQL practice 45 lectures

The update process also involves two important logging modules, the Redo Log (redo log) and Binlog (archive log).

redo log

InnoDB’s redo log is a fixed size, such as a set of 4 files with each file size of 1GB, so the “powder board” can log 4GB of operations in total. You start at the beginning, and when you get to the end, you loop back to the beginning, as shown in the figure below.

The difference between a redo log and a binlog

  1. The redo log is specific to the InnoDB engine; Binlog is implemented by MySQL’s Server layer and is available to all engines.
  2. A redo log is a physical log of what changes have been made to a data page. The binlog is the logical log that records the original logic of the statement, such as “Add 1 to the C field of the line ID=2”.
  3. The redo log is written in a loop, so it always runs out of space; Binlog can append writes. The “append write” is when the binlog file is written to a certain size and then switches to the next one, without overwriting the previous log.

The UPDATE statement executes the flow

The redo log is used to guarantee the Crash-Safe capability. When innodb_flush_log_at_trx_commit is set to 1, the redo log of each transaction is persisted directly to disk. I suggest that you set this parameter to 1, so as to ensure that MySQL does not lose data after abnormal restart. When the SYNC_BINLOG parameter is set to 1, it means that the binlog of each transaction is persisted to disk. I also recommend that you set this parameter to 1. This will ensure that the binlog is not lost after an abnormal MySQL restart.

The Redo log does not record the “updated state” of a data page, but “what changes have been made” to the page.

There are two modes for Binlog. Statement format records SQL statements, and ROW format records the contents of rows, both before and after the update.

Transaction isolation: why can’t I see your change?

  1. ACID (Atomicity, Consistency, Isolation, Durability)
  2. Dirty read, non-repeatable read, and phantom read problems can occur when multiple transactions are executed simultaneously on the database.
  3. The transaction isolation levels of the SQL standard include read uncommitted, read committed, repeatable read, and serializable. The tighter the isolation, the less efficient it will be.
  • Read-uncommitted means that changes made by a transaction can be seen by other transactions before it is committed.
  • Read commit means that after a transaction commits, the changes it makes are not visible to other transactions.
  • Repeatable reads mean that the data seen during the execution of a transaction is always the same as the data seen when the transaction was started. Of course, under the repeatable read isolation level, uncommitted changes are also invisible to other transactions.
  • Serialization, as the name implies, means that for the same row, “write” will apply a “write lock”, “read” will apply a “read lock”. In the event of a read-write lock conflict, the later-accessed transaction must wait for the previous transaction to complete before proceeding.
  1. Implementation of isolation level:
  • A view is created in the database and is accessed based on the logical results of the view. Under the “repeatable read” isolation level, this view is created when the transaction starts and is used for the entire life of the transaction. Under the “read commit” isolation level, this view is created at the beginning of each SQL statement execution. Note that under the “read uncommitted” isolation level, the most recent value on the record is returned directly, with no view concept; Under the “serialized” isolation level, parallel access is avoided by locking directly.
  • Each record is updated with a rollback operation. Multiple versions of the same record can exist in the system, which is called multi-version concurrency control (MVCC) of the database.

  1. Transaction startup mode

    1. Explicitly start transaction statements, begin or start transaction, commit commit, rollback
    2. Set autocommit=0. This will turn off auto-commit for this thread. As soon as a SELECT statement is executed, the transaction starts and does not commit automatically until a COMMIT or ROLLBACK is actively executed or the connection is broken.
  2. The first option is to use the Commit Work and Chain syntax if you are considering an additional interaction. The transaction is explicitly started with BEGIN when AUTOCOMMIT =1, and committed if COMMIT is performed. If you commit Work and Chain, the transaction is committed and the next transaction is automatically started.