MySQL Transaction ISOLATION LEVEL

MySQL's Lock

1. Shared lock, which is read lock, can be forced by select ... lock in share mode, the default SELECT statement is unlocked

2. Exclusive lock, that is, write lock, you can force write lock by select ... for UPDATE, INSERT, UPDATE, DELETE statement write lock by default

Size of MySQL Lock

1. Row lock, lock line, high concurrency (because of the conflict), low overhead, will generate a deadlock, InnoDB engine

2. Table lock, lock full table, concurrency low (because of conflict), overhead, will not produce deadlocks, MyISAM, memory engine, so MyISAM no transaction, also does not have a database deadlock, do a lock on the required resources before executing SQL, However, MyISAM may be unable to obtain a read lock due to a large number of table-level write locks to block read

3. Page lock, a tradeoff between Hang and table locks, locks a batch of adjacent records, in the concurrency and overhead between row-level locks and table locks, also produces deadlocks, BDB database

InnoDB Storage Engine

Because InnoDB has MVCC, so select ... is not locked, directly read the latest version of the data

The SELECT statement forces the lock to be locked when it is specified, as well as insert, update, and lock when the delete is added.

Transaction ISOLATION LEVEL

The transaction isolation level can be understood as describing the level of locking of data at the same time as multiple transactions, a tradeoff between concurrency and cross-transaction performance, and a total of four transaction isolation boundaries in standard SQL

Four isolation levels there are almost no use in two projects

Read uncommited, uncommitted reading

Memory from the name is, the transaction has not been submitted, other transactions in the observation of the data changes, no one want to use this, for others have not yet submitted the phenomenon of reading is called Dirty Read

Serialiazble, serialization

Read and write are conflicting, so it becomes serial, no concurrency, and no one uses it.

There are also two isolation levels, which are commonly used

Read commited, submitted reads, referred to as RC isolation level

This level solves the problem of read uncommited dirty reads, reads the data to read the lock, reads the immediately release, writes the data to write the lock, until the transaction end writes the lock to release. This will not produce dirty reads. However, it will result in a transaction that reads two read data inconsistencies before a write lock is locked and is called non-repeatable read

REPEATABLE read, Repeatable read, abbreviated RR isolation level (MySQL default transaction isolation level)

This level solves the problem that read commited two reading data may be inconsistent, and the solution is to read the read lock until the end of the transaction is released, write the write lock until the end of the transaction is released. This would not result in inconsistent data for two reads, since the release time of the read lock was increased, guaranteeing that no one could change the data at the time of reading.

If the RR isolation level is implemented through a simple row-level lock mechanism, there is a problem when data is inserted, such as transaction 1 doing a scope query, but transaction 2 inserts a piece of data within the transaction, commits the transaction 1 and then queries the scope query back to the transaction 2 new data, This is also a two-way reading data inconsistency, but this is not called non-repeatable read, called Phantom Reading.

MySQL in the RR isolation level can be solved by gap lock to solve the Phantom read, that is, when the row lock is not only a row lock, but according to the index, the adjacent index interval is locked, so that in the adjacent interval when the insert, it is necessary to wait, the size of the gap can finally become a lock full table

MySQL transaction isolation level