Mysql and multi-Version Concurrency control-mysql tutorial

Mysql transactions and multi-Version Concurrency control I. mysql transactions ?? A transaction is an atomic SQL query, or an independent unit of work. If the database engine can successfully execute all the statements in this group, execute the statements in this group. If one of the statements cannot be executed, none of the statements will be executed. That is to say, the language in the transaction

Mysql transactions and multi-Version Concurrency control I. What about mysql transactions? ? A transaction is an atomic SQL query, or an independent unit of work. If the database engine can successfully execute all the statements in this group, execute the statements in this group. If one of the statements cannot be executed, none of the statements will be executed. That is to say, the language in the transaction

Mysql transaction and multi-Version Concurrency control

I. mysql transactions

? ? A transaction is an atomic SQL query, or an independent unit of work. If the database engine can successfully execute all the statements in this group, execute the statements in this group. If one of the statements cannot be executed, none of the statements will be executed. That is to say, the statements in the transaction are either all successfully executed or all failed.

1. ACID properties of transactions

? ? Transactions require strict ACID support. ACID indicates atomicity, consistency, isolation, and durability. A well-running transaction processing system must have these standard features.

• Atomicity: a transaction must be regarded as an inseparable minimum unit of work. all operations in the entire transaction must be committed successfully or all fail to be rolled back. for a transaction, it is impossible to perform only one part of the operations, which is the atomicity of the transaction.
• Consistency: The database always changes from a consistent state to another consistent state. Consistency ensures that data will not be lost even if the system crashes after some of the operations in the transaction have been executed, because the transaction is not committed, therefore, the modified liquid in the transaction will not be saved to the database.
• Isolation: generally, modifications made by a firm before the final commit are invisible to other transactions. If another transaction needs to operate on the data while the transaction is being executed, other transactions still see the raw data.
• Durability: Once a transaction is committed, the modifications it makes will be saved to the database forever. At this time, even if the system crashes, the modified data will not be lost.

2. isolation level

? ? ? Isolation is actually more complex than imagined. Four isolation levels are defined in the SQL standard. each level specifies the modifications made in a transaction, which are visible between transactions in the transaction kernel, those are invisible. Lower-level isolation can usually execute higher concurrency, and the system overhead is also lower.

• Read uncommitted (uncommitted read): modifications made to transactions at this level are visible to other transactions even if they are not committed. A transaction can read uncommitted data, which is also called a dirty read. This level will cause many problems. in terms of performance, this level will not be much better than other levels, but it lacks many advantages of other levels, unless there are really necessary reasons, it is rarely used in practical applications.
• Read committed: The default isolation level of most database systems is read commited (mysql is not ). This level meets the simple definition of isolation mentioned above: At the beginning of a transaction, you can only see the modifications made by the submitted bookstore. In other words, any modifications made to a transaction from the beginning to before commit are invisible to other transactions. At this level, you can also perform non-repeated reads, because the same query may be executed twice and different results may be obtained.
• Repeatable read: This level solves the issue of dirty reading. This level ensures that the results of reading the same record multiple times in the same transaction are consistent. However, theoretically, the repeatable read isolation level still cannot solve another Phantom read problem. The so-called phantom read ----- refers to when a transaction reads records within a certain range, another transaction inserts new records within the range, A Phantom line is generated when the previous transaction reads records within this range again. The Innodb storage engine solves Phantom read problems through multi-version concurrency control (MVCC.
• SERIALIZABLE: this is the highest isolation level. He forced the transaction to execute in sequence to avoid the Phantom read problem mentioned above. To put it simply, this level locks each row of data to be read, which may cause a lot of timeout and lock contention problems. This isolation level is rarely used in practical applications. this level is considered only when data consistency needs to be ensured and no concurrency is acceptable.

3. deadlock

? ? A deadlock occurs when two or more transactions occupy each other on the same resource and request to lock the resources occupied by the other party, resulting in a vicious circle. When multiple transaction views lock resources in different order, a deadlock may occur. A deadlock occurs when multiple transactions lock the same resource at the same time.

? ? To solve this problem, the database system implements various deadlock detection and deadlock timeout mechanisms. The more complex the system is, the more it can detect the circular dependency of the deadlock and immediately return an error. This solution is very effective, otherwise the deadlock will lead to very slow queries. Another solution is to discard the lock request when the query time reaches the lock wait timeout setting. this method is usually not good. InnoDB currently handles deadlocks by rolling back transactions that hold at least rows.

? ? The behavior and sequence of locks are related to the storage engine. When statements are executed in the same order, some storage engines may experience deadlocks, and some may not. There are two reasons for deadlock: some are due to real data conflicts, which are usually difficult to avoid, but some are completely caused by the implementation of the storage engine.

? ? After a deadlock occurs, only partial or full rollback of one of the transactions can break the deadlock. This is unavoidable for transactional systems, so applications must consider how to handle deadlocks during design. In most cases, you only need to re-execute the transaction due to the deadlock rollback.

4. transaction logs

? ? Transaction logs can help improve the efficiency of transactions. With transaction logs, the storage engine only needs to modify its memory copy when modifying table data, and then records the modification behavior to the transaction logs that are persistent on the disk, instead of persisting the modified data to the disk each time. Transaction logs use the append method. Therefore, the log writing operation is the sequential I/O in the disk area, unlike random I/O, the head needs to be moved in multiple locations on the disk, so the transaction log method is much faster. After the transaction log persists, the modified data in the memory can be slowly flushed back to the disk in the background. At present, most storage engines are implemented in this way, which is usually called prewrite logs. to modify data, you need to write the disk twice.

? ? If the data modification has been recorded in the transaction log and persisted, but the data has not been written back to the disk, the system crashes and the storage engine can automatically restore the modified data when it is restarted.

II. Multi-Version Concurrency control

? ? Most transaction storage engines in mysql do not implement simple row-level locks. Based on the consideration of improving concurrency performance, they generally implement multi-version concurrency control (MVCC) at the same time ). MVCC can be considered as a variant of row-level locks, but it avoids locking operations in many cases, thus reducing the overhead. Although the implementation mechanism is different, most of them implement non-blocking read operations, and write operations only lock necessary rows.

? ? MVCC is implemented by saving snapshots of data at a certain time point. That is to say, no matter how long it takes to execute, the data displayed by each transaction is consistent. Depending on the start time of the transaction, each transaction may view different data at the same time for the same table.

? ? Different storage engines have different MVCC implementations. typical MVCC implementations include optimistic concurrency control and pessimistic concurrency control. The MVCC of InnoDB is implemented by saving hidden columns after each Record Row. The creation time of the rows and the Expiration Time (or deletion time) of the rows are saved ). Of course, the actual time value is not stored, but the system version number. The system version number increases automatically every time a new transaction starts. The system version number at the start of the transaction will be used as the transaction version number to compare with the version number of the queried records in each row. At the repeatable read isolation level, MVCC works as follows:

• SELECT ---- InnoDB checks each row of records based on the following two conditions: ① InnoDB only looks for data rows whose versions are earlier than the current transaction version (that is, the system version number of the row is smaller than or equal to the system version number of the transaction. This ensures that the row read by the transaction either exists before the transaction starts, either the transaction itself has been inserted or modified; ② the row deletion version is either undefined or later than the current transaction version. This ensures that the row read by the transaction is not deleted before the transaction starts.
• INSERT ---- InnoDB saves the current system version number as the row version number for each newly inserted row.
• DELETE ---- InnoDB saves the current system version number for each row to be deleted as the deletion identifier.
• UPDATE ---- InnoDB inserts a new record, saves the current system version number as the row version number, and saves the current system version number to the original row as the row deletion mark.

? ? Save the two additional system versions so that most read operations do not need to be locked. This design makes the read data operation very simple and has good performance, and can ensure that only the row that complies with the standard can be read. The disadvantage is that each row of records requires additional storage space, more row checks, and some additional maintenance work.

? ? MVCC only works at the repeatable read and read committed isolation levels. The other two isolation levels are not compatible with MVCC, because read uncommitted always reads the latest data rows instead of the data rows that match the current transaction version. SERIALIZABLE locks all read rows.