MYSQL Isolation Level

"MYSQL Isolation Level "

1. Modify the syntax of the isolation level:
　　

Note: The default behavior (without session and global) is to set the isolation level for the next (not started) transaction. If you use the Global keyword, the statement sets the default transaction level globally for all new connections created from that point, except for connections that do not exist. You need super privilege to do this. Use the session keyword to set the default transaction level for future transactions performed on the current connection. Any client can freely change the session isolation level (even in the middle of a transaction) or set the isolation level for the next transaction.

You can query the global and session transaction isolation levels with the following statement:

　　

Reference: http://blog.itpub.net/195110/viewspace-1080777/

2. Details of isolation level.

The SQL standard defines a Class 4 isolation level, which includes specific rules to define which changes within and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.
READ UNCOMMITTED (Read UNCOMMITTED content)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).
Read Committed (read submit content)

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.
Repeatable Read (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). To put it simply, Phantom reads 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 of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) 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 one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

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

Dirty Reads (drity read): A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the latter will read the data is not correct.

Non-repeatable read (non-repeatable Read): Data inconsistency in two queries for a transaction, which may be the original data that was inserted in the middle of a transaction update during the two query process.

Phantom Read (Phantom Read): In a transaction two times the number of data pens inconsistent, for example, one transaction queried several columns (row) of data, while another transaction at this time inserted a new column of data, the previous transaction in the next query, you will find that there are a few columns of data that it did not previously.

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

　　

Reference: http://xm-king.iteye.com/blog/770721

MYSQL Isolation Level