Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the characteristics of the MySQL transaction log", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the characteristics of the MySQL transaction log.

I. MySQL transaction

Transaction is an important feature that distinguishes MySQL from NoSQL, and is the key technology to ensure the data consistency of relational database. A transaction can be seen as the basic unit of execution of a database operation and may contain one or more SQL statements. When these statements are executed, either all or none of them are executed.

The execution of a transaction mainly includes two operations, commit and rollback.

Commit: commit, which writes the transaction execution results to the database.

Rollback: rollback, rollback all executed statements and return the data before modification.

MySQL transactions contain four features, known as the four kings of ACID.

Atomicity (Atomicity): statements are either executed or not executed at all, which is the core feature of the transaction, and the transaction itself is defined by atomicity; the implementation is mainly based on the undo log log.

Durability: ensures that data will not be lost due to downtime after a transaction is committed; the implementation is mainly based on redo log logs.

Isolation: ensures that transaction execution is not affected by other transactions as much as possible; the default isolation level for InnoDB is that the implementation of RR,RR is mainly based on locking mechanism, hidden columns of data, undo log, and next-key lock-like mechanisms.

Consistency (Consistency): the ultimate goal of transactions. The realization of consistency needs not only the guarantee at the database level, but also the guarantee at the application level.

Atomicity

The atomicity of a transaction is like an atomic operation, which means that the transaction can no longer be divided, and either all or none of the operations are done; if the execution of a SQL statement in the transaction fails, the executed statement must also be rolled back, and the database returns to the state before the transaction. There are only 0 and 1, and no other values.

The atomicity of the transaction indicates that the transaction is a whole. When the transaction cannot be executed successfully, it is necessary to roll back all the statements that have been executed in the transaction to make the database return to the state that the transaction did not start at the beginning.

The atomicity of transactions is achieved through undo log logs. When the transaction needs to be rolled back, the InnoDB engine will call the undo log log to undo the SQL statement to realize the data rollback.

Persistence

The persistence of a transaction means that when a transaction is committed, the change to the database should be permanent, not temporary. That is to say, after the transaction is committed, any other operation or even the downtime of the system will not affect the execution result of the original transaction.

Transaction persistence is achieved through redo log logs in the InnoDB storage engine, as shown below.

Isolation

Atomicity and persistence are the properties of a single transaction itself, while isolation refers to the relationship that should be maintained between transactions. Isolation requires that the influence of different transactions is not interfering with each other, and the operation of one transaction is isolated from other transactions.

Because a transaction may contain more than one SQL statement, it is likely that other transactions will begin to execute during the execution of the transaction. Therefore, the concurrency of multi-transactions requires that the operations between transactions are isolated from each other. This is similar to the concept of data synchronization between multiple threads.

Locking mechanism

The isolation between transactions is achieved through the locking mechanism. When a transaction needs to modify a row of data in the database, it needs to lock the data first; for the locked data, other transactions do not run the operation and can only wait for the current transaction to commit or roll back to release the lock.

Locking mechanism is not an unfamiliar concept, and different implementations of locks are used to protect and synchronize data in many scenarios. In MySQL, locks can be divided into different types according to different classification criteria.

Divided by granularity: row lock, table lock, page lock

Divided by way of use: shared lock, exclusive lock

Divided according to thought: pessimistic lock, optimistic lock

There are many knowledge points about the locking mechanism, which are all discussed due to the lack of space. Here is a brief introduction to locks divided by granularity.

Granularity: the level of refinement or synthesis of data stored in the data unit of a data warehouse. The higher the degree of refinement, the smaller the particle size; on the contrary, the lower the degree of refinement, the greater the particle size.

According to the granularity of locks, MySQL can be divided into row locks, table locks and page locks.

Row lock: a lock with the smallest granularity, indicating that it is locked only for the rows of the current operation

Table lock: the lock with the largest granularity, indicating that the current operation locks the entire table

Page lock: a lock whose granularity is between a row-level lock and a table-level lock, indicating that a page is locked.

Granularity Partition of Database

These three kinds of locks lock the data at different levels, and due to different granularity, their advantages and disadvantages are also different.

Table locks lock the entire table when manipulating data, so the performance of concurrency is poor.

The row lock only locks the data that needs to be operated, and the performance is good. However, because locking itself consumes resources (obtaining locks, checking locks, releasing locks, etc.), using table locks in the case of more locking data can save a lot of resources.

Different storage engines in MySQL can support different locks. MyIsam only supports table locks, while InnoDB supports both table and row locks, and row locks are used in most cases for performance reasons.

Concurrent reading and writing problems

In the case of concurrency, reading and writing at the same time of MySQL may lead to three kinds of problems: dirty reading, unrepeatability and phantom reading.

(1) dirty reading: the uncommitted data of other transactions is read in the current transaction, that is, dirty data.

As an example in the above figure, transaction A reads the data committed by transaction B when it reads the number of articles read. If transaction B does not commit smoothly in the end, resulting in a rollback of the transaction, then in fact, the reading volume has not been modified successfully, while transaction An is the modified value read, which is obviously unreasonable.

(2) non-repeatable reading: the same data is read twice in transaction A, but the results of the two reads are different. The difference between dirty reading and unrepeatable reading is that the former reads data that is not committed by other transactions, while the latter reads data that has been committed by other transactions.

In the above figure, for example, when transaction A reads the data of the number of articles read successively, the result is different. It indicates that during the execution of transaction A, the value of the reading amount is modified by other transactions. This makes the query results of the data no longer reliable and also impractical.

(3) Phantom reading: in transaction A, the database is queried twice according to a certain condition, and the number of rows of the query results is different. This phenomenon is called phantom reading. The difference between unrepeatable reading and phantom reading can be understood as: the former is that the data has changed, while the latter is that the number of rows of the data has changed.

The above figure is an example, when 0

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.