A, the transaction

A transaction is a logical unit of DBMS execution consisting of a finite sequence of database operations. In MySQL, transactions are implemented at the engine level. For example, MyIsam does not support transactions. InnoDB does

Second, the ACID

When it comes to transactions, ACID comes to mind, so get a feel for the concept, and then we’ll talk about isolation.

Atomicity: All operations of a transaction are either all successful or all rolled back.

Consistency: Always transitions from one consistent state to another consistent state.

Isolation: When multiple transactions are executed concurrently, the execution of one transaction should not affect the execution of other transactions

Durability: Modifications to the database by committed transactions should be permanently saved in the database.

Iii. Isolation level

I have been a CRUDer for many years. I am familiar with these words and would not have understood them if I had not gone out for an interview. I hope you don’t panic after the interview, just with him.

In fact, these scenarios occur when multiple transactions are executed simultaneously.

3.1 Dirty Reads (Read Uncommitted)

In layman’s terms, a transaction reads uncommitted data from another transaction during processing.

You have not submitted, I read the data you just operated, what if you roll back, you say it is dirty.

For example:

Suppose the logic of tipping is: ① My account +1 yuan; ② Your account -1 yuan.

When you performed the first step, I checked my account and it was already 2 yuan, very happy!! Announce please go to masturbation!! But the final deduction of the time found that your balance is insufficient, rolled back, MY 1 yuan did not, very uncomfortable!!

3.2 Non-repeatable Read

In layman’s terms, multiple queries for the same data within the scope of a transaction get different results.

The difference between a dirty read and a dirty read is that a dirty read reads uncommitted data, while an unrepeatable read reads committed data, which violates the consistency principle of a transaction.

For example:

Suppose I check my account balance and see that you have tipped xiaobian 1 yuan, I am very happy!! Announce please go to masturbation!! Before the payment, the money was taken by another person, and then checked that there was no money, and was left to wash the dishes!!

After I query, this data is not locked, and it is updated by another transaction, causing the current transaction to read the latest data every time.

3.3 phantom read

With the Repeatable Read isolation level, a transaction may encounter Phantom Read issues.

Transaction A reads several rows that match the search criteria. Transaction B modifies the result set of transaction A by inserting or deleting rows, etc., before committing.

For example:

See, in A transaction A, the first time you query A record, it doesn’t exist, but when you try to update the record that doesn’t exist, it succeeds, and reads the same record again, it magically appears.

In fact, in the InnoDB engine, for index scans, not only locks the scanned index, but also locks the index coverage (gap), so it is not allowed to insert data within this range.

To be continued…