About the ACID properties of the database has been a lot of introduction, here again summed up: A (atomicity) atomicity: That is, the transaction is either complete or not, do not appear only to do part of the situation, such as a to B transfer, do not appear a less money, B of the money did not increase the situation C ( Consistency) Consistency: Refers to the transaction from one state to another state is consistent, as a reduced 100,b cannot increase only 30. I (Isolation) Isolation: That is, a transaction is not visible to other transactions when the commit modification of the data is not completed. There is, of course, an isolation level concept where there are different forms of expression D (durability) persistence at different isolation levels: once a transaction commits, the changes are persisted to the database. Before we say the isolation level, let's talk about the following concepts: Dirty reads: A transaction adds and deletions data, but it does not commit, but the other transaction reads that uncommitted data cannot be read repeatedly: a transactionUpdate or deleteoperation, data inconsistency for another transaction two queries the Phantom reads: A transaction has been performed on the dataNew Action, the result of another transaction two queries is inconsistent. We see that non-repeatable reading is similar to Phantom reading, but in fact they are very different,non-repeatable reads are mainly reflected in update and delete, while Phantom reads are mainly reflected in the insert, from the implementation level, to solve the non-repeatable reading, we only need to lock the data of the query can be implemented, at this time, the update and delete these lines will block the wait, but the insert can still, avoid the Phantom read, and to solve the phantom read, must be the row and line before the lock, In MySQL, it is done by next key lock (row lock +gap Lock). Isolation level: Read uncommited unread: The isolation level is 0, there will be dirty read, non-repeatable read, Phantom Read commit read: Isolation level is 1, no dirty read, but there is non-repeatable read, Phantom read REPEATABLE read REPEATABLE READ: Isolation Level 2, there will be no Dirty reading, not repeated reading, but there will still be phantom reading. But why does the repeatable read in MySQL solve the phantom read? It would have been a phantom reading, butit uses the next key lock plus the for update to avoid, InnoDB provides a mechanism for obtaining the most up-to-date data through lock-up queries, such as two transactions at the same time, a transaction inserting a data, and committing, b transaction to check the select * from T, at which point B is unable to query the data submitted by a transaction, but add a for Update, which is the SELECT * from-t for update, is able to find the data that has just been inserted into a transaction. That's why we're saying that MySQL can avoid the reason of phantom reading under repeatable Read isolation level, remember to add for update. Serializable serializable: Isolation Level 3, no dirty read, non-repeatable read, Phantom read. But the least efficient, the worst concurrency, and generally not used.
MySQL acid and four isolation levels summary
