Shulou(Shulou.com)06/01 Report--

Class IV isolation levels

  The SQL standard defines four levels of isolation, including specific rules that define which changes inside and outside a transaction are visible and which are invisible. Lower isolation levels generally support higher concurrency and have lower overhead.

Read Uncommitted

  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 its performance is not much better than other levels. Reading uncommitted data is also known as dirty reads.

Examples:

  The company paid the salary and transferred 50000 yuan to my account, but the transaction was not submitted, and I happened to check the account and found that the salary had been received, which was 50000 yuan. I was very happy. Unfortunately, however, the leader found that the amount of salary paid was not correct, it was 2000 yuan, so he quickly rolled back the transaction, revised the amount, and submitted the transaction. Finally, my actual salary was only 2000 yuan, which was an empty joy.

  Dirty reads are two concurrent transactions,"Transaction A: Leader Payroll" and "Transaction B: I Query Payroll Account." Transaction B reads data that transaction A has not yet committed.

  When the isolation level is set to Read uncommitted, dirty reads may occur. How to avoid dirty reads, please see the next isolation level.

Read Submitted

  This is the default isolation level for most database systems (but not MySQL). It satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports so-called nonrepeatable reads, because other instances of the same transaction may have new commits in between, so the same select may return different results.

Examples:

  I took the salary card to spend, the system read that there is indeed 2000 yuan in the card, and at this time my wife also happens to be online transfer, the salary card of 2000 yuan transferred to her account, and submitted the business before me, when I deducted, the system checked that the salary card has no money, deduction failed, very puzzled, obviously there is money in the card, why…

  Non-repeatable reads are two concurrent transactions,"Transaction A: Consumption" and "Transaction B: Wife Online Transfer." Transaction A reads data in advance, transaction B updates data immediately and submits transactions, and when transaction A reads the data again, the data has changed.

  When the isolation level is set to Read committed, dirty reads are avoided, but non-repeatable reads may result.

Repeatable Read

  This is MySQL's default transaction isolation level, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. In theory, though, this leads to another tricky problem: Phantom Read. In simple terms, phantom reading means that when a user reads a range of data rows, another transaction inserts new rows into the range, and when the user reads the range of data rows again, he will find new "phantom" rows. InnoDB and Falcon storage engines solve this problem through the Multiversion Concurrency Control (MVCC) mechanism.

Examples:

  When the isolation level is set to Repeatable reads, non-repeatable reads can be avoided. When I take my paycard to spend, once the system starts reading the paycard information (i.e., the transaction starts), my wife cannot modify the record, that is, cannot transfer money at this time. How much does it cost to have an abortion? http://mobile.chnk120.com/

  Although Repeatable reads avoid non-repeatable reads, phantom reads are possible. For example: My wife works in the banking department and she often checks my credit card records through the bank's internal system. One day, she was looking up the total amount of my credit card for the month.(select sum(amount) from transaction where month = this month) is 80 yuan, and I happen to be paying at the cashier after eating a big meal outside, spending 1000 yuan, that is, adding a new consumption record of 1000 yuan (insert transaction...), and submitted the transaction, then my wife will print the details of my credit card consumption for the month to A4 paper, but found that the total consumption was 1080 yuan, my wife was very surprised, thought there was an illusion, and the illusion reading was produced.

Serializable

  This is the highest level of isolation and solves the phantom read problem by forcing transactions to be ordered so that they are unlikely to conflict with each other. In short, it places a shared lock on each row of data read. At this level, a lot of timeouts and lock contention can result.

Isolation levels and consistency

  These four isolation levels are implemented with different lock types, and problems are likely to occur if the same data is read. For example:

   Dirty Read: A transaction has updated a piece of data, another transaction reads the same piece of data at the same time, and for some reason, the previous RollBack operation, the data read by the latter transaction will be incorrect.

   Non-repeatable read: The data is inconsistent between two queries of a transaction, which may be the original data updated by a transaction inserted between the two queries.

   Phantom Read: In two queries of a transaction, the number of data entries is inconsistent, for example, one transaction queries several rows of data, while the other transaction inserts new columns of data at this time, and the previous transaction will find several columns of data that it did not have before in the next query.

In MySQL, these four isolation levels are implemented, and each of them may cause problems as follows:

isolation level dirty read non-repeatable read phantom read

Read Uncommitted √ √

Read Submitted × √ √

Repeatable Read × × √

Serializable × × ×

Summary:

Serializable: Avoid dirty reads, unrepeatable reads, and phantom reads.

Repeatable read: Avoid dirty and non-repeatable reads.

Read committed: Avoid dirty reads.

Read uncommitted: The lowest level, which cannot be guaranteed under any circumstances.

  The highest of the above four isolation levels is Serializable level, and the lowest is Read uncommitted level. The higher the level, the lower the execution efficiency. A level such as 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 should be based on the actual situation. The default isolation level in MySQL databases is Repeatable read.

  In MySQL database, the above four isolation levels are supported, and the default is Repeatable read; in Oracle database, only Serializable level and Read committed level are supported, and the default is Read committed level.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.