Mysql Concurrency Control

Recently, I have been looking at Mysql concurrency control, transaction processing, and other knowledge to make some preparations. Concurrency Control aims to ensure data consistency when multiple connections modify the database. Currently, mysql InnoDB uses row-level locks during update and delete operations. For select operations, MVCC is used to ensure consistency. 1. Concurrency Control MySQL provides two levels of concurrency control: Service

Recently, I have been looking at Mysql concurrency control, transaction processing, and other knowledge to make some preparations. Concurrency Control aims to ensure data consistency when multiple connections modify the database. Currently, mysql InnoDB uses row-level locks during update and delete operations. For select operations, MVCC is used to ensure consistency. 1. Concurrency Control MySQL provides two levels of concurrency control: Service

Recently, I have been looking at Mysql concurrency control, transaction processing, and other knowledge to make some preparations.

Concurrency Control aims to ensure data consistency when multiple connections modify the database. Currently, mysql InnoDB uses row-level locks during update and delete operations. For select operations, MVCC is used to ensure consistency.

1. Concurrency Control
MySQL provides two levels of concurrency control: the server level and the storage engine level ). Locking is the basic method for implementing concurrency control. The granularity of locks in MySQL:
(1) TABLE-level locks: MySQL provides table locks independently of the storage engine. For example, the server provides TABLE-level locks for ALTER table statements ).
(2) Row-level locks: InnoDB and Falcon storage engines provide row-level locks. In addition, BDB supports page-level locks. The concurrency control mechanism of InnoDB is discussed in detail in the next section.
In addition to the blocking mechanism, some MySQL storage engines (such as InnoDB and BDB) also use the MVCC mechanism, multi-version two-phase blocking protocol MVCC (Multiversion two-phrase locking protocal) to implement concurrent control of transactions, so that read-only transactions do not have to wait for the lock, and improve the concurrency of transactions.

The principle of Database Transaction processing is to ensure the correctness of ACID.

2. Transaction Processing

2.1 ACID properties of transactions
A transaction is a logical processing unit composed of a group of SQL statements. A transaction has the following four attributes:
(1) Atomicity (Atomicity): a transaction is an atomic operation unit, and its modifications to the data are either all executed or all are not executed.
(2) Consistent: data must be Consistent at the beginning and end of the transaction. This means that all relevant data rules must be applied to transaction modifications to maintain data integrity. At the end of the transaction, all internal data structures (such as B-tree indexes or two-way linked lists) it must also be correct.
(3) Isolation: the database system provides a certain Isolation mechanism to ensure that transactions are executed in an "independent" environment not affected by external concurrent operations. This means that the intermediate state in the transaction processing process is invisible to the outside, and vice versa.
(4) Durable: after the transaction is completed, the modification to the data is permanent and can be maintained even if a system failure occurs.

2.2. Issues related to transaction processing
The concurrent execution of transactions brings about the following famous problems:
(1) Lost Update: When two or more transactions select the same row and Update the row based on the originally selected value, because every transaction does not know the existence of other transactions, an update loss occurs. The last update overwrites the updates made by other transactions.
(2) Dirty Reads: a transaction is modifying a record. before the transaction is completed and committed, the data of this record is inconsistent, another transaction also reads the same record. If no control is imposed, the second transaction reads the "dirty" data and performs further processing accordingly, the uncommitted data dependency is generated. This phenomenon is vividly called "Dirty reading ".
(3) Non-Repeatable Reads: A transaction Reads previously read data at a certain time after reading some data, it is found that the read data has changed, or some records have been deleted! This phenomenon is called "repeatable reading ".
(4) Phantom Reads: A transaction re-Reads previously retrieved data based on the same query conditions, but finds that other transactions Insert new data that meets the query conditions, this phenomenon is called phantom reading ".

3. Mysql isolation level:

Read uncommitted: the transaction can see the data not committed by other transactions (dirty data)
Read committed: the transaction can see the data COMMITTED by other transactions.
Repeatable read: The results of the two queries in the transaction are the same. (Default Innodb level)
SERIALIZABLE: all transactions are executed sequentially and all read operations are locked. Ensure consistency.

4. Notes:

(1) InnoDb uses MVCC to prevent Phantom Read, that is, another transaction inserted data and commit between two select statements. The two select statements ensure consistency. However, if the execution time and commit time of the insert/update/delete Statement of transaction B are before the first select of transaction, then, the first select statement of transaction A can read the updated data of transaction B.

(2) If A transaction A performs the update/insert/delete operation first and performs the first select operation, the select operation can read the update time, the result is that select may read data that does not exist at all (http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html)

(3) If A transaction A performs the select operation, then other transaction B after the select Operation updates the data (update/insert/delete ), select of transaction A will not see the result of transaction B operation. The Aselect transaction may encounter two situations during the traversal process. One is that the row currently traversed is being updated (update/insert/delete), and the row will be added with the write lock. The other is that the row currently traversed is not locked.

For the first case, Innodb is used to traverse data row by row during the select process. If a row is locked by write, in the undo segment, the previous snapshot data of this row will be found. This snapshot data is caused by the write operation. Each write operation will save the original data of the current transaction time, it is used for write failure rollback, so it is placed in the undo data segment.

In the second case, you can filter the transaction numbers. Each transaction in innodb has a transaction number stored in a hidden field of each row of data, if the current transaction number is smaller than the transaction number of the hidden field in the data record, it indicates that the current data is updated by the transaction following the current transaction, so the current data is not retrieved, instead, the snapshot data is retrieved from the undo segment.

(The original Mysql document is: Suppose that you are running in the defaultREPEATABLE READIsolation level. When you issue a consistent read (that is, an ordinarySELECTStatement ),InnoDBGives your transaction a timepoint according to which your query sees the database. if another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. inserts and updates are treated similarly .)