Display of data under different transaction isolation levels in MySQL--reprint

A transaction is a set of atomic SQL query statements that can also be viewed as a unit of work. If the database engine succeeds in applying all of the query statements to the database, it executes all queries, and if any of the query statements cannot be executed because of a crash or other reason, then none of the statements will be executed. In other words, the statements within a transaction are either all executed or not executed in a single sentence.

Characteristics of a transaction: four Tests of acid, also known as transactions (atomicity, consistency, isolation, persistence)

Automicity: atomicity, a database operation caused by a firm, either completed or not executed

Consisitency: Consistency, the sum before transaction execution and the sum after the transaction is unchanged

Isolation: Isolation, the result of a transaction is only visible to other transactions after it is completed

Durability: Persistence, once a transaction is successfully completed, the system must ensure that no failure causes the transaction to exhibit inconsistencies

Status of the transaction:

Activities

Partial submissions

Failed

Stop

Submit

A transaction must be in one of the top five states at a time, and the transformation between the state of the transaction is as follows:

Issues caused by transaction concurrency

Dirty Reads (drity read): A transaction has updated one copy of the data, another transaction reads the same data at this time, for some reason, the previous rollback operation, the latter will read the data is not correct.

Non-repeatable read (non-repeatable Read): Data inconsistency in two queries of a transaction, which may be a transaction that was inserted in the middle of a two-query process to update the original data.

Phantom Read (Phantom Read): Data inconsistency in two queries of a transaction, such as one transaction querying several columns (row) data, while another transaction inserts a new column of data at this point, and the previous transaction in the next query will find that there are a few columns of data that it did not previously have.

concurrency control

Multi-version concurrency control: multiversion concurrency CONTROL,MVCC

Each user operations data is a time snapshot of the source data, and when the user operation is complete, merge into the source data according to the point in time of each snapshot

Lock: The simplest implementation mechanism for concurrency control is the lock (not the lock mechanism used by MVCC).

Read lock: Shared lock, by the read table operation plus the lock, lock the other users can only get the table or row of the shared lock, can not get exclusive lock, that is, can only read can not write

Write Lock: An exclusive lock, a lock that is added by a write table operation, and a lock that the other user cannot obtain for any lock on the table or row

Lock granularity: From large to small, the MySQL server only supports table-level locks, and row locks require the storage engine to complete.

Table Lock: Lock a table

Page Lock: Lock a page

Row Lock: Locks a row

The finer the granularity, the better the concurrency. The concurrency of a lock is best, but it requires the support of the storage engine.

Four isolation levels for transactions

READ UNCOMMITTED: Allow dirty reads, that is, data that could be read to uncommitted transaction modifications in other sessions

Read Committed: Only data that has been committed can be read. Most databases, such as Oracle, are this level by default

Reread (Repeatable Read): Queries within the same transaction are the same as the start of a transaction, the default level of InnoDB. In the SQL standard, this isolation level eliminates non-repeatable reads, but there are also phantom reads

Serializable (SERIALIZABLE): Fully serialized read, each read requires a table-level shared lock, both read and write blocking each other

In MySQL, in the case of concurrency control, the different isolation levels are likely to cause problems as follows:

The above reason is to introduce so much theoretical knowledge, is to facilitate understanding. In the table above has been listed, at different isolation levels, the display of the data may be problems, now install MySQL on Linux, through our experiments to look at the different isolation levels of data display.

Lab Environment:

Linux system: RedHat 5.8

Linux kernel: Linux-2.6.18-308.el5

MySQL version: mysql-5.6.10-linux-glibc2.5-i686

All of the operations in this experiment are done in a virtual machine, connect the virtual machine via Xmanager, then open two session connections, change the isolation level at the same time in both sessions, and see how the data is displayed.

In this experiment, MySQL installs MySQL in the way of source code compilation, and you can install MySQL directly using the RPM package. The specific source of the installation of the way and process, here is no longer demonstrated, in the previous blog, I have introduced many times. If you use the source code to compile the installation of the way, do not know how to install MySQL, you can see my previous blog, the inside are introduced. In the MySQL configuration file, it is best to enable a table space per table in the form of source code compilation and installation. Here we are directly enabled.

Because it was an experiment, there was no password for MySQL, so we went directly to MySQL using the command. The command and display results are as follows:

50[[email protected] ~]# mysql-uroot-p #使用该命令进入mysql, because there is no password, when asked to enter the password, press ENTER directly

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) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> Show variables like '%iso% '; #查看mysql默认的事务隔离级别, the default is to be accented. You can also use the SELECT @ @tx_isolation command to view

+-----------------+------------------+

| variable_name | Value |

+-----------------+------------------+

| tx_isolation | Repeatable-read |

+-----------------+------------------+

1 row in Set (0.36 sec)

mysql> show databases; #查看系统已经存在的数据库

+---------------------+

| Database |

+---------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| Test |

+---------------------+

4 rows in Set (0.00 sec)

Now import the database used in our experiment.

[email protected] ~]# MySQL < jiaowu.sql #导入实验所用的jiaowu数据库

[Email protected] ~]# 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) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> show databases; #查看导入的jiaowu数据库是否存在

+----------------------+

| Database |

+----------------------+

| Information_schema |

| Jiaowu |

| MySQL |

| Performance_schema |

| Test |

+----------------------+

5 rows in Set (0.01 sec)

In the MySQL command interface, when the transaction is not explicitly enabled, each command entered is submitted directly, because there is a variable value in MySQL, which enables autocommit. This means that each statement we enter is automatically committed, which results in a lot of disk IO, which reduces the performance of the system. When we do the experiment, because we want to explicitly use the transaction, it is recommended to turn off the auto-commit feature, if it does not close, but if you do not explicitly use the transaction, want to do the experiment below, then you need to turn off this function. Here, we explicitly use the transaction, and turn off the auto-commit feature. If you turn off auto-commit, you need to explicitly use transactions, otherwise all the statements you enter will be treated as a transaction. The command is as follows:

16mysql> SELECT @ @autocommit; #查看该值, 1 means that automatic commit is initiated

+--------------+

| @ @autocommit |

+--------------+

| 1 |

+--------------+

1 row in Set (0.00 sec)

Mysql> set autocommit=0; #关闭自动提交功能

Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT @ @autocommit; #重新查看该值, the auto-commit feature is turned off for 0

+--------------+

| @ @autocommit |

+--------------+

| 0 |

+--------------+

1 row in Set (0.00 sec)

Now open two sessions, in both sessions into MySQL, first of all remember to modify the two autocommit variables in the reply, turn off the auto-commit function, and then view the isolation level of the transaction, the default is Repeatable-read. The isolation level needs to be modified in both sessions. Let's start with the lowest isolation level.

30mysql> SELECT @ @tx_isolation;

+-------------------+

| @ @tx_isolation |

+-------------------+

| Repeatable-read |

+-------------------+

1 row in Set (0.00 sec)

Mysql> set tx_isolation= ' read-uncommitted '; #修改隔离级别, change the isolation level to read UNCOMMITTED

Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT @ @tx_isolation;

+-------------------+

| @ @tx_isolation |

+-------------------+

| read-uncommitted |

+-------------------+

In two answers, after modifying the isolation level, use the imported database and use the Tutors table to verify the display.

mysql> use Jiaowu; #使用jaiowu数据库

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> Show tables; #查看该数据库中都有那些表

+-------------------+

| Tables_in_jiaowu |

+-------------------+

| Courses |

| Scores |

| Students |

| Tutors |

+-------------------+

4 rows in Set (0.00 sec)

In session 1, we'll modify the data in tutors, and in session 2 we'll look at the data and see what happens. The intention was to use the tutors table to demonstrate the experiment below, but it was cumbersome to modify the data, so I wrote a script that created a new table teachers than the fields of the tutors table. The script is a bit clumsy and interested in writing a better script for creating and inserting data. The script to create the table and insert the data is as follows:

23#!/bin/bash

#

#Author: Hulunbeier, http://lq2419.blog.51cto.com/

#Description: 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 #执行该脚本是, will let input a number, because it is an experiment, so we insert 5 rows of data here, the reader can modify their own

For I in ' seq 1 $NUM '; Do

Name=tech$i

A= ' echo $RANDOM/365 | BC '

Until [$A-ge] && [$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 script above to create the table and insert the data. Check to see if the new table we created is successful and whether there is data inside. The query command and display results are as follows:

The new table you created already exists, and the insert data is successful, so let's use the teachers table to demonstrate the following experiments. The demo experiment starts at a low isolation level and ends at a high isolation level.

Read-uncommitted: Read not submitted

First, modify the Autocommit feature in both sessions, turn it off, and then modify the system's default isolation level, starting at the low level and changing the default to read uncommitted.

After the above modifications are completed in both sessions, we explicitly start the transaction, query all the data in the table below, show the age of 61 for the teacher with Tid 5, then update the teacher's age in session 1 with the TID 5 in the Teachers table, change the original 61 to 50, and then In both sessions, look at all the data under Requery, and see how old the teacher is at 5 tid. The command and display effects are as follows:

When we used rollback rollback in Session 1, we looked at the data in both sessions and found that it was 61. From the display of the top two sessions, you can see that when the isolation level is read UNCOMMITTED, when we open a transaction, we modify the information for a data row in that transaction, and in that transaction, it is not committed, but in another transaction, if it is an operation on the same dataset, Will find that the results of our two queries are different, in the same transaction, two query results are different, this situation is not allowed to occur. In this case, the phenomenon of dirty reading, non-repetition reading and phantom reading appeared.

Read-committed: Read Commit

First modify the isolation level on the basis of the above modification and change the READ uncommitted to read commit. We then changed the age of the teacher of Tid to 5, changing the 61 to 40. Next, look at the display effect again in two sessions. The command and display effects are as follows:

Now, in session 1, we use the commit command to commit the transaction, and then we'll see what happens next in conversation 2, looking at what's going on. It turns out that in Session 2, the age of the 5 teacher of the TID becomes 40. From the top of the display, you will find that when the isolation level is read-committed (read commit), when we open a transaction in Session 1, and modify the information of a row of data, in session 1 can see the modified effect, but in session 2 will not see the modified results. When we commit a transaction in session 1 and then query in the 2, we will find that it is not the same as our last query, which shows the result of Session 1 modification. Under this isolation level, although dirty reads can be avoided, there are still non-repeatable reads and Phantom reads.

Repeatable-read: Can be stressed

First modify the isolation level to change the read submission to be reread. Then, in Session 1, the age of the teacher whose TID is 5 is still modified, and its age is changed to 60. Finally, the results are reviewed in two sessions.

The result from the top shows that at this isolation level, when we modify a value in Session 1, Session 1 immediately displays the modified result, and session 2 does not appear. When we commit a transaction in Session 1, we get a permanent result, in Session 1, in the view, or the modified result, but in Session 2, the original result. But when we commit the transaction in Session 2, and then query, we find that the result of the modified session 1, in Session 2, we did not make any changes, I commit a transaction, found that the data has changed. At the very least, the data that was seen before and after the transaction was submitted is not the same. At this point, there is a phenomenon of phantom reading.

Serializable: Serializable

First, we still modify the isolation level, changing the stress to serializable. Then in session 1, start the transaction and change the age of the TID to 5 for the teacher from 60 to 100, and then view in Sessions 1 and 2. Before starting a transaction, let's take a look at the age of the 5 teacher at Tid.

In the above display, it is found that under the Serializable isolation level, when we start two transactions, if we modify a data row in one of the transactions, we cannot query the information of the dataset in another transaction, that is, the system will not display any information, except in the modified transaction, we commit the , or the rollback command was executed. If, in the modified transaction, we neither commit the commit command nor perform the rollback command rollback, then in another transaction, when we query, it will remain stuck until the lock time expires, and then prompt us to reopen the transaction. In, the discovery, in Session 1, when we start a transaction, and modify a data, in Session 2, we can not query to any information, when we execute the rollback command in Session 1, Session 2 will not display the query results, at this time the query will take longer than the previous query time. Because at the serializable level, it is not allowed to enable multiple transactions to be opened, or to perform any operation on the same dataset. At this point, there will be no dirty reads, no repeatable reads, and no Phantom reads. However, concurrency at this point is affected.

In summary, in the low isolation level, when there are multiple transactions concurrently executed, although there are many problems, such as dirty read, non-repeatable read, Phantom Read, but the concurrency of the transaction is good, can execute multiple transactions at the same time, at high isolation level, when there are multiple transactions concurrently executing, because under high isolation level, multi-transaction concurrent execution , although there are no phenomena such as dirty reading, non-repeatable reading and Phantom reading, but the concurrency is low. InnoDB The default isolation level is Repeatable-read (reread), whereas in most databases, most databases, such as Oracle, typically have a default isolation level of read-committed (read commit). Generally in practical applications, in addition to the bank, stocks and other high data security requirements of the scene, must use a higher isolation level, other data requirements are not high, can adopt a low isolation level, to improve concurrency. However, the level of isolation that is more appropriate depends on how much security you need for your data.

Original: http://www.haofanben.com/jiaocheng/mysqlshujuku/197015.html

Display of data under different transaction isolation levels in MySQL--reprint