Four isolation levels for SQL transactions and MySQL multi-version concurrency control

The SQL standard defines class 4 isolation levels, including specific rules that can be used to limit the visibility of changes in and out of transactions, which are not visible. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.

readuncommitted (Read UNCOMMITTED content)

At this isolation level, all transactions can see the execution structure of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. READ UNCOMMITTED data, also known as dirty Read (Dirty Read)

ReadCommitted (Read 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 submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have new commits during the instance processing, so the same select may return different results.

RepeatableRead (can be reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). Simply put, the Phantom read worth when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row for that range again, it returns to the existing new Phantom row. The INNDB and Falcon storage engines address this issue through a multi-version concurrency control (MVCC) mechanism.

Serializable (Serializable)

This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with each other. In short, it adds a shared lock on each read row. At this level, a large number of timeouts and lock competitions can result.

These four isolation levels are implemented with different lock types, which are prone to problems if the same data is read. For example:

Dirty Reads (drity read):

A transaction has updated one copy of the data, another transaction reads the same sub-data at this time, for some reason, the previous rooback operation, the latter will read the data is not correct.

Non-repeatable read (non-repeatable Read):

The data is inconsistent among the two queries in a transaction, which may be the original data for a transaction update inserted between the two query processes.

Phantom Read (Phantom Read):

In a transaction two times the number of data bars inconsistent, for example, one transaction query a few rows of data, and another transaction at this time insert a new row of data, the previous transaction in the next query, you will find a few rows of data is what it had not previously.

These four isolation levels are implemented in MySQL, each of which may have the following problems:

Isolation level	Dirty Read	Non-REPEATABLE READ	Phantom reading
Unread (Read UNCOMMITTED)	Yes	Yes	Yes
Read submitted (committed)	No	Yes	Yes
REPEATABLE READ (Repeatable Read)	No	No	Yes
Serializable (Serializable)	No	No	No

To query the current transaction ISOLATION level:

SELECT @ @tx_isolation;

To set the transaction isolation level:

SET tx_isolation = ';

To open a transaction:

START transaction;

Commit TRANSACTION:

Commit

To roll back a transaction:

Rollback

Multi-version concurrency control (multiversion concurrencycontroll MVCC)

1th:

MVCC is not unique to MySQL, oracle,postgresql and so on are used.

MVCC does not simply use row locks, but instead uses row-level locks (row-level locking). The basic principles of MVCC are:

Keeping a snapshot of the data in a transaction means that you can see a consistent view of the data in one thing, without worrying about how long the transaction will run, and also means that the data in the same table as seen by different transactions at the same time may be different.

Basic features of MVCC:

· Each row of data has a version that is updated every time the data is updated

· Copy out the current version at random modification, no interference between transactions

· Compare version number on save, overwrite original record if successful (commit), failure to discard copy (rollback)

InnoDB Storage Engine MVCC implementation strategy:

Save two extra hidden fields in each row of data: The version number at the time the current row was created and the version number when it was deleted (possibly empty). Each transaction has its own version number, so that the data versioning is achieved by comparing version numbers when performing CRUD operations within a transaction. See below for specific practices.

---------------------------------------------------------

The public number "Programmer's Road", the road long its repair far XI, I will go up and down and quest!

Gently hold down the following it, let us together, grow!

Four isolation levels for SQL transactions and MySQL multi-version concurrency control