Detailed description of transaction isolation level

Detailed description of transaction isolation level the SQL standard defines four types of isolation levels, including some specific rules to limit which changes inside and outside the transaction are visible and which are invisible. Low-level Isolation generally supports higher concurrent processing and lower system overhead. Read Uncommitted (Read Uncommitted content) at this isolation level, all transactions can see the execution results of other Uncommitted transactions. This isolation level is rarely used in practical applications, because it has better performance than other levels, and is implemented through exclusive write locks. Read uncommitted data, also known as Dirty Read ). Www.2cto.com Read Committed (Read Committed content) is the default isolation level for most database systems (but not for MySQL ). It satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions. "Instant shared read lock" and "exclusive write lock" are implemented. This isolation level also supports the so-called Nonrepeatable Read, because the instance of the same transaction may have a new commit during the processing of the instance, so the same select may return different results. Repeatable Read (repeable) is the default transaction isolation level of MySQL. It ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. "Shared read lock" and "exclusive write lock" are implemented. However, theoretically, this will lead to another tricky problem: Phantom Read ). In short, phantom read refers to when a user reads data in a certain range, another transaction inserts (or deletes) a new row in this range, when the user reads data in this range again, a new Phantom line is found. The InnoDB and Falcon storage engines solve this problem through the Multi-version Concurrency Control (MVCC, Multiversion Concurrency Control) mechanism. Serializable (Serializable) www.2cto.com is the highest isolation level. It forces transaction sorting to make it impossible to conflict with each other, thus solving the phantom read problem. In short, it adds a shared lock to each read data row. At this level, there may be a lot of timeout and lock competition. The four isolation levels adopt different lock types. If the same data is read, problems may occur. For example, Drity Read: a transaction has updated a copy of data, and another transaction has Read the same copy of data at this time. For some reason, the previous RollBack operation is performed, the data read by the other transaction is incorrect. Non-repeatable read: the data in the two queries of a transaction is inconsistent. This may be because the original data updated by a transaction is inserted in the two queries. Www.2cto.com Phantom Read: the number of data records in two queries of a transaction is inconsistent (the total number of data records is different in two queries ), for example, a transaction queries several columns of data, while another transaction inserts several new columns of data at this time. The previous transaction is in the next query, you will find that there are several columns of data that are not included before. In MySQL, these four isolation levels are implemented, which may cause the following problems: