Shulou(Shulou.com)06/01 Report--

This article is from: https://blog.csdn.net/sinat_27143551/article/details/80876127

This series of articles will be sorted out in my Java interview Guide warehouse on GitHub. Please check out more wonderful content in my warehouse.

Https://github.com/h3pl/Java-Tutorial

Have some trouble with Star if you like.

The article was first posted on my personal blog:

Www.how2playlife.com

This article is one of the "re-learning MySQL database" of Wechat official account [Java technology jianghu]. Part of this article comes from the Internet. In order to explain the topic of this article clearly and thoroughly, it also integrates a lot of technical blog content that I think is good. I quote some good blog articles, if there is any infringement, please contact the author.

This series of blog posts will show you how to start to advanced, from the basic usage of sql, from MySQL execution engine to index, transaction and other knowledge, to learn the implementation principles of MySQL-related technologies step by step, to better understand how to optimize sql based on this knowledge, to reduce SQL execution time, to analyze SQL performance through execution plans, and then to master-slave replication and master-slave deployment of MySQL. So that you can have a more complete understanding of the whole MySQL technical system and form your own knowledge framework.

If you have any suggestions or questions about this series of articles, you can also follow the official account [Java Technology jianghu] to contact the author. You are welcome to participate in the creation and revision of this series of blog posts.

Basic elements of a transaction (ACID)

1. Atomicity: after the transaction starts, either all operations are done or none are done, and it is impossible to stay in the middle. When an error occurs during the execution of the transaction, it is rolled back to the state before the start of the transaction, and all operations are as if they had not occurred. In other words, affairs are an indivisible whole, just like atoms learned in chemistry, are the basic units of matter.

2. Consistency: before and after the transaction starts, the integrity constraints of the database are not broken. For example, if A transfers money to B, it is impossible for A to deduct the money, but B does not receive it.

3. Isolation: only one transaction is allowed to request the same data at a time, and there is no interference between different transactions. For example, An is withdrawing money from a bank card, and B cannot transfer money to this card until the withdrawal process of An is over.

4. Durability: after the transaction is completed, all updates made by the transaction to the database will be saved to the database and cannot be rolled back.

Concurrency of transactions

1. Dirty reading: transaction A reads the data updated by transaction B, and then B rolls back the operation, then the data read by An is dirty data.

2. Non-repeatable reading: transaction A reads the same data many times, and transaction B updates and commits the data in the process of reading the same data many times, resulting in inconsistent results when transaction A reads the same data many times.

3, illusory reading: system administrator A changes the scores of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record of specific scores at this time. When system administrator A changes to the end, he finds that there is a record that has not been changed, as if there were hallucinations, which is called phantom reading.

Summary: non-repeatable reading and phantom reading are easy to be confused, non-repeatable reading focuses on modification, and phantom reading focuses on adding or deleting. To solve the problem of unrepeatable reading, you only need to lock the rows that meet the conditions, and to solve the phantom reading, you need to lock the table.

MySQL transaction isolation level

Transaction isolation level dirty reading can not repeat phantom reading

Read unsubmitted (read-uncommitted) is

Read submitted (read-committed) Yes or not

Repeatable readable (repeatable-read) No

Serialization (serializable) No

Default transaction isolation level:

1. Read unsubmitted examples

(1) Open client A, set the transaction mode to read uncommitted, and query the table

(1) before A commits the transaction, open client B and update the table

(3) at this time, the transaction of B is not committed, but A can already find the updated data of B.

(4) once the transaction of B is rolled back for some reason, the data found by An is dirty data.

(5) when you execute the update statement in A, you will find that the result is very strange when you do not know that other transactions are rolled back. To solve this problem, read the committed transaction isolation level.

2. Read submitted

(1) set the transaction mode to read committed on client A

(2) before the client A transaction commits, open client B and start the transaction update table.

(3) the transaction of B has not been committed, and A cannot find the updated data, which solves the problem of dirty reading:

(4) commit the transaction of client B at this time

(5) An executes the same query as the previous step, and the result is different from the previous step. This is the problem of unrepeatable reading:

3. Repeatable

(1) Open client An and set the transaction mode to repeatable read.

(2) before A transaction commits, open client B, update table account and commit:

(3) when the query of step 1 is executed in client A, the balance of zhangsan is still the same as that of step (1), and there is no problem of non-repeatable reading; then the balance value of update balance = balance-50 where id=4;balance is calculated using 400 in step (2), so it is 350. The consistency of the data has not been broken.

(4) commit the transaction on client A

(5) Open the transaction in client A, then open the transaction in client B, and add a new piece of data. Submit

(6) when A calculates the sum of balance, the value is 350,16000,2400,18750, and the new data of client B is not taken into account. Client A submits and then calculates the sum of balance, which becomes 19350. At this time, client B's balance is included. From the customer's point of view, the customer can not see client B, he will feel that there is a pie in the sky, more than 600 yuan, this is illusory reading, from the developer's point of view, the consistency of the data has not been broken. However, in the application, our code may commit 18750 to the user, and if we must avoid this low-probability situation, we should adopt a "serialization" transaction isolation level.

4. Serialization

(1) Open client A, set the transaction isolation level to serializable and start the transaction.

(2) Open client B, set the transaction isolation level to serializable, enable transaction to insert data, and report an error. The table is locked and the insert fails. The table is locked when the transaction isolation level in mysql is serializable, so there is no phantom reading. This isolation level has low concurrency and is rarely used in development.

Add:

1. There may be some differences in the specific implementation of different databases according to the standards stipulated in the SQL specification.

2. The default transaction isolation level in mysql is repeatable and the rows read will not be locked.

3. When the transaction isolation level is read commit, the write data will only lock the corresponding rows.

4. When the transaction isolation level is repeatable, if there is an index (including the primary key index) and the data is updated with the index as a condition, there will be the problem of gap lock, row lock and next key lock, which will lock some rows; if there is no index, the whole table will be locked when the data is updated.

5. When the transaction isolation level is serialized, both read and write data will lock the entire table

6. The higher the isolation level, the more you can ensure the integrity and consistency of the data, but the greater the impact on concurrent performance, you can't have both. For most applications, priority can be given to setting the isolation level of the database system to Read Committed, which can avoid dirty reads and has good concurrency performance. Although it can lead to concurrency problems such as unrepeatable reading and phantom reading, it can be controlled by the application using pessimistic or optimistic locks in individual situations where such problems may occur.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.