1. What is ACID

  • Atomicity: A transaction is the smallest unit of execution and does not allow separation. The atomicity of the transaction ensures that the action either completes completely or does not work at all
  • Consistency: Data is consistent before and after a transaction. Multiple transactions read the same data with the same result
  • Isolation: When accessing the database concurrently, one user’s transactions are not disturbed by other transactions, and the database is independent between all concurrent transactions
  • Durability: After a transaction is submitted, its changes to data in the database are persistent and should not be affected if the database fails

2. Concurrency problems

2.1. Dirty reads

Scenario: Two operations reach the database at the same time. Operation A queries the age of data X. Operation B updates the age of data X

In full compliance with ACID:

In this case, the transactions for operation A and operation B are serialized, and either operation A or operation B takes place first. There is no crossover and everything is fine.

Concurrent case:

When the transaction of operation B modifies data X, but the transaction is not committed, operation A starts to query data X, and after the query result is obtained, the transaction of operation B completes the commit. In this case, the data queried by transaction A is out of date, which is called A dirty read.

2.2. Lost modifications

Scenario: Operation A and operation B query data X on {id=1,age=20} and perform the +1 operation on age

In full compliance with ACID:

  1. Step 1: The transaction of A queries data x. age=20 and updates data X.age= age + 1 = 21. The transaction ends.
  2. Step 2: After the transaction of operation A is complete, start the transaction of operation B. The data x. age=21 is queried and updated. The transaction is complete.
  3. The final result, x.age =22

Concurrent case:

  1. Operation A transaction query x. age=20 and update x. age= age + 1 = 21. At this time, the transaction is not committed.
  2. When transaction A is not committed, operation B queries the value of x. age=20, updates the value of x. age + 1 =21, and submits transaction x. age=21
  3. Commit transaction for operation A, x.age =21
  4. The final result is x.age =21

The last committed transaction overwrites the first committed transaction, in which case the modification is lost.

2.3. It cannot be read repeatedly

Scenario: Perform operation A twice in the same transaction. Uid =1 User age, original data age=18. Operation B updates age=20 where uid=1

In full compliance with ACID:

  1. The age of uid=1 is 18 no matter how many times the query is performed in this transaction
  2. If uid=1 and age=20, commit the transaction.
  3. The age value 18 obtained in operation A is fixed and normal

Concurrent case:

  1. In the transaction of operation A, age=18 is the first query, and operation B is executed concurrently
  2. Operation B updates the age=20. Operation B commits the transaction when operation A does not perform the second query
  3. Operation A now performs the second query within the transaction, age=20
  4. Two different query results within the same transaction are called unrepeatable reads

2.4. Magic to read

Scenario A Twice queries all users whose age is less than 20 in the same transaction, for example, 20 messages. Operation B Add a user whose age is less than 20

In full compliance with ACID:

  1. No other transaction is allowed to execute until operation A commits, so each query within this operation has 20 results
  2. After operation A commits the transaction, operation B executes
  3. The two operations are serial and the operation result is normal

Concurrent case:

  1. Operation A starts the transaction, and the first query result is 20
  2. When operation A does not perform the second query, operation B starts the transaction, inserts A data with user age<20, and commits the transaction
  3. Operation A performs the second query, and the result is 21
  4. At this point, the two query results are inconsistent, as if there is an illusion. We call this case of multiple queries increasing or decreasing data a phantom read

2.5. The difference between dirty reading and non-repeatable reading

The two cases are very similar and can be interpreted as follows:

  • Dirty read: In a transaction, only one query is performed. When the data is changed, the transaction does not know that the data it queried has changed. This is called dirty read
  • Non-repeatable read: In a transaction, the transaction is aware of changes in the queried data during the process of multiple queries. This is called non-repeatable read

The main difference is a query, or multiple queries.

3. MySQL isolation level

  • Read-uncommitted: The lowest isolation level, allowing UNCOMMITTED data changes to be READ, which may cause dirty reads, illusory reads, and unrepeatable reads.
  • Read-committed:Allows concurrent transactions to read committed data, which can be preventedDirty read, butUnreal read, non – repeatable readIt could still happen
    • Two transactions. If one is not committed, the other cannot be read, so dirty reads can be avoided
    • In transaction A, the first time to read multiple messages, transaction B has not committed; After transaction B is committed, transaction A conducts A second query, which may find the data submitted by transaction B. If transaction B is an insert operation, it is a phantom read; If transaction B is an update operation, it is a non-repeatable read
  • REPEATABLE-READ:Multiple reads of the same field are consistent, unless the data is modified by the transaction itself, preventing dirty reads and unrepeatable reads, butPhantom readCan still happen
    • Two transactions, transaction A, the first query of multiple data, transaction B can be inserted or updated; If transaction B is updated, since the data is not modified by its own transaction, there are no dirty and unrepeatable reads; If transaction B is inserted, it will still be queried by transaction A, resulting in A phantom read
  • SERIALIZABLE: Highest isolation level, fully subject to ACID isolation level. All transactions are serialized and executed sequentially, with no interference between transactions at all. This level prevents dirty reads, unrepeatable reads, and phantom reads.

MySQL InnoDB storage engine supports isolation level REPEATABLE-READ by default

4. The last

Interviewer: Please talk about MySQL transactions