Four levels of isolation for MySQL

Class one or four isolation level

The SQL standard defines 4 classes of isolation, including specific rules that define which changes are visible inside and outside the transaction and which are not. The low-level isolation level generally supports higher concurrency processing and has lower system overhead.

Read uncommitted(Reading uncommitted content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used for practical purposes because it does not perform much better than other levels. Reading uncommitted data is also called dirty Read (Dirty read).

Read committed(reading submissions)

This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been committed by the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read) because other instances of the same transaction may have a new commit during the processing of the instance, so the same select may return different results.

repeatable Read(reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same data row when they read the data concurrently. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). In short, phantom reading means that when a user reads a range of data rows, another transaction inserts a new row within that range, and a new phantom row is found when the user reads the range of data rows. The InnoDB and Falcon storage engines address this problem through the multiple versioning concurrency control (mvcc,multiversion concurrency controls) mechanism.

Serializable(Serializable)

This is the highest isolation level, which solves the problem of phantom reading by forcing transactions to be sorted so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a large number of timeout phenomena and lock competition.

Ii. Isolation Levels and consistency

These four isolation levels are implemented with different lock types, which can be easily problematic if the same data is read. For example:

Dirty Read (drity Read): A transaction has updated a data, another transaction at this time read the same data, for some reason, the previous rollback the operation, then the data read by the latter will be incorrect.

Non-repeatable reads: Data is inconsistent among two queries in a transaction, which may be the original data in which a transaction update was inserted in the middle of a two-query process non-repeatable.

Phantom read: The number of data pens is inconsistent in a two-time query of a transaction, for example, a transaction queries several columns of data, while another transaction inserts new columns of data at this time, and the previous transaction finds several columns of data that it previously did not have in the next query.

In MySQL, these four isolation levels are implemented, which can cause problems as follows:

Isolation level	Dirty Read	Do not read repeatedly	Phantom reading
Read UNCOMMITTED	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

Third, set the current isolation level

--Cancel Autocommit

Set autocommit=0

Show variables like "%autocommit%";

--View Isolation Level

SELECT @ @global. tx_isolation;

SELECT @ @session. tx_isolation;

SELECT @ @tx_isolation;

Show variables like '%iso% ';

+---------------+-----------------+

| variable_name | Value |

+---------------+-----------------+

| tx_isolation | Repeatable-read |

+---------------+-----------------+

Show global variables like '%iso% ';

+---------------+-----------------+

| variable_name | Value |

+---------------+-----------------+

| tx_isolation | Repeatable-read |

+---------------+-----------------+

--Set Isolation Level

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;

--Transaction operations

Start transaction;

SELECT * from Text.tx;

Commit

Start transaction;

SELECT * from Text.tx;

Update text.tx set num =10 where id = 1;

Insert into TEXT.TX (id,num) values (9,9);

Rollback

Commit

Four, MY.CNF set

# MySQL supports 4 transaction isolation levels, respectively:

# read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE.

# if not specified, MySQL defaults to repeatable-read,oracle by default is read-committed

Transaction_isolation = Repeatable-read