Write in front: the blogger is a real combat development after training into the cause of the “hill pig”, nickname from the cartoon “Lion King” in “Peng Peng”, always optimistic, positive attitude towards things around. My technical path from Java full stack engineer all the way to big data development, data mining field, now there are small achievements, I would like to share with you what I have learned in the past, I hope to help you on the way of learning. At the same time, the blogger also wants to build a perfect technical library through this attempt. Any anomalies, errors and matters needing attention related to the technical points of the article will be listed at the end, and everyone is welcome to provide materials in various ways.

  • Please criticize any mistakes in the article and revise them in time.
  • If you have any questions you would like to discuss or learn, please contact me at [email protected].
  • The style of the published article varies from column to column, and all are self-contained. Please correct the deficiencies.

Database concurrency and concurrency exceptions

Keywords: dirty read, dirty write, update lost, unrepeatable read, unreal read \

The article directories

When using the database to support the business system, with the increase of the number of users, we often encounter the situation of reading the same data at the same time, and will encounter all kinds of problems without concurrency control. We should have some understanding of the possible problems.

What is concurrency

And refers to at the same time, there are multiple programs are in the state between the start and finish running, and are running on the same processor. In the case of databases, parallelism refers to multiple transactions that are in the state between start and commit at the same time, all in the same database service.

Common concurrency exceptions

See the previous article, Database transactions and features, for more information about the features of transactions. Assuming that no concurrency control is done and those operations on the data are allowed to proceed freely, you will encounter the following problems:

1. Dirty reads

Dirty reads refer to the condition that one transaction A reads uncommitted data from another failed transaction B at run time. This results in transaction B being rolled back and transaction A reading an incorrect data that conflicts with the database record.



The money value of a data item in the database is20During processing, transaction B changes the value to100, which is then read by transaction A, and then rolled back by transaction B due to errors in other links, and the value of money is restored to20In this case, the data read by transaction A isDirty data(Error data).

2. Write dirty

Dirty write refers to the fact that one transaction A rolls back data that has already been committed by another transaction B, which will cause the successful operation of transaction B to be rolled back.



Transaction B succeeds before transaction A, but then transaction A rolls back, rolling back the commit from transaction B along with it.

3. Updates are lost

Update loss occurs when both transactions A and B commit successfully, but because of the timing of the read and write, the changes in transaction B appear not to have taken effect.



The original value of money in the database is20After transaction B commits successfully, the value of money is40, the normal logical transaction A subtracts the value of money by 20, and the result should be 20, but for transaction A the value is read, resulting in the final result0.

4. Do not read it repeatedly

Non-repeatable read refers to the situation that the data read by transaction A is inconsistent with the data read by another transaction B. The main difference between dirty reads and dirty reads is that one reads data that has been rolled back later, and one reads data that has been committed successfully, but is inconsistent.

5. Phantom reads

Phantom reading is when a range of data is read and the results of two different queries are different because of other transactions. The main difference between an unrepeatable read and a phantom read is that an unrepeatable read is an inconsistency between a specific piece of data, while a phantom read is an inconsistency between a set of data.



Before transaction B is executed, the result set obtained by transaction A is:One, three, four, sevenInsert a new account in transaction B:8After, the result set obtained by transaction A is:One, three, four, seven, eight.