Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the differences between Repeatable-Read and Read-Committed". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "what are the differences between Repeatable-Read and Read-Committed".

Mysql provides Repeatable-Read repeatable readability by default, which is more suitable for oltp

Read-Committed non-repeatable read can also be called commit read

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)

These are all the contents of this article entitled "what are the differences between Repeatable-Read and Read-Committed". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.