Presentation of data at different transaction isolation levels in mysql

Transaction Concept

A transaction is an atomic SQL query statement and can be considered as a unit of work. If the database engine can successfully apply all query statements to the database, it will execute all queries. If any query statement cannot be executed due to crashes or other causes, all statements are not executed. That is to say, the statements in the transaction are either fully executed or one sentence is not executed.

Transaction Features: acid, also known as four tests of transactions (atomicity, consistency, isolation, persistence) automicity: database operations caused by atomicity and transactions are either completed, or do not execute
Consisitency: consistency. The total number before the transaction execution and the total number after the transaction execution remain unchanged.
Isolation: isolation. The result of a transaction is visible to other transactions only after completion.
Durability: persistence. Once the transaction is successfully completed, the system must ensure that no fault will cause transaction inconsistency.

Transaction Status:
Activity
Partially submitted
Failed
Abort
Submit

A transaction must be in one of the top five states at a certain time. The conversion between the statuses of the transaction is as follows:

650) this. length = 650; "src ="/e/u/themes/default/images/spacer.gif "style =" url (/e/u/lang/zh-cn/images/localimage.png) no-repeat center; border: 1px solid # ddd "word_img =" file: // C:/Users/ADMINI ~ 1/AppData/Local/Temp/enhtmlclip/%E5%9B%BE%E5%83%8F%203.jpg "/> 650) this. width = 650;" src = "http://www.bkjia.com/uploads/allimg/131228/22293I421-0.jpg" title = "image 3.jpg"/>

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 a transaction is inserted to update the original data during the two queries.

Phantom Read: the data in two queries of a transaction is inconsistent. For example, a transaction queries several rows of data, another transaction inserts several new columns of data at this time. In the subsequent query, the previous transaction will find that several columns of data are not at the beginning.

Concurrency Control
Multi-version concurrency control: Multiversion concurrency control, MVCC
Each user operates on data as a time snapshot of the source data. After the user completes the operation, the lock is merged to the source data based on the time points of each snapshot: To achieve concurrency control, the simplest implementation mechanism is the lock (MVCC does not adopt the lock mechanism ). Read lock: A Shared lock that is added by a read Table operation. After the lock is applied, other users can only obtain the shared lock of the table or row, but cannot obtain the exclusive lock. That is to say, they can only read and write locks: exclusive locks, the lock added by the write table operation. After the lock is applied, other users cannot obtain any lock granularity of the table or row: from large to small, MySQL Server only supports table-level locks, the row lock must be completed by the storage engine. Table lock: Lock a table
Page lock: Lock a page
Row lock: Lock a row

The finer the granularity, the better the concurrency. That is, row locks have the best concurrency, but they must be supported by the storage engine.

Read uncommitted (read uncommitted): allows dirty reads, that is, reads from uncommitted transaction changes in other sessions (read committed ): only submitted data can be read. Most databases such as oracle repeatable read are repeatable read by default. queries in the same transaction are consistent at the start time of the transaction, and the default innodb level. In the SQL standard, this isolation level eliminates non-repeated reads, but there is still an illusion that the read can be serializable: Fully serialized read, each read needs to obtain a table-level shared lock, reading and writing are blocked in MySQL. In the case of concurrency control, different isolation levels may cause the following problems: 650) this. length = 650; "src ="/e/u/themes/default/images/spacer.gif "style =" url (/e/u/lang/zh-cn/images/localimage.png) no-repeat center; border: 1px solid # ddd "word_img =" file: // C:/Users/ADMINI ~ 1/AppData/Local/Temp/enhtmlclip/%E5%9B%BE%E5%83%8F%202.jpg "/> 650) this. width = 650;" src = "http://www.bkjia.com/uploads/allimg/131228/22293H516-1.jpg" title = "image 2.jpg"/>

The reason for the introduction of so many theoretical knowledge above is to facilitate understanding. The table above has already been listed. The data display performance may be affected at different isolation levels. Now mysql is installed on linux, let's take a look at the data display effects at different isolation levels through our experiments.

Experimental environment: linux: RedHat 5.8linux kernel: linux-2.6.18-308.el5mysql version: mysql-5.6.10-linux-glibc2.5-i686

All operations in this experiment are completed in the virtual machine, connect to the virtual machine through Xmanager, then enable two Session connections, change the isolation level in both sessions, and then view the data display effect.

In this experiment, mysql is installed through source code compilation and installation. You can also directly install mysql using the rpm package. The method and process of installing the source code are not demonstrated here. I have already introduced it many times in the previous blog. If you use the source code compilation and installation method and do not know how to install mysql, you can refer to my previous blog, which is described in. Use the source code compilation and installation method. In the mysql configuration file, it is best to enable a tablespace for each table. Here we enable it directly. Because it is an experiment, no password is set for mysql, So we directly use the command to enter mysql. Command and display effect:

[Root @ mysql ~] # Mysql-uroot-p # Use this command to Enter mysql. Because no password is set, press the Enter key to Enter password: Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 2 Server version: 5.6.10 MySQL Community Server (GPL) Copyright (c) 2000,201 3, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their r Espectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> show variables like '% iso %'; # Check the default transaction isolation level of mysql. The default value is repeat. You can also use the select @ tx_isolation command to view + rows + ---------------- + | Variable_name | Value | + rows + ------------------ + | tx_isolation | REPEATABLE-READ | + rows + ---------------- + 1 row in set (0.36 sec) mysql> show databases; # view existing databases in the system + --------------------- + | Database | + --------------------- + | information_schema | mysql | performance_schema | test | + ------------------ --- + 4 rows in set (0.00 sec) is now imported to the database used in our experiment. [Root @ mysql ~] # Mysql <jiaowu. SQL # import the jiaowu database used in the experiment [root @ mysql ~] # Mysql-uroot-p Enter password: Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 7 Server version: 5.6.10 MySQL Community Server (GPL) Copyright (c) 2000,201 3, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. mysql> show databases; # Check whether the imported jiaowu Database exists + ---------------------- + | Database | + -------------------- + | information_schema | jiaowu | mysql | cece_schema | test | + tables + 5 rows in set (0.01 sec)

In the mysql command interface, when transactions are not explicitly enabled, each command is directly submitted because mysql has a variable value that can be automatically submitted. That is to say, each input statement will be automatically submitted, which will generate a large number of disk IO and reduce system performance. We recommend that you disable the automatic commit function because we need to explicitly use transactions during our experiments. It does not matter if it is not disabled, but if you do not explicitly use transactions, to do the following experiment, you need to disable this function. Here, we use transactions explicitly and disable the automatic commit function. If you disable the automatic commit function, you must use the transaction explicitly. Otherwise, all the statements you enter will be processed as a transaction. The command is as follows:

Mysql> select @ autocommit; # view this value, if it is set to 1, automatic submission is started + -------------- + | @ autocommit | + -------------- + | 1 | + -------------- + 1 row in set (0.00 sec) mysql> set autocommit = 0; # disable the automatic submission function Query OK, 0 rows affected (0.00 sec) mysql> select @ autocommit; # view this value again, 0 indicates that the automatic submission function is disabled + -------------- + | @ autocommit | + -------------- + | 0 | + -------------- + 1 row in set (0.00 sec)

Now we open two sessions and enter mysql respectively in these two sessions. First, remember to modify the autocommit variable in the two calls, disable the automatic commit function, and then check the transaction isolation level, the default value is REPEATABLE-READ. You must modify the isolation level in both sessions. We will first start the demonstration from the lowest isolation level.

Mysql> select @ tx_isolation; + ----------------- + | @ tx_isolation | + --------------------- + | REPEATABLE-READ | + ----------------- + 1 row in set (0.00 sec) mysql> set tx_isolation = 'read-uncommitted'; # modify the isolation level to repeat the isolation level and change it to read not submitted Query OK, 0 rows affected (0.00 sec) mysql> select @ tx_isolation; + ----------------- + | @ tx_isolation | + ------------------- + | READ-UNCOMMITTED | + comment + after modifying the isolation level Use the imported database and the tutors table to verify the display effect. Mysql> use jiaowu; # use the jaiowu database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql> show tables; # View tables in the database + ------------------- + | tables | + ------------------- + | courses | scores | students | tutors | + ------------------- + 4 rows in set (0.00 sec)

In session 1, we modify the data in tutors. In Session 2, we can view the data to see what the situation will be. I plan to use the tutors table to demonstrate the following experiment, but it is troublesome to modify the data. Therefore, I wrote a script to create a new table named teachers for each field in the tutors table. Script writing is a little poor. If you are interested, you can write a better script to create and insert data. The script for creating a table and inserting data is as follows:

#! /Bin/bash # Author: hulunbeier, creating table and inserting data # let B = 0 mysql-e "use jiaowu; create table teachers like tutors; "read-p" Input a number to create NUMBER data. you choice: "NUM # execute this script. It will input a number. Because it is an experiment, we will insert 5 rows of data here, you can modify for I in 'seq 1 $ num' on your own '; do NAME = tech $ I A = 'echo $ RANDOM/365 | bc 'until [$ A-ge 40] & [$ A-le 100]; do A = 'echo $ RANDOM/365 | bc 'done B = 'echo $ RANDOM % 2 | bc' if [$ B = 0]; then GD = F else GD = M fi mysql-e "insert into jiaowu. teachers (Tname, Gender, Age) values ('$ name',' $ GD ', $ A); "echo" create tech $ I success. "done

Execute the above script to create the corresponding table and insert data. Check whether the new table we created is successful and whether there is data in it. The query command and display result are as follows:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22293H536-2.jpg "title =" image 4.jpg"/>

The created table already exists and data is inserted successfully. Now we use the teachers table to demonstrate the following experiments. The demonstration experiment starts from the low isolation level to the high isolation level.

Read-uncommitted: Read uncommitted

First, modify the automatic submission function of the two sessions, disable it, And then modify the default isolation level of the system. From the low level, change the default repeatable level to read uncommitted.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22293I0L-3.jpg "title =" image 5.jpg"/>

After the preceding modifications are completed in two sessions, we start the transaction and query all the data in the table below. The age of the teacher whose TID is 5 is 61, then, in session 1, update the age of the teacher whose TID is 5 in the teachers table, change the original 61 to 50, and then re-query all the data in the two sessions, check the age of the teacher whose TID is 5. The command and display effect are as follows:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22293MG9-4.jpg "title =" image 6.jpg"/>

When we use rollback in session 1, we can check the data in two sessions and find that it is still 61. From the Display Results of the preceding two sessions, we can see that when the isolation level is read not committed, when we start a transaction, the information of a Data row is modified in the transaction, the transaction is not committed, but in another transaction, if we operate on the same dataset, we will find that the results of the two queries are different, in the same transaction, the results of the two queries are different. This situation is not allowed. In this case, dirty reads, non-repeated reads, and Phantom reads occur.

Read-committed: Read commit first modifies the isolation level based on the preceding modification, and changes uncommitted Read to Read commit. Then, we will change the age of the teacher whose TID is 5 to 40. Then, view the display effect again in the two sessions. Command and display effect: 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22293K153-5.jpg "style =" cursor: default; "title =" image 7.jpg"/>

Now, in session 1, we use the commit command to submit the transaction, and then in session 2, we can check the display effect. In Session 2, the age of the teacher whose TID is 5 is 40. When the isolation level is read-committed, we can see from the display above that when we start the transaction in session 1, when a row of data is modified, the modified result is displayed in session 1, but the modified result is not displayed in session 2. When we submit a transaction in session 1 and query it in session 2, we will find that the result is different from the one we last queried. At this isolation level, although dirty reads can be avoided, non-repeated and Phantom reads can still occur.

Repeatable-read: Repeatable: first, modify the isolation level and change the read submission to Repeatable. Then, in session 1, change the age of the teacher whose TID is 5 to 60. Finally, check the results in the two sessions. 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22293K1U-6.jpg "style =" cursor: default; "title =" image 8.jpg"/> 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22293JR3-7.jpg "title =" image 10.jpg"/>

The preceding results show that when a value is modified in session 1 at this isolation level, Session 1 immediately displays the modified results, session 2 is not displayed. After the transaction is committed in session 1, we get the permanent result. In session 1, we can check whether it is the modified result, but in session 2, it is still the original result. However, when we commit the transaction in session 2, we can query it again and find that it is the result of the modification in session 1. In Session 2, we did not make any changes. When I commit the transaction, the data has changed. At least, the data displayed before and after the transaction is committed is different. At this point, phantom reading occurs.

Serializable: Serializable. First, we still modify the isolation level and change the repeatable to Serializable. Then start the transaction in session 1, change the age of the teacher whose TID is 5 from 60 to 100, and then view it in sessions 1 and 2. Before starting the transaction, let's look at the age of the teacher whose TID is 5. 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22293J318-8.jpg "style =" cursor: default; "title =" image 17.jpg"/>

In the above display, we found that at the serializable isolation level, when we start two transactions, if a data row is modified in one of the transactions, in another transaction, we cannot query the information of this DataSet. That is to say, the system will not display any information, unless we commit in the modified transaction, or the rollback command is executed. If we do not execute the commit command commit or rollback command rollback in the modified transaction, the transaction will remain stuck during the query, wait until the lock time times out and prompt us to restart the transaction. In middle, we found that in session 1, when we start a transaction and modify a data, we cannot query any information in session 2, when the rollback command is executed in session 1, the query result is displayed in session 2. the query time is much longer than the previous query time. At the serializable level, the notification is not allowed to start multiple transactions, or the operation on the same dataset is not allowed. At this time, there will be no dirty reads, non-repeated reads, or Phantom reads. However, the concurrency will be affected.

In summary, when multiple transactions are executed concurrently at a low isolation level, many problems may occur, such as dirty reads, non-repeated reads, and Phantom reads, however, the transaction concurrency is good, and multiple transactions can be executed simultaneously. At the high isolation level, when multiple transactions are executed concurrently, concurrent execution of multiple transactions is not supported. Although there are no such phenomena as dirty reads, non-repeated reads, and Phantom reads, the concurrency is low. The default isolation level of InnoDB is repeatable-read (repeatable). In most databases, oracle and other databases, the default isolation level is read-committed (read commit ). In practice, in addition to scenarios with high requirements on data security, such as banks and stocks, high isolation levels must be used for other scenarios with low data requirements, low isolation levels can be used to improve concurrency. However, which isolation level is more suitable depends on how high your data security requirements are.

This article from the "Hulunbeier-written on the road to life" blog, please be sure to keep this source http://lq2419.blog.51cto.com/1365130/1226000