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

I wrote an article about affairs (1) before, which can be regarded as a basic understanding of transactions. Today, let's briefly summarize the isolation level of transactions. Although it is an old knowledge point, it is worth reviewing it.

There are basically two kinds of transaction isolation level settings in MySQL, the default RR (Repeatable-Read) and the common RC (Read-Committed) in practice. What is the difference between the two, how to correctly understand, with a few SQL sentences can be explained, with a simple experiment to understand.

Let's get started.

First create a test table, test, and insert some data.

Create table test (id int primary key,name varchar (30), memo varchar (30))

Insert into test values), (2) recorder name2), (3), (3), (4), (4), (5), (5) change the isolation level from the default RR to RC, which is also the default transaction isolation level for many other databases.

We open two windows to compare the associated tests.

Testing in RC mode

one

Window 1

> show variables like 'tx_isolation'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | READ-COMMITTED |

+-+ +

1 row in set (0.01 sec)

> begin;-- start a transaction

> select * from test;-- View data

+-- +

| | id | name | memo | |

+-- +

| | 1 | name1 | aaaa |

| | 2 | name2 | aaaa |

| | 3 | name3 | aaaa |

| | 4 | name4 | aaaa |

| | 5 | name5 | aaaa |

+-- +

5 rows in set (0.00 sec)

two

Window 2

Begin;-start a transaction

> update test set name='aaaaa' where id=2;-- modify a record

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

> commit;-- commit transaction

Query OK, 0 rows affected (0.01 sec)

one

Window 1

> select * from test;-if you look at the data in window 1, you will find that the data in the original window has changed. This is a typical example of non-repeatable reading.

+-- +

| | id | name | memo | |

+-- +

| | 1 | name1 | aaaa |

| | 2 | aaaaa | aaaa |

| | 3 | name3 | aaaa |

| | 4 | name4 | aaaa |

| | 5 | name5 | aaaa |

+-- +

5 rows in set (0.00 sec)

Testing in RR mode

Let's take a look at the isolation level of RR. In fact, with the above tests, we have a relative bottom. This is the default isolation level for MySQL, and phantom reading occurs.

one

Window 1

First change the isolation level from RC to RR

> set global transaction isolation level repeatable read

Query OK, 0 rows affected (0.00 sec)

? View the transaction isolation level.

> show variables like 'tx_isolation'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

> begin;-- start a transaction

> select * from test;-- View the data of table test.

+-- +

| | id | name | memo | |

+-- +

| | 1 | name1 | aaaa |

| | 2 | aaaaa | aaaa |

| | 3 | name3 | aaaa |

| | 4 | name4 | aaaa |

| | 5 | name5 | aaaa |

+-- +

5 rows in set (0.00 sec)

two

Window 2

> begin;-- start a transaction

> update test set name='RR_test';-- modify the data in table test, and all records are changed.

Query OK, 5 rows affected (0.01sec)

Rows matched: 5 Changed: 5 Warnings: 0

> commit;-- commit transaction

Query OK, 0 rows affected (0.00 sec)

one

Window 1

> select * from test;-in RR mode, the transaction in window 1 is still seeing the original data because it has not been committed.

+-- +

| | id | name | memo | |

+-- +

| | 1 | name1 | aaaa |

| | 2 | aaaaa | aaaa |

| | 3 | name3 | aaaa |

| | 4 | name4 | aaaa |

| | 5 | name5 | aaaa |

+-- +

5 rows in set (0.00 sec)

> commit;-- We commit the transaction for window 1

Query OK, 0 rows affected (0.00 sec)

> select * from test;-- when you look at the data again, it changes. In fact, there is no DMl operation in window 1.

+-- +

| | id | name | memo | |

+-- +

| | 1 | RR_test | aaaa |

| | 2 | RR_test | aaaa |

| | 3 | RR_test | aaaa |

| | 4 | RR_test | aaaa |

| | 5 | RR_test | aaaa |

+-- +

5 rows in set (0.00 sec)

Summary

Use a popular example to illustrate, for example, when you get a salary increase, the leader asks you to talk, and then gives you a 10% salary increase RC, which takes effect immediately in the same month; in the case of RR, the leader asks you to talk, and as a result, you see that your salary remains the same after two months. In the third month, you have also made up for the salary increase of the previous two months. That's probably what it means.

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.