Mysql concurrent processing mechanism-part 1 and part 1

Mysql concurrent processing mechanism-part 1 and part 1
Back to write a blog, django was fascinated by the Front-End Time

If it is reprinted, please indicate the source of the blog: www.cnblogs.com/xinysu/. The copyright belongs to xiaoradish in the blog garden. Thank you for your support!

 

1. What is MVCC?The full name of MVCC is: Multiversion concurrency controlMulti-version concurrency control. It allows you to process the memory read from the transaction when you access the database concurrently, to avoid the concurrency issue of write operations blocking read operations. For example, programmer A is reading some content in the database, while programmer B is modifying the content (assuming it is modified in A transaction for about 10 s ), A may see an inconsistent data within 10 s. Before B does not submit, how can A ensure that all the data that A can read is consistent? There are several processing methods. The first one is lock-based concurrency control. When programmer B starts to modify the data, he adds A lock to the Data. Then, programmer A reads the data and finds that the data cannot be read, in the waiting condition, data can only be read after B's operation. This ensures that A will not read an inconsistent data, but this will affect the running efficiency of the program. Another type is MVCC. When each user connects to the database, they will see a database snapshot at a specific time point. Before B's transaction is committed, A always reads the database snapshot at A specific time, and does not read the data modification information in transaction B. It does not read the modification content of transaction B until transaction B is committed. A database that supports MVCC does not overwrite old data when updating some data. Instead, it marks that the old data is outdated and adds a new data version elsewhere. Therefore, the same data base has multiple versions, but only one is the latest. MVCC provides a time consistency processing idea. When reading transactions under MVCC, a timestamp or transaction ID is usually used to determine the database in which status to access and the data of which versions. Read transactions and write transactions are isolated from each other without affecting each other. Assume that the same copy of data includes both read transaction access and write transaction operations. In fact, write transactions create a new data version, while read transactions access the old data version, the new data version will not be accessed until the write transaction is committed. MVCC can be implemented in two ways. The first method is to save multiple versions of data records in the database. When data of different versions is no longer needed, the garbage collector recycles these records. This method is adopted by PostgreSQL and Firebird/Interbase, and similar mechanisms are used by SQL Server. The difference is that the old version of data is not stored in the database, stored in tempdb, another database different from the primary database. The second method is to save the latest version of data in the database, but the old version of data is dynamically reconstructed when undo is used. This method is used by Oracle and MySQL/InnoDB. This part can be viewed in Wikipedia: https://en.wikipedia.org/wiki/Multiversion_concurrency_control 2. MVCC of InnodbIn Innodb, no matter whether it is a clustered index or a secondary index, each row of record contains a DELETE bit, which indicates whether the record is deleted. At the same time, the clustered index has two hidden values: DATA_TRX_ID and DATA_ROLL_PTR. DATA _ TRX_ID indicates the transaction ID that generates the current record item. This ID increases with the creation of the transaction. DATA _ ROLL_PTR points to the undo information of the current record item. MVCC only works at the repeatable read and read commited isolation levels. Read uncommited is not compatible with MVCC, because the query cannot find the row version suitable for their transaction version; they can only READ the latest version each time. SERIABLABLE is not compatible with MVCC, because read Operations lock each row of data they return.  In MVCC, read operations are divided into two types: Current read and snapshot read. When the current read returns the latest record, the lock will be applied to ensure that the record will not be modified by other transactions; snapshot read, read a specific version of the record (either the latest or the old version) without locking. Snapshot read: RU, RC, RR isolation level, select * from tbname where... current read: This part reference: http://hedengcheng.com /? P = 148 3 Two Phase Locking2-PL is a two-phase lock. The lock operation is divided into two phases: locking and unlocking. First lock, then unlock, not intersection. When locking, the read operation applies for and occupies the S lock. The write operation applies for and occupies the X lock. If there is a conflict with the lock on the record, the lock will be in the waiting status, the next step is awakened only when the lock is successful. When a transaction is committed or rolled back, all locks in the transaction will be released and the locks will be released one by one. Assume that A transaction operates on both record A and record B, the locks and locks are unlocked row by row, as shown below:

BEGINLOCK AREAD AA:A+100WRITE AUNLOCK ALOCK BREAD BUNLOCK BCOMMIT

There are several special cases of two-phase locks: conservative (conservative), strict (strict), and strong strict (strong strict). These three types are different in the handling of lock locking and lock releasing. This part can be viewed in Wikipedia: https://en.wikipedia.org/wiki/Two-phase_locking, 4. data inconsistency 4.1 dirty readsIt is called dirty read to read the modified data in a non-committed transaction. For example, in table A (name, age), record 1 is name = 'internal', age = 188. Here, the data read from transaction 2 is (name, age) = ('x', 299), which is a non-committed record in transaction 1 and is dirty data. 4.2 update lossMultiple update operations are executed concurrently, resulting in data loss of some update operations. For example, in table A (name, age), record 1 is name = 'x', age = 188. Two concurrent update operations are as follows: normally, if the age is 288 after transaction 1 and transaction 2 is 288 + 100 = 388, but in fact, the operation of transaction 2 overwrites the operation of transaction 1, resulting in the loss of update of transaction 1. 4.3 non-repeated readThe same transaction reads the same existing record multiple times, but the read structure is inconsistent. For example, in table A (name, age), record 1 is name = 'x', age = 188. The operation is as follows: Transaction 1 reads data for the first time in the structure of 'xinysu ', age = 188, and the result of the second read is name = 'xinysu', age = 288, when the records in the same row are read multiple times but the results are inconsistent, the transaction cannot be read repeatedly. 4.4 phantom readThe same transaction reads data within a certain segment multiple times, but the number of rows read is inconsistent. This is a phantom read. For example, in table A (name, age), record 1 is name = 'x', age = 188. The operation is as follows: In transaction 1, the number of rows of results read for the first time has one row. If transaction 2 executes delete, transaction 1 reads 0 rows for the second time; if transaction 2 executes INSERT, the number of rows read by transaction 2 for the second time is two rows, and the number of records before and after transaction 2 is inconsistent, which is called phantom read. 5 innodb isolation level 5.1 Introduction to isolation level 5.2 isolation level testTest data inconsistency at each isolation level.

1. view the current session isolation level select @ tx_isolation; 2. view the current isolation level of the system. select @ global. tx_isolation; 3. set the current session isolation level set session transaction isolation level repeatable read; 4. sets the current system isolation level set global transaction isolation level repeatable read;

5.2.1 Read Uncommitted

Set the isolation level of all transactions: set session transaction isolation level read Uncommited;  Snapshot reads that are not available at this isolation level. All read operations are performed in the latest version to read uncommitted transaction data. Test 1: The update data is not submitted, and the test results are queried again: normal select queries can query non-committed transaction content, which is a dirty read Test 2: Modifying data is not submitted, test results of multiple queries of another transaction: The results of reading the same row record from the same transaction are inconsistent multiple times, which is a duplicate read Test 3: INSERT data is not committed, and the test results of multiple queries of another transaction are as follows: the same transaction reads data of the same range multiple times, but the number of rows is different. It is a phantom read Test 4: different transactions update the data of the same row. Test Result: Because INNODB has a lock mechanism, all updates are mutually exclusive with the X lock, and there is no loss of updates when transactions are committed successfully. Therefore, the loss of updates can be avoided at the four isolation levels. Summary: there are no snapshot reads, all of which are current reads, all of which are read and can read uncommitted records, and there are dirty reads, non-repeated reads, Phantom reads, and other problems. 5.2.2 Read Committed Set the isolation level of all transactions: set session transaction isolation level read committed;Because the isolation level supports snapshot reading, the select query statement for update and lock in share mode is not added, and the snapshot reading is used to read submitted records without adding locks. Therefore, the test uses the current read mode. Add lock in share mode and S lock. Test 1: The update data is not submitted, and the test result of the new query is as follows: because the current read holds the S lock, the update application X lock is waiting and cannot be updated, the results of multiple queries within the same transaction are consistent, with no dirty reads or repeated reads.

 

Test 2: INSERT data is not submitted, and test results are queried multiple times in another transaction: the same transaction reads data in the same range multiple times, but the number of rows is different, which is a phantom read (note here, if the insert statement is divided into beigin; commit, if the insert statement is not commit, the query of 3 will be waiting because the S lock applied for by the insert statement is blocked by the X lock)

 

Test 3: Snapshot read Test Result: the same transaction reads the same record multiple times, and all reads are committed records. There are no dirty reads or missing updates, but there are non-repeated and Phantom reads.

 

Conclusion: snapshot reading is supported. There are no dirty reads or missing updates for snapshot reading, but there are non-repeated reads and Phantom reads;Currently, there are no dirty reads and non-repeated reads, but there is a phantom read problem. 

5.2.3 Read Repeatable Set the isolation level of all transactions: set session transaction isolation level repeatable Read;Because the isolation level supports snapshot read, the select query statement for update and lock in share mode is not added, and the snapshot read and lock are used. Therefore, the test uses the current read mode. Add lock in share mode and S lock. Test 1: The update data is not submitted, and the test result of the new query is as follows: because the current read holds the S lock, the update application X lock is waiting and cannot be updated, the results of multiple queries within the same transaction are consistent, with no dirty reads or repeated reads. Test 2: INSERT data is not submitted, and test results are queried multiple times in another transaction: if the same transaction reads data within the same range multiple times, the GAP lock will lock, therefore, the number of current read result records for the same transaction is consistent for multiple times, and there is no phantom read.

 

Test 3: Snapshot read Test Result: the same transaction reads the same record multiple times, and there are no dirty reads, lost updates, non-repeated reads, and Phantom reads.

 

Conclusion: snapshot reading, snapshot reading, and Currently, there are no dirty reads and non-repeated reads, and phantom read problems exist.  5.2.4 Read Serializable Set the isolation level of all transactions: set session transaction isolation level Serializable ;This isolation level does not support snapshot reading. All SELECT queries are currently read and hold the S lock. test 1: update data is not submitted, and query is performed separately; INSERT data is not submitted, and test results are queried multiple times for another transaction: All select statements at the isolation level hold the S lock, as a result, the update application lock X is waiting, and the INSERT application X is blocked. The results of multiple queries in the same transaction are consistent, and there are no dirty reads, non-repeated reads, and Phantom reads. Conclusion: No snapshot read, all SELECT queries are Currently, there are no dirty reads and non-repeated reads, and Phantom reads.  I thought it was gone, not. There is another concept that is not submitted here. Here I will introduce semi-consistent read PS: semi-consistent read  At the read committed or read uncommitted isolation levelTest TABLE and data create table 'tblock' ('id' int (11) not null AUTO_INCREMENT, 'name' varchar (10) default null, primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 3 default charset = utf8; insert into tblock (name) select 'su '; insert into tblock (name) select 'xin '; Test 1: Two update transactions are concurrently updated, and different rows are updated respectively. The update condition column has no index. Test results: the two updates do not affect each other and are executed normally.

 

Test 2: The update statement is not submitted, and the current read operation of another transaction is tested. Result: The current read is blocked and the X lock cannot be properly applied. Question: Why is the SQL number 2 in both tests applying for the X lock? Test 1 can be applied normally, but test 2 won't?Under normal circumstances, the name column in The where condition does not have an index, so this update operation adds the X lock to scan the entire table. Normally, in the first transaction, if the update statement is not submitted, there is a table lock X in this table, and the S lock or X lock cannot be applied for each row of data. Why can I apply for a normal update statement? Here, semi-constent-read must be introduced to ensure semi-consistent read. The official website is explained as follows: Semi consistent read: A type of read operation used for UPDATE statements, that is a combination of read committed and consistent read. when an UPDATE statement examines a row that is already locked, InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. if the row matches (must be updated), MySQL reads the row again, and this time InnoDB either locks it or waits for a lock on it. this type of read operation can only happen when the transaction has the read committed isolation level, or when the innodb_locks_unsafe_for_binlog option is enabled.Semi-consistent read is an operation of the update statement to read data. It is a combination of read committed and consistent read. When update Statement A is not submitted, another update statement B reads A row of records that have been locked by A, but this row of records is not in the where condition of, at this time, InnoDB returns a record of the recently submitted version to B, and the MySQL upper layer determines whether this version meets the where condition of B's update. If yes (update required), MySQL will initiate a read operation again, and read the latest version (and lock) of the row ). Semi-consistent read only occurs in read committed and Read uncommittedIsolation level, or the innodb_locks_unsafe_for_binlog parameter is set to true. It works for update and does not work for select insert delete. This causes update Not To Be congested, but the current read select statement is blocked. The conditions for semi consitent read are summarized as follows: