Before we say how to implement ACID, we should understand ACID. In this case, AID is easy to understand, but consistency has never been understood.

Consistency is a transaction that changes the database from one state to the next consistent state. Database integrity constraints are not broken (the system moves from one correct state to another correct state) before and after a transaction.

MySQL Tech Insider: InnoDB Storage Engine But I don’t understand what a consistent (correct) state is and what a database integrity constraint does not break. I saw the answer on Zhihu and felt clear.

Among the examples mentioned:

If A has 90 yuan, transfer 100 yuan to B, but the balance constraint is not less than 0, so the transfer fails. If the transfer is successful, then the database constraint is broken and there is no consistency.

If there is no database constraint above, the balance is not allowed to be less than 0 in the business. After the payment is finished, check the account of A and find that the balance is less than 0, perform transaction rollback. This breaks business constraints without breaking database constraints, but ensures business consistency through transaction rollback.

If there are no constraints on the database and no constraints on the business, then the payment will be successful. But any constraint is broken, then even if unreasonable, consistency is preserved that day.

Transactions can guarantee the process of C through AID, where C is the end and AID is the means.

The retelling is not good, or the original is clear.


Getting to the point, how is ACID implemented for transactions

atomic

The key to achieving atomicity is the ability to undo all SQL statements that have been successfully executed when the current transaction is rolled back. InnoDB implements rollback using undo logs. When a transaction changes data, InnoDB generates the corresponding Undo log. If a transaction fails or a rollback is called, and the transaction needs to be rolled back, you can use the information in the undo log to rollback the data to where it was before the modification.

Undo logs are logical logs that record information about SQL execution. When a rollback occurs, InnoDB will do the reverse based on the undo log contents. Insert-delete, delete-insert, update-update.

persistence

Data is stored in disks, but disk I/OS are required for reading and writing data, which is inefficient. Therefore, InnoDB provides a Buffer Pool that contains a map of some of the data pages on disk and serves as a Buffer to access the database. When data is read from the database, it is read from the Buffer Pool first; if not, it is read from disk and put into the Buffer Pool. When data is written to the database, the Buffer Pool is first written, and the modified data in the Buffer Pool is periodically flushed to disks (this process is called flushing).

The use of Buffer Pool greatly improves the efficiency of reading and writing data, but it also brings a new problem: if MySQL crashes and the modified data in the Buffer Pool is not flushed to disk, the data will be lost and the persistence of the transaction cannot be guaranteed.

The redo log was introduced to solve this problem. When data is modified, the redo log logs data in the Buffer Pool. When a transaction commits, the fsync interface is called to flush the redo log. If the MySQL database is down, you can read the redo log data during the restart to restore the database. Redo log uses write-ahead logging (WAL). All changes are written to the log first and then updated to the Buffer Pool. This ensures that data will not be lost due to MySQL downtime and meets the persistence requirements.

Since redo logs also need to be written to disk at transaction commit time, why is it faster than scrubbing? The main reasons are

  • The redo log is a sequential I/O. The redo log is a sequential I/O.
  • The default MySQL page size is 16KB. A small change on a page will be written to the entire page. The redo log contains only the parts that really need to be written, and the number of invalid IO is greatly reduced.

Isolation,

Isolation pursues complementary interference between transactions in the concurrent case. Through locks and MVCC.

For example, before modifying data, a lock is acquired. Once the lock is acquired, you can modify the data. This part of the data is locked for the duration of the transaction, and other transactions need to modify the data, requiring the transaction to commit or roll back.

consistency

Consistency is the ultimate goal of a transaction. The AID of a transaction is to achieve C, so to speak, C is the end and AID is the means. In addition to database-level guarantees such as AID, consistency also requires application-level guarantees. For example, a transfer operation only deducts the sender’s balance and does not increase the receiver’s balance, so no matter how perfect the database is, it cannot guarantee consistency.

  • How does Zhihu understand the concept of consistency in database transactions?
  • MySQL Tech Insider: InnoDB Storage Engine
  • “2022 School Recruitment Interview Treasure book Java post” — Niuke