The isolation level of MySQL transactions

1. Transaction concurrency problem

• Dirty read: Transaction A reads the data updated by transaction B (whether or not transaction B commits the data) and then B rolls back the operation, so the data read by TRANSACTION A is dirty.

• Unrepeatable read: Unrepeatable read means that the same batch of data may be read differently at different times within the same transaction and may be affected by other transactions, such as other transactions changing the batch of data and committing. Usually for data UPDATE operations.

• Magic reads: Magic reads are for INSERT operations. Assume that the transaction for some lines for A change, but has not been submitted and the transaction B insert with the transaction before A change of record of the same line, and the transaction is A submit before you submit, and then, in A transaction in A query, will find that just change for some of the data did not play A role, but in fact is the transaction B just inserted in, let the user feel very magical, I feel like I’m hallucinating. It’s called hallucination.

2. Transaction isolation level

3. The case

What is the default isolation level of mysql? The default isolation level is repeatable-read

SELECT @@tx_isolation;
show variables like 'tx_isolation';
Copy the code

Set the mysql transaction isolation level

Set [scope] Transaction isolation level,  SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | The SERIALIZABLE}.Copy the code

Create the test data table

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8


INSERT INTO user VALUES(NULL.'zhang fei'.38);
INSERT INTO user VALUES(NULL.'liu bei'.46);
Copy the code

3.1 Read – Uncommitted

Set the transaction isolation level to read-UNcommitted

SET  GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Copy the code

When the isolation level is read-Uncommitted, INSERT, UPDATE, and DELETE operations are seen when other transactions are not committed

3.2 Read – Committed

Set the transaction isolation level to read-COMMITTED

SET  GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Copy the code

When the isolation level is read- COMMITTED, INSERT, UPDATE, and DELETE operations are not seen when other transactions are not committed. They can be seen only when they are committed

3.3 Repeatable (repeatable-read)

Set the transaction isolation level to repeatable-read

SET  GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Copy the code

The isolation level is repeatable-read. INSERT, UPDATE, DELETE operations are not seen regardless of whether other transactions are committed or not. Note: If a record is modified by the current transaction, then the result of that record being modified by another committed transaction can be seen. If the current transaction is queried using the for update statement, you can see the changes made by other committed transactions

If transaction A and transaction B modify the same record in the same table at the same time, the subsequent transaction may fail to execute. In this case, row lock may occur and the execution may fail. The subsequent transaction must wait for the previous transaction to commit or rollback before modifying the same record.

3.3 Serializable

Set the transaction isolation level to serializable

SET  GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Copy the code

A sequence of transactions. Transactions are executed one after another, waiting for the previous transaction to complete before subsequent transactions can be executed in sequence