MySQL read commited and repeatable read reason

Read commited and Repeatable read

On the question of consistency reading. Refer to MySQL technology insider--INNODB storage Engine

Session 1

：

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from T2;

+------+

| ID |

+------+

| 1 |

+------+

1 row in Set (0.00 sec)

At the same time to Session2:

Mysql>

Mysql>

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from T2;

+------+

| ID |

+------+

| 1 |

+------+

1 row in Set (0.00 sec)

mysql> update T2 set id=2;

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

Mysql>

Mysql> select * from T2;

+------+

| ID |

+------+

| 2 |

+------+

1 row in Set (0.00 sec)

Mysql> commit;

Query OK, 0 rows affected (0.01 sec)

And back to Session1:

If it is

Mysql> SELECT @ @tx_isolation;

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

| @ @tx_isolation |

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

| read-committed |

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

1 row in Set (0.00 sec)

Mysql> select * from T2;

+------+

| ID |

+------+

| 2 |

+------+

1 row in Set (0.00 sec)

In Session1 of the same thing, two queries T2, you will see the results,

If it is:

Mysql> SELECT @ @tx_isolation;

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

| @ @tx_isolation |

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

| Repeatable-read |

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

1 row in Set (0.00 sec)

In Session 1, you see

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from T2;

+------+

| ID |

+------+

| 2 |

+------+

1 row in Set (0.00 sec)

Mysql> select * from T2;

+------+

| ID |

+------+

| 2 |

+------+

1 row in Set (0.00 sec)

is the same result.

This is due to MySQL's consistent non-locking read.

Read operations do not wait for a row to be locked if the read row is in preparation for another thing to perform an update or delete operation

Release, InnoDB go back and read a snapshot of the data. Because there is no need to wait for access to the X lock on the line, it is called non-locking read.

Snapshot data refers to the previous version of the data (there may be multiple versions) of the row. is achieved by Undo, and Undo is

To roll back data in things, so there is no additional overhead for snapshot data.

In repeatable read, the snapshot reads always read the row version data at the beginning of the thing, and in read commited,

is to read the latest snapshot, so a query will see no results.

MySQL read commited and repeatable read reason