MySQL transaction isolation level there are four, respectively is read uncommitted, read committed and repeatable read and serializable,
MySQL isolation level role is to make the transaction between isolated from each other, each other, so that we can guarantee the consistency of the transaction,
Isolation level comparison: serializable & gt; Repeatable read & gt; Read committed & gt; Read uncommitted
Isolation level the performance impact of comparison: serializable & gt; Repeatable read & gt; Read committed & gt; Read uncommitted
READ UNCOMMITTED (READ UNCOMMITTED) : READ UNCOMMITTED isolation level, can READ A to modified B but UNCOMMITTED data (the database may be dirty READ isolation level, not repeatable READ and illusions, general rarely use this isolation level,)
Dirty Read (Dirty Read) : a another uncommitted Read things that the modified data,
Example: the New Year, the boss ready to each employee to the company's bonus, the boss ready to send 2000 yuan of bonus to xiao gang but due to the input error input is 5000 yuan, but has not been submitted to transfer, then small just good-looking myself this year's bonuses for 5000 at happy is broken, hurriedly to share with the friend xiao Ming also decided to xiao Ming, please drink milk tea from work, after work the boss announced all bonuses were sent the user card, xiao gang hurriedly open a cellular phone a look at the card payment of RMB 2000, suddenly a bit lost,
READ submitted (READ COMMITTED) : reading submitted under the isolation level, B only in A modified and submitted before they can READ to modify data, transaction B (solved the problem of the dirty reads, but may be not repeatable READ and illusions, general rarely use this isolation level,)
Phantom read (Phantom) : a transaction to query some records, according to certain conditions after another transaction records to insert a table in accordance with these conditions, the original transaction in accordance with the conditions of the query again, can insert another transaction records are read out, (Phantom reads the uncommitted read, read committed, repeatable read isolation level may be)
Example: xiao gang and xiao Ming to milk tea shop, please drink the tea with milk, but due to the bonus than xiao Ming, the results of 23 yuan xiao Ming bought a single to xiao gang, xiao Ming went back home and his wife that he sent the salary and bonus a total of 34210 yuan, and my good friend xiao gang to drink tea with milk just spent 23 yuan 34187 yuan, the left and put the bank card consumption record to the wife, is not thinking clearly when xiao gang said to xiao Ming, please drink milk tea, then decided to send money to xiao Ming, but send WeChat messages he don't accept pay treasure to transfer directly as a result, when xiao Ming wife see bill suddenly one more transfer payment in RMB 23,
Not Repeatable Read: (Non - Repeatable Read) : a transaction can only be Read another has submitted the affairs of the modified data, and other transactions every time the data is modified and submitted, the transaction can query to get the latest value, (not Repeatable Read Read uncommitted and committed isolation level may be)
REPEATABLE READ REPEATABLE READ) : under the REPEATABLE READ isolation level, transaction B only after the transaction is A modified data and submit themselves to commit the transaction, can READ to modify data, transaction B
SERIALIZABLE (serialization) (SERIALIZABLE) : all sorts of problems (dirty read, not repeatable read, phantom reads) will not happen, by locking (read locks and write locks),
Conclusion:
Serializable (serialization) : can avoid dirty reads, non-repeatable reads, the occurrence of phantom read,
, Repeatable read (Repeatable read) : can avoid dirty read, not Repeatable read,
, the Read committed Read (submitted) : can avoid the happening of dirty reads,
, the Read uncommitted Read uncommitted () : the lowest level, cannot assure any situation,
Above four isolation level is the highest level of Serializable, the lowest is the Read uncommitted level, the higher the level, the lower execution efficiency,