MySQL Transaction ISOLATION LEVEL

Content one: MySQL's transaction isolation level has the following four kinds:

READ UNCOMMITTED (UNCOMMITTED)

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

Read Committed (submit reading)

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 (Repeatable Read)

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.

Content two: Dirty read non-repeatable read Magic Read

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): the number of data pens in a two-time query of a transaction is inconsistent, for example, one transaction queried several columns (row) data, while another transaction inserted a new column of data at this point, the previous transaction in the next query, You will find a few columns of data that were not previously available.

Content three: row-level lock

MySQL row-level locks have shared locks and exclusive locks

SELECT ... Lock in SHARE mode sets a GKFX mode lock on any rows that is read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of the these rows were changed by another transaction that have not yet committed, your query waits until that transacti On ends and then uses the latest values.

SELECT ... for UPDATE sets a exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing.

Lock in SHARE MODE adds a shared lock on the read line, the other session can only be read and cannot be modified or deleted, and if another transaction modifies the record, it waits for the transaction to be committed before it is read.

The for UPDATE sets an exclusive lock on the read line. Prevent other sessions from reading or writing row data

This may seem to explain why the lock in SHARE MODE creates a deadlock, and if two transactions A and B all read the same row, then a shared lock is added to the line, but the A and B transactions need to be modified, so all wait for the other party to release the shared lock, resulting in Deadlock.

These are the differences between two row-level locks.

MySQL Transaction ISOLATION LEVEL