Mysql transaction,

Mysql transaction,
Database Transaction Features: acida: atomicity. A single operation either succeeds or fails to be rolled back. (Atomicity) c: consistency. data must be consistent before and after execution. (Consistency) [For transfer: for example, before an account transaction is executed and before it is 500, it must be ensured that it is 500] I: isolation, and multiple concurrent transactions are isolated from each other, do not interfere with each other. (Isolation) [Transaction a is executed either before or after transaction B is executed] provides several isolation levels d: permanent, and changes to the database after the transaction is executed are permanent. (Durability) Dirty read: one transaction reads the uncommitted data of another transaction. Unrepeatable read: The results of multiple reads from the same data in a transaction are inconsistent. (During the query interval, other transactions change data and commit transactions.) The difference between repeated reads and dirty reads is that a transaction reads dirty data not committed by another transaction, the non-repeated read reads the data committed by the previous transaction. Phantom read: occurs when a transaction is not executed independently. For example, if transaction a changes the name of all data in the database to Michael Jacob, transaction B inserts a new data name, which is Li Si and is committed, the user of transaction a re-queries the data and finds that another one (Added by transaction B) is not updated, just like an illusion, that is, phantom reading occurs. Both phantom read and non-repeated read another committed transaction (which is different from dirty read). what is different is that non-repeated read queries are the same data item, phantom read targets a batch of data (such as the number of data ). MySQL database provides four isolation levels: ① Serializable (Serializable): This prevents dirty reads, non-repeated reads, and Phantom reads. ② Repeatable read: This prevents dirty reads and repeated reads. ③ Read committed (Read committed): prevents dirty reads. ④ Read uncommitted (Read uncommitted): the lowest level, which cannot be guaranteed in any situation. Among the above four isolation levels, the highest is the Serializable level, and the lowest is the Read uncommitted level. Of course, the higher the level, the lower the execution efficiency. The level like Serializable is to lock the table (similar to the lock in Java multithreading) so that other threads can only wait outside the lock, so what isolation level should be selected at ordinary times according to the actual situation. In MySQL databases, the default isolation level is Repeatable read (Repeatable read ). View the isolation level of the current transaction in the MySQL database: select @ tx_isolation; set the isolation level of the transaction in the MySQL database: set tx_isolation = 'isolation level name; 'Remember: set the database isolation level before starting the transaction!