Interviewer: What is ACID in business?

A, the transaction

A transaction is a logical unit of DBMS execution consisting of a finite sequence of database operations. — From Encyclopedia

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 transition 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

  

• Persistence: Changes made to the database by committed transactions should be permanently stored in the database.

Iii. Isolation level

Having 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:

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.

Mysql simulates transaction isolation tests

SELECT @@session.tx_isolation;   
SELECT @@tx_isolation; SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; SET SESSION TRANSACTION ISOLATION LEVEL read committed; SET SESSION TRANSACTION ISOLATION LEVEL repeatable read; SET SESSION TRANSACTION ISOLATION LEVEL serializable; start transaction; Drop table AMOUNT; CREATE TABLE `AMOUNT` ( `id` varchar(10) NULL, `money` numeric NULL ) ; Insert datainsert into amount(id,money) values('A'.800);
insert into amount(id,money) values('B'.200);
insert into amount(id,money) values('C'.1000); Test repeatable read, insert datainsert into amount(id,money) values('D'.1000); -- SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; SELECT @@tx_isolation; -- Start transaction; -- Dirty read demo, read data not committed by other transactions -- case column1Transaction 1: A turns to B200Update amount set money = money -200 where id = 'A';
update amount set money = money + 200 where id = 'B'; -- Unrepeatable read demo, read data submitted by other transactions -- case column2Transaction 1: B goes to A200Transaction 2: B goes to C200turn100SET SESSION TRANSACTION ISOLATION LEVEL read committed; -- Start transaction; Select * from amount; select * from amount; Transaction 1: B goes to A200
update amount set money = money - 200 where id = 'B';
update amount set money = money + 200 where id = 'A'; commit; Transaction 2: B goes to C200turn100
update amount set money = money - 100 where id = 'B';
update amount set money = money + 100 where id = 'C'; commit; From transaction 2's point of view, read transaction 1 commits transaction data, resulting in negative value3Transaction 1: B goes to A200Transaction 2: B goes to C200turn100SET SESSION TRANSACTION ISOLATION LEVEL repeatable read; -- Start transaction; Select * from amount; select * from amount; Transaction 1: B goes to A200
update amount set money = money - 200 where id = 'B';
update amount set money = money + 200 where id = 'A'; commit; Transaction 2: B goes to C200turn100
update amount set money = money - 100 where id = 'B';
update amount set money = money + 100 where id = 'C'; commit; -- From transaction 2's point of view, read transaction 1's commit transaction data, resulting in a negative amountCopy the code