MySQL transaction Insider with acid

The MySQL transaction implementation strictly adheres to the acid characteristics, i.e. atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability). To avoid getting caught up in the concept of acid in the first step, we will talk about the MySQL transaction implementation mechanism directly.

MySQL provides two types of transactional storage engines: InnoDB and NDB Cluster. There are also third-party storage engines that support transactions, more well-known including XTRADB and PBXT. The following is illustrated by InnoDB.

            MySQL will maximize the use of caching mechanisms to improve database access, but in the event of a database outage, because the cached data is not written to disk, What happens if data is lost in memory that causes data to be out of sync?

          &NBSP;&NBSP;INNODB The acid feature is primarily implemented through transaction logs, which can help improve the efficiency of transactions. With transaction logs, the storage engine modifies the table's data only by modifying its memory copy, and then logging the modification behavior to the transaction log that is persisted on the hard disk without having to persist the modified data itself to disk each time. < Span style= "color: #000;" > download

The transaction log is appended, so the log operation is sequential I/O for a small area of disk, rather than a random I/O that requires moving the heads in multiple places on the disk. So the way the transaction logs are relatively much faster. After the transaction log is persisted, the data that is modified in memory can be slowly brushed back to disk in the background. Most of the current storage engines are implemented in this way, which we call pre-write logging, which requires two disk writes to modify the data.

The transaction log includes the redo log redo and the rollback log Undo,redo records that are all completed transactions, that is, a COMMIT transaction, the log file is Ib_logfile0 ib_logfile1. Undo records unfinished transactions that have been partially completed and written to the hard disk, and the rollback log is logged in the table space by default (shared tablespace or exclusive table space).

            Typically, after a crash, MySQL restarts the service, InnoDB by rolling back the log undo to rollback all unfinished transactions that have been completed and written to disk, Then all the transactions in the redo are re-executed again to recover the data, but as the amount of redo increases, each time it takes a long time to recover from the first piece of redo, the checkpoint mechanism is introduced.

          in general business operations, when a business needs to modify a row of data in a table, InnoDB will first read the data from the disk into the cache, and then modify the data in the cache. So the data in the cache is inconsistent with the data on the disk, and this time the data in the cache is called the Dirty page, which is clean page only if the dirty pages are flushed to disk uniformly. < Span style= "color: #000;" > download

Checkpoint: If the dirty page data is flushed to disk at a certain point in time, the system will record the refreshed point to the end of redo log, and when the data is restored, the data before the Checkpoint point in time will not need to be restored, which can shorten the time.

Innodb_log_buffer_size Redo Log Cache size

Innodb_log_file_size Redo log File Size file The larger the data recovery time

The default number of Innodb_log_file_group redo log files is 2 Ib_logfile0 ib_logfile1

The mechanism of the transaction log is actually the atomicity and persistence of the transactions that are satisfied, that either both succeed or fail. When it comes to the consistency and isolation of transactions, it is about "locks".

The InnoDB uses a two-phase locking protocol. Locks can be executed at any time during the execution of a transaction, and the lock is freed only when a commit or rollback is executed, and all locks are released at the same moment. InnoDB automatically locks up when needed, depending on the isolation level.

           mysql are not simple row-level locks. Based on the consideration of improving concurrency performance, they generally implement multi-version concurrency control (MVCC) at the same time. The MVCC implements a non-blocking read operation, and the write operation locks only the necessary rows. The implementation of MVCC is achieved by saving the snapshot of the data at a certain point in time. For InnoDB, this is done by saving two hidden columns after each row of records. These two columns, one saving the creation time of the row, one saving the line's expiration time (or deletion time). Of course, not the actual time value is stored, but the system version number. Each start of a new transaction, the system version number is automatically incremented. The system version number at the start of a transaction is used as the version number of the transaction to compare to the version number of each row of records queried. Here's a look at the MySQL default isolation level repeatable Read, MVCC how it's done. < Span style= "color: #000;" > download

SELECT

InnoDB checks each row of records according to the following two criteria:

A. InnoDB only finds rows of data that are earlier than the current version of the transaction (that is, the system version number of the line <= the system version of the transaction), which ensures that the transaction reads the rows that existed before the transaction began, or that the transaction itself was inserted or modified.

B. The deleted version of the row is either undefined or larger than the current transaction version number. This ensures that the transaction is read to a row that has not been deleted before the transaction begins.

Only records that meet the above two criteria can be returned as query results.

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 as the deletion identity for each row that is deleted.

UPDATE

InnoDB to insert a new row of records, save the current system version number as the line version number, and save the current system version number to the original line as the row delete identity.

  saves these two additional system version numbers so that most read operations can be unlocked. This is easy to read and performs well, and can be guaranteed to read a line that conforms to the standard. The disadvantage is that additional storage space is required, more row checking is needed, and some additional maintenance work is required. In addition, MVCC only works under the two isolation levels of repeatable read and read commited. The other two isolation levels are incompatible with MVCC because read uncommited reads to the most recent data row, not to the current transaction version of the data row, and serializable locks all the rows read.

Well, I believe you have some understanding of the MySQL transaction implementation mechanism, the following excerpt from the two paragraphs on the understanding of acid. Download

--------------

Reasoning

Definition: Database consistency means that the result of a transaction execution must be to change the database from one consistent state to another.

How does the database state change? Each time a data change results in a state migration of the database. If the initial state of the database is C0, the submission of the first transaction T1 results in a system change number (SCN) being generated, which is the database state from C0 to C1. When executing the second transaction T2 the database state changes from C1 to C2, and so on, the database state is changed from C (n-1) to CN when performing the first TN transaction.

There are 2 main aspects of definition consistency, consistent read and consistent writing.

Consistent write: Data changes performed by a transaction can only be based on the previous consistent state and only in one state. The change result of T (n) can only be based on C (n-1), C (n-2), ... C (1) state, and can only be reflected in the C (N) state. In other words, a state can have only one transaction change data, and No 2 or 2 transactions are allowed to change the data in one state. As to which state the specific consensus is based on, it is necessary to determine whether T (n) transactions and T (n-1), T (n-2),... T (1) has a dependency relationship.

Consistent read: Transactional read data can only be read from one state and cannot be read from 2 or more than 2 states. That is, t (n) can only be from C (n-1), C (n-2) ... A state in C (1) reads the data, not a portion of the data is read from C (n-1), and the other part of the data is read from C (n-2).

Pendulum facts

Consistent write:
Define 100 transactions T (1) ... T (100) Implement the same logical Update table set I=i+1,i The initial value is 0, so what is the value of I after executing the 100 transactions concurrently? It may be easy to think of it as 100. So how do you understand it from a consistency perspective?

The database is randomly dispatched to T (50), at which time the database state is C (0), and the other transactions are dependent on T (50), and according to the write consistency principle, other transactions must wait until T (50) has finished executing until the database state changes to C (1). Therefore, the database uses a lock mechanism to block execution of other transactions. Until T (50) is completed, the database state is migrated from C (0) to C (1). When the database wakes up other transactions, it is randomly dispatched to T (89), and so on until all the transaction schedules are executed, the database state eventually changes to C (100).Download
Consistent reading:
Or the above example, suppose T (1) ... T (100) sequential execution, at different times execute select I from table, we see what is the value of I?
1. T (1) in the course of execution. Database state has not been migrated, read the i=0

2. T (1) completed, T (2) of the execution process, the database state migrated to C (1), read the I=1

---------------

How can database transaction consistency not mention the acid characteristics of the database. The
first describes the transaction, what is the transaction, and the transaction is any execution of the user program in the DBMS, the basic modification unit that the transaction room DBMS can see. A

transaction is a set of operations performed on a system that, in order to ensure the integrity of the system, needs to have acid properties, as follows:
1. atomicity (Atomic)
A transaction contains multiple operations that either execute all or do not. To implement the atomicity of a transaction, to support a rollback operation that, after an operation fails, rolls back to the state before the transaction executes.
Rollback is actually a high-level abstraction, and most DB implements transactions in a data snapshot of a transactional operation (for example, MVCC), does not modify the actual data, and if the error is not committed, it is natural to support rollback.
in other systems that support simple transactions, the actual data is not updated on the snapshot and is directly manipulated. You can rehearse all the actions you want to perform, and if you fail, these operations will not be executed, which is a simple way to achieve atomicity. < Span style= "color: #000;" > download

2. Consistency (consistency)
consistency refers to a transaction that transforms a system from a consistent state to another consistent state. The consistency of transactions determines the complexity of a system's design and implementation. Transactions can be of varying degrees of consistency:
Strong Consistency : Read operations can read immediately to the submitted update operation.
Weak consistency : A commit update operation that is not necessarily read immediately by the read operation, in which case there is an inconsistency window that refers to a time when the read operation can read to the latest value.
Final Consistency : is a special case of weak consistency. The transaction updates a copy of the data, and eventually the consistency guarantees that the same value is updated in the absence of other transactions, and eventually all transactions are read to the latest value of the previous transaction update. If no error occurs, the size of the inconsistency window depends on: Communication delay, system load, and so on.
Other conformance variants are:
monotonic Consistency : If a process has read a value, subsequent values will not be read.
Session Consistency : During a session that guarantees client-server interaction, read operations can read the latest values after the update operation.

3. Isolation (isolation) < Span style= "color: #000;" > download

The degree to which concurrent transactions affect each other, such as whether a transaction will read to another uncommitted transaction modified data. Problems that can occur when a transactional concurrency operation are:
Dirty Read : Transaction a modifies a data, but not committed, transaction B reads the result of an uncommitted update to transaction A, and if transaction a commits fails, transaction B reads dirty data.
non-repeatable read : In the same transaction, the results read to the same data are inconsistent. For example, the result that transaction B reads before transaction A is submitted may be different from the results read after the commit. Non-repeatable reads occur because transactions are concurrently modifying records, to avoid this situation, the simplest way is to lock the records to be modified, which leads to increased lock competition, affecting performance. Another approach is to avoid non-repeatable reads with MVCC without locking.
Phantom Read : In the same transaction, the same query returns multiple times with inconsistent results. Transaction A adds a new record, and transaction B performs a query operation before and after transaction a commits, discovering that one more record than the previous one. Phantom reads are caused by the increased recording of concurrent transactions, which cannot be resolved by record locking as non-repeatable reads because new records cannot be locked at all. You need to serialize the transaction to avoid Phantom reads.
The isolation level of a transaction is from low to High:
READ UNCOMMITTED: The lowest isolation level, nothing needs to be done, one transaction can read the results of another transaction uncommitted. All concurrent transaction problems can occur.
Read Committed: Only after a transaction commits, its update results are seen by other transactions. Can solve the dirty read problem.
repeatedread: In a transaction, the result of reading the same data is always the same, regardless of whether there are other transactions to manipulate the data, and whether the transaction is committed. Can resolve dirty read, non-repeatable read.
serialization: Transaction serialization execution, the highest isolation level, sacrificing the concurrency of the system. All problems with concurrent transactions can be resolved.
Often, in engineering practice, isolation is compromised in the interest of performance considerations.

4. Persistence (Durability)
After a transaction is committed, the effect on the system is permanent.

MySQL transaction Insider with acid