Preface:

They refer to Atomicity, Consistency, Isolation, Durability.

  • atomic

    • By definition, atomicity means that a transaction is an indivisible unit of work in which all or none of the operations are done. Commit it all or roll it all back.

  • consistency

    • Data and state changes in transactions are consistent, that is, data writing results must fully comply with the preset rules, and state inconsistency will not occur due to system accidents and other reasons.

  • Isolation,

    • The visibility of one transaction’s data to other transactions before it is committed (normally set to invisible)
    • The isolation levels in MySQL are Read Uncommitted, Read committed, Repeatable Read, and Serializable.

  • persistence

    • Transaction persistence means that once a transaction is committed, the data in the database must be kept forever. Even if the server system crashes or server downtime and other failures. As soon as the database is restarted, it must be able to be restored to its state after a successful transaction.

The default MySQL engine is usually InnoDB. Let’s take a look at how InnoDB implements transactions in the engine.

InnoDB Engine Architecture (MySQL5.7)

Before explaining how the InnoDB engine implements ACID, let’s take a look at some parts of the InnoDB engine architecture for a better understanding.

  • Buffer Pool

    The Buffer Pool contains the mapping of some of the data pages in the disk. When data is read from the database, the data is read from the Buffer Pool first. If there is no Buffer Pool, the data is read from the disk and added to the Buffer Pool. When data is written to the database, it is first written to the Buffer Pool. The updated data in the Buffer Pool is periodically flushed to disks (this process is called flushing).

  • Log Buffer

    When changes are made to an InnoDB table in MySQL, the changes are first stored in memory in the InnoDB log buffer and then written to an InnoDB log file commonly known as redo logs.

  • DoubleWrite Buffer

    Doublewrite Buffer is a 2MB Buffer that is open to physical files in a shared (system) tablespace. It’s a 2MB space split in two.

    At the beginning of the dirty operation, the dirty page ** ‘backup’ operation is performed first. Writes dirty page data to Doublewrite Buffer.

    Write the Doublewrite Buffer(sequential IO) to a disk file (shared tablespace) for flushing.

  • Rollback Log (Undo Log)

    Undo Log records logical logs. It records the changing version and condition of each piece of data during the transaction.

    The default Undo Log in Innodb disk architecture is to open the Buffer of physical files in the shared (system) tablespace.

    In the event of transaction interruption or Rollback,Innodb uses Undo Log to Rollback data to the transaction start state.

  • Redo Log

    A Redo Log is a physical Log that records physical changes to a data page. Row records are not recorded. (That is, only the changes to be made are recorded, not the completion of the changes)

    When the database is restarted after a downtime, the redo logs are restored to the database.

atomic

Innodb guarantees atomicity of transactions, including transaction commit mechanism and transaction rollback mechanism.

  • Submit mechanism

When a DML statement is executed, the operations shown in the figure above are performed

  1. The modified data is stored in the Buffer Pool to be flushed.
  2. Write a Redo Log to the Log Buffer and set the Log state to Prepare.
  3. Return to the MySQL service layer and record the BinLog.
  4. Set the status of the Log files in the Log Buffer to Commit and wait for the Log files to be saved to disk.

The above operations are not serial, but concurrently processed by background processes

The above transaction commit process is called the two-phase commit of XA

  • Rollback mechanism

The rollback mechanism of transactions in Innodb engine relies on the rollback Log (Undo Log) to roll back data.

In the event of transaction interruption or Rollback,Innodb uses Undo Log to Rollback data to the transaction start state.

Isolation,

Isolation relies on two main techniques

  • LBCC (Lock Based Concurrency Control)

Before a transaction begins to manipulate data, it is locked to prevent other transactions from modifying the data. For SQL operations, use current reads to solve the isolation problem of concurrent reads and writes. (Current read: The data read is the latest data exclusive to the thread.)

  • MVCC (Multi Version Concurrency Control)

Before a transaction starts to operate on data, it backs up the data with a Snapshot at the current point in time and provides this Snapshot for other transactions to read consistently.

Manage multiple versions of the data being processed within a transaction when accessing (read or write) a database concurrently. To prevent write operations from blocking concurrent read operations, use snapshot read to solve the isolation problem of concurrent read and write operations.

Multi-version information management is implemented based on undo logs.

persistence

The minimum unit of database IO is a page size of 16KB

The minimum unit of OS IO is a page size of 4KB

Therefore, four disk I/O operations are required for a dirty page in the database. If the power is cut off suddenly during operation, the page will break.

There are three possible scenarios for the transaction-based commit mechanism process.

  • The Redo Log was submitted. Data is successfully disked. persistence is guaranteed

  • Page breakage caused by system accidents in the process of data brushing dirty (some pages are brushed successfully) In view of page breakage, the Double write mechanism is adopted to ensure the recovery of page breakage data.

  • There was no page fracture in the data and no successful brushing

    MySQL persists data using the Redo Log

Double Write mechanism explained in detail

Doublewrite Buffer is a 2MB Buffer that is open to physical files in a shared (system) tablespace.

At the beginning of the dirty operation, the dirty page ** ‘backup’ operation is performed first. Writes dirty page data to Doublewrite Buffer.

Write the Doublewrite Buffer(sequential IO) to a disk file (shared tablespace) for flushing.

The idea behind the Double Write mechanism is that a copy of the dirty page data is created before the dirty page is flushed. Page breaks caused by unexpected system outages can be recovered using a dirty page data copy (DoubleWrite Buffer).

consistency

At the database level, databases ensure consistency through atomicity, isolation and persistence.

In other words, among the four properties of ACID, consistency is the goal, atomicity, isolation and persistence are the means, which are provided by the database to ensure consistency.

The database must implement AID to achieve consistency.

For example, atomicity is not guaranteed, and obviously consistency is not guaranteed either.

However, if you deliberately write code in a transaction that violates the established rules, consistency is not guaranteed. (For example, money is deducted from this account but not added to other accounts)

The last

Thank you for reading here, the article is inadequate, welcome to point out; If you think it’s good, give me a thumbs up.

Also welcome to pay attention to my public number: Java programmers gathering place, Maidong will share Java related technical articles or industry information every day, welcome to pay attention to and forward the article!