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

This article mainly explains "how to understand Innodb consistent non-lock reading". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand Innodb consistent non-lock reading".

Consistent non-locking read means that InnoDB reads data by querying database snapshot data at a certain point in time through multiple version control (MVCC).

Under the RR transaction isolation level, a snapshot is created when the data is first (select read) in a transaction. The snapshot is the latest version of all committed data before the first select, and before the end of the transaction, the data received by select is consistent (snapshot).

Note: the snapshot is not created when Begin and start transaction start the transaction, but the first time select reads the data.

As follows:

Session A

Session B

Session A > drop table t

Session A > create table t (x int primary key)

Session A > insert into t values (1), (5)

Session A > begin

Session A > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

Session A > select * from t where Xero5 for update

The lock read performed by the above for update does not create a read view. Note: both for update and lock in share mode are lock reads, and no snapshot will be created at this time.

Session B > begin

Session B > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Session B > insert into t select 3

Session B > commit

Session A > select * from t; # create read view to generate snapshots at this time

+-- +

| | x |

+-- +

| | 1 |

| | 3 |

| | 5 |

+-- +

You may find that the RR isolation level should not be able to see the data modified by other transactions. This is because begin starts a transaction, not when the read view is created when begin, but when the snapshot is read for the first time

Select operations are performed under the READ COMMITTED and REPEATABLE READ isolation levels of innodb, and the default mode is consistent read.

Consistent reads do not put any locks on the accessed tables, so other sessions can modify object data at will without affecting the consistent reads of the current session.

Under the RR isolation level, the data of a transaction T1 query table is opened at a certain point in time, and then another new transaction T2 is opened for its delete, update, insert data and commit is successful, the result of T2 modification and commit cannot be seen in T1.

Note: snapshot reads are mainly applicable to select statements in a transaction. So the DML statement in the transaction can see the update of the transaction in other session, and even SELECT can't see these.

For example, if transactions T1, T2 and T3 are opened at the same time, and the data of table t is deleted or modified in T1, the result of the select query (snapshot read) in T2 is the data of the previous t (the data of t before the start of T2 transaction), but the modification or deletion of table t by T3 may affect the rows that have just been committed by T1.

In the following example: Session A can see the data inserted by Session B only after the insert operation commit of Session B is completed and Session An itself transacts commit.

You can use READ COMMITTED or locking read (SELECT * FROM t LOCK IN SHARE MODE;) to view the latest data in the table.

Consistent reads do not apply to specific DDL statements such as DROP TABLE, ALTER TABLE.

In addition, for INSERT INTO... SELECT, UPDATE... (SELECT) and CREATE TABLE... SELECT operation. Although FOR UPDATE or LOCK IN SHARE MODE is not specified in the subsequent select, the select reads the latest version of the data (the current read used), just like the SELECT under the READ COMMIT isolation level.

As follows:

Session A

Session B

Ession A > create table a (x int primary key,y int)

Query OK, 0 rows affected (0.17 sec)

# start a new transaction

Session A > begin

Query OK, 0 rows affected (0.00 sec)

Session A > select * from a

Empty set (0.00 sec)

# insert data

Session A > insert into a select 1Pol 2

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

# insert data can be seen in the current transaction

Session A > select * from a

+-+ +

| | x | y |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec) # not commit for now

# before A commits, B starts transaction Session B > begin

Query OK, 0 rows affected (0.00 sec)

# at this point, query the data in table an and read the data that has been committed before the transaction starts. Because the transaction in B has not been committed when the transaction starts, the insert data in A will not be displayed.

Session B > select * from a

Empty set (0.00 sec)

# session A commits the above transaction Session A > commit

Query OK, 0 rows affected (0.00 sec)

# the select query in B is still a snapshot read, so you cannot see the data Seesion B > select * from a

Empty set (0.00 sec)

Seesion B > select * from aa

Empty set (0.00 sec)

# INSERT INTO... SELECT * FROM A; operation

Seesion B > insert into aa select * from a

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

Seesion B > select * from a

Empty set (0.00 sec)

# found that the data can not be found in the direct select table a, but can be seen in the aa table

Seesion B > select * from aa

+-+ +

| | x | y |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec)

# using lock in share mode or for update for current reading, you can see the data in table a

Seesion B > select * from a lock in share mode

+-+ +

| | x | y |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec)

Seesion B > select * from a

Empty set (0.00 sec)

Seesion B > select * from a for update

+-+ +

| | x | y |

+-+ +

| | 1 | 2 |

+-+ +

1 row in set (0.00 sec)

When the innodb_locks_unsafe_for_binlog option is 1 (turn off the GAP lock), under the READ UNCOMMITTED, READ COMMITTED, REPEATABLEREAD isolation level, the data of the select query table will not be locked, but will be read consistently.

Thank you for your reading, the above is the content of "how to understand Innodb consistent unlocked reading". After the study of this article, I believe you have a deeper understanding of how to understand Innodb consistent non-locking reading, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.