Understand the transaction isolation level of mysql

foreword

The scores of the postgraduate entrance examination have been settled, and I missed this year, so I started to prepare for the spring recruitment. When it comes to this year's postgraduate entrance examination, the score can be said to be rising. Last year, the school was able to enter 270 for the master's degree. This year, a roommate scored 270 and ranked 140 (70 majors are required). It is basically hopeless to enter the re-examination. to 290.
If you go to school recruiting, there are all aspects of the company's general interview questions, and the focus is inseparable from the basic knowledge of algorithms and languages. Dachang attaches great importance to the algorithm ability of school admissions, because the school admissions project has little experience, and the Dachang project has a large number of users. Algorithm This is to do more questions on the force button and learn more about the answer.
For the basic knowledge of language, you need to read more books. There are some things that we can't usually develop, and the interviewer loves to ask. The interview is like this, the interview builds the rocket, and the job turns the screw. Xishuo senior recommended two books, one "In-depth MySQL", written by Zhai Zhenxing, and one "In-depth Understanding of JVM Virtual Machine", written by Zhou Zhiming. A cursory look at "Mysql in Simple" is more like a mysql dictionary. It covers everything from basic sql statements to sql optimization and maintenance. You can also buy this book without textbooks in the psychology database. It is definitely better than the textbooks issued by schools. , Most of the school issued are written by the school teachers themselves. And there are more examples in it, which is easy to understand. "In-depth understanding of jvm virtual machine" can be read as an extended reading. It talks about some basic knowledge of java, such as what happens to a new object, how jvm performs memory recovery, and jvm tuning. We don't know these things and it doesn't affect us. Developed with java. And there are few examples, it is recommended to read this book with a certain java foundation and operating system knowledge.

mysql lock overview

Locks are an important mechanism for coordinating the concurrent access of various processes or threads to a resource. A good locking mechanism not only ensures the consistency and validity of concurrent data access. And also minimize the impact of concurrency performance.
We know that the InnoDB engine supports transactions relative to others. A transaction is a logical processing unit composed of a group of SQL statements and has four properties, often referred to as transaction ACID properties.

• Atomicity: A transaction is an atomic unit of operation, either all or none.
• Consistency: Data is kept in a consistent state at the start and completion of a transaction. That is, the data remains in a correct state at the start and completion of the transaction.
• Isolation: The database provides a certain isolation mechanism to ensure that transactions run in an environment that is not affected by external transactions. This means that the intermediate state during transaction processing is invisible to other transactions.

• Persistence: After the transaction is completed, the modification of the data is permanent.
  We take the classic bank transfer to understand transactions.
  For example, Zhang San's account wants to transfer 1,000 yuan to Li Si's account.
  Then the sql statement is like this

  update bank set balance = balance - 1000 where name = '张三';
  update bank set balanc = balance + 1000 where name = '李四';

  If we typo one of these fields, then one of the statements will not execute correctly.

  update bank set balance = balance - 1000 where name = '张三';
  update bank set balance = balance + 1000 where name = '李四';

  Then it will be construed that Zhang San lost 1,000 yuan and Li Si did not have 1,000 yuan more.
  This doesn't happen if you join a transaction.
  Atomicity guarantees that either both statements will execute, or neither will execute.
  Consistency guarantees that the total number remains the same when the money transfer process starts and finishes, and the balance never drops below 0.
  Isolation ensures that this transaction will not affect other transactions until the execution is completed.
  Durability guarantees that changes to money after a transaction is complete are permanent.

  Problems with concurrent transactions

  Concurrent transactions increase database utilization, improve throughput, but also bring some problems.

• Update lost:

  Transaction A	Transaction B
  open a transaction	open a transaction
  Add 200 to Zhang San's account balance	Add 500 to Zhang San's account balance
  submit	/
  /	submit

  This resulted in two people depositing money for Zhang San at the same time, one depositing 200 and the other 500. As a result, the account was only increased by 500 and 200 was gone.

• Dirty read:

  Transaction A	Transaction B
  open a transaction	open a transaction
  Query Zhang San's balance is 1000
  If the query result is greater than or equal to 1000, the balance of Zhang San's account will be reduced by 1000	Query Zhang San's balance is 1000
  /	If the query result is greater than or equal to 1000, the balance of Zhang San's account will be reduced by 1000
  /	submit
  submit	/

• Unrepeatable read:

  Transaction A	Transaction B
  open a transaction	open a transaction
  Query Zhang San's balance is 1000	/
  /	Add 500 to Zhang San's account balance
  /	submit
  Query Zhang San's balance is 1500	/
  submit	/

  This causes the two query results of transaction A to be inconsistent, and I don't know which one prevails.

• Phantom reading:

  Transaction A	Transaction B
  open a transaction	open a transaction
  Check the balance of the person surnamed Zhang, and find that the balance of Zhang San is 1000	/
  /	Create a new account called Zhang Si with a balance of 0
  /	submit
  Check the balance of the person surnamed Zhang, and find that the balance of Zhang San is 1000, and the balance of Zhang Si is 0	/
  submit	/

  Update loss only ensures that one transaction cannot change the data until another transaction commits the update to the data. This is mainly solved at the application level.
  Dirty reads, non-repeatable reads, and phantom reads need to be resolved by a certain transaction isolation mechanism provided by the database. The database implements data isolation methods, which are basically divided into the following two types:

• One is to lock the data before reading it, preventing other transactions from making changes to the data.

• One is to generate a data snapshot at the time of the data request through a certain mechanism without adding any locks, and use this snapshot to provide a certain level of data reading. From the user's point of view, the Haoxiang database can provide different versions of unified data. This technology is called data multi-version concurrency control, or MVCC. About MVCC, you can read this article this article
  The tighter the database transaction isolation, the smaller the concurrent side effects, but the greater the cost. It's a balancing act. To solve the contradiction between isolation and concurrency in the future, sql defines 4 transaction isolation levels. The application selects different isolation levels according to its own business logic requirements to balance the contradiction between isolation and concurrency.

  	dirty read	non-repeatable read	hallucinations
  Uncommitted read READ-UNCOMMITTED	Yes	Yes	Yes
  Committed read READ-COMMITTED	no	Yes	Yes
  Repeatable read REPEATABLE-READ	no	no	Yes
  Serializable SERIALIZABLE	no	no	no