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

This article mainly explains "how to divide the isolation level of Mysql transactions". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to divide the isolation level of Mysql transactions.

We will use transactions in development work, do you know what kinds of transactions are?

The MYSQL standard defines four types of isolation levels to define which changes inside and outside the transaction are visible and which are not.

Low isolation levels generally support higher concurrent processing and have lower system overhead.

Isolation level from low to high: Read Uncommitted

< Read Committed < Repeatable Read < Serializable。 Read Uncommitted（读取未提交内容） 在该隔离级别，所有事务都可以看到其他未提交(commit)事务的执行结果。 本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。 读取未提交的数据，也被称之为脏读（Dirty Read）。 [窗口A]: mysql>

Set GLOBAL tx_isolation='READ-UNCOMMITTED'

Query OK, 0 rows affected (0.00 sec)

Mysql > quit

Bye

[root@vagrant-centos65 ~] # mysql-uroot-pxxxx (login again)

Mysql > SELECT @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | READ-UNCOMMITTED |

+-+

1 row in set (0.00 sec)

Mysql > use test

Database changed

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from user

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

+-+ +

2 rows in set (0.00 sec)

[window B]:

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | READ-UNCOMMITTED |

+-+

1 row in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test.user values (3,'c')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from user

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

/ / so far, window B has not commit

[window A]:

Mysql > select * from user

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL).

It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction.

This isolation level also supports so-called non-repeatable reads (NonrepeatableRead), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

[window A]:

Mysql > SET GLOBAL tx_isolation='READ-COMMITTED'

Query OK, 0 rows affected (0.00 sec)

Mysql > quit

Bye

[root@vagrant-centos65 ~] # mysql-uroot-pxxxx (login again)

Mysql > SELECT @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | READ-COMMITTED |

+-+

1 row in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

+-+ +

2 rows in set (0.00 sec)

[window B]:

Mysql > SELECT @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | READ-COMMITTED |

+-+

1 row in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

+-+ +

2 rows in set (0.00 sec)

Mysql > delete from test.user where id=1

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 2 | b | |

+-+ +

1 row in set (0.00 sec)

[window A]:

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

+-+ +

2 rows in set (0.00 sec)

[window B]:

Mysql > commit

Query OK, 0 rows affected (0.02 sec)

[window A]:

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 2 | b | |

+-+ +

1 row in set (0.00 sec)

Repeatable Read (reread)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently.

But in theory, this leads to another thorny problem: Phantom Read.

To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row.

InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

[window A]:

Mysql > SET GLOBAL tx_isolation='REPEATABLE-READ'

Query OK, 0 rows affected (0.00 sec)

Mysql > quit

Bye

[root@vagrant-centos65 ~] # mysql-uroot-pxxxx (login again)

Mysql > SELECT @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

[window B]:

Mysql > quit

Bye

[root@vagrant-centos65 ~] # mysql-uroot-pxxxx (login again)

Mysql > SELECT @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Mysql > insert into test.user values (4,'d')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 2 | b | |

| | 4 | d | |

+-+ +

2 rows in set (0.00 sec)

[window A]:

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 2 | b | |

+-+ +

1 rows in set (0.00 sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 2 | b | |

| | 4 | d | |

+-+ +

2 rows in set (0.00 sec)

Serializable (serialization execution)

This is the highest isolation level, and it solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other.

In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

[window A]:

Mysql > SET GLOBAL tx_isolation='SERIALIZABLE'

Query OK, 0 rows affected (0.00 sec)

Mysql > quit

Bye

[root@vagrant-centos65 ~] # mysql-uroot-pxxxx (login again)

Mysql > SELECT @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | SERIALIZABLE |

+-+

1 row in set (0.00 sec)

Mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 2 | b | |

| | 4 | d | |

+-+ +

2 rows in set (0.00 sec)

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test.user values (5,'e')

Query OK, 1 row affected (0.00 sec)

[window B]:

Mysql > quit

Bye

[root@vagrant-centos65 ~] # mysql-uroot-pxxxx (login again)

Mysql > SELECT @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | SERIALIZABLE |

+-+

1 row in set (0.00 sec)

Mysql > select * from test.user

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

[window A]:

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

[window B]:

Mysql > mysql > select * from test.user

+-+ +

| | id | name |

+-+ +

| | 2 | b | |

| | 4 | d | |

| | 5 | e |

+-+ +

3 rows in set (0.00 sec)

At this point, I believe you have a deeper understanding of "how to divide the isolation level of Mysql transactions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.