First, four characteristics of transaction

MySQL transactions refer to transactions in the InnoDB engine, MyISAM engine does not support transactions. I believe you all know the four major features of transactions: Atomicity, Consistency, Iosolation and Durability. So what is the purpose of using transactions?

  • Reliability: Exceptions may be thrown during data modification or data crash is required to ensure data consistency before and after modification. In this case, you need to know the data before and after modification, including the undo log and redo log.
  • Concurrent processing: When multiple concurrent requests are sent, only changes to the data are needed. In order to avoid reading dirty data, read and write between transactions need to be isolated.

Here are the techniques for implementing transactions, log files (undo and redo logs), locks, and MVCC, and how the four features of transactions are implemented.

Undo log and redo log files

1.undo log

Undo log – Rollback log, used to record the information before data modification. In order to roll back to the pre-modification state in the event of an error, the pre-modification data is recorded.

start transaction; Update bank set balance= balance-600 where name="kmli"; Update finance set amount= amount + 600 where name="kmli"; update finance set amount= amount + 600 where name="kmli"; commit;Copy the code

Every time data is written or modified, the information before the modification is recorded in the Undo log.

Summary: Undo log ensures atomicity of uncommitted transactions by rolling data back and forth.

2.redo log

Redo log – A redo log that records information about modified data. The log file consists of two parts: the redo log buffer, which is in memory, and the redo log, which is on disk. The redo log prevents the loss of unsynchronized modified data during system downtime or power failure.

start transaction; Update bank set balance= balance-400 where name="kmli"; // Generate redo log balance=600 (original data balance=1000) update bank set balance= balance-400 where name="kmli"; Update finance set amount= amount + 400 where name="kmli"; update finance set amount= amount + 400 where name="kmli"; commit;Copy the code

To improve performance, MySQL does not synchronize every change to disk in real time. Instead, it stores the change to a Buffer Pool and uses it as a cache. Background threads are then used to synchronize between the buffer pool and the disk.

What if the system that hasn’t had time to synchronize goes down or power goes out? There is no time to perform the operation shown in red in the figure above. This will cause the modification information of some committed transactions to be lost! So the redo log is introduced to record changes to successfully committed transactions, and the redo log is persisted to disk, and the redo log is read after the system restarts to recover the latest data.

Summary: Redo logs are used to recover data and ensure the persistence of committed transactions.

MySQL lock and MVCC

1. The MySQL lock

When dealing with concurrent read or write operations, the problem is solved by implementing a lock system consisting of two types of locks: shared locks (read locks) and exclusive locks (write locks).

  • Shared lock: Also called read lock, multiple read requests can share a lock without blocking.
  • Exclusive lock: Also known as a write lock, a write lock blocks all other lock requests until the lock is released.

Conclusion: With read/write lock, you can read and parallel, but not write/read. The isolation of write/write parallel transactions is based on the read/write lock!

2.MVCC(Multi-version Concurrency Control)

InnoDB’s MVCC is implemented by storing two hidden columns at the end of each row. These two columns, one holds the creation time of the row and the other holds the expiration time of the row, not the actual time value, but the system version number.

The implementation of MVCC in MySQL relies on undo log (version chain) and Read View (consistency view). READ COMMITTED – Generates a ReadView before each READ; REPEATABLE READ – Generates a ReadView when reading data for the first time.

  • Undo log: The undo log records multiple versions of a row of data.
  • Read View: Used to determine the visibility of the current version of data

Fourth, the implementation of transactions

The rollback logging, redo logging, and locking techniques described earlier are the basis for implementing transactions.

  • Atomicity of transactions is achieved through undo log
  • Transaction persistence is implemented through redo logs
  • Transaction isolation is achieved through the transaction isolation level (read/write lock +MVCC)
  • Consistency is achieved through atomicity, persistence, and isolation!

ACID is just a concept. The ultimate purpose of a transaction is to ensure the reliability and consistency of data.