Illustration: pixabay

Current read and snapshot read. As the name implies, the current read reads the data submitted at the current moment, while the snapshot read reads the data generated when a snapshot is created.

Here, the conceptual understanding should leave aside the physical concept of reading and writing, the concept of reading and writing separation and so on. The read here includes the processing logic in SELECT, UPDATE, INSERT, and so on.

What is a snapshot?

The logical concept of a view. UNDO LOG + MVCC.

The present moment is easy to understand. At the time the statement is executed, the library (disk +buffer) looks whatever it is.

The snapshot generation time varies according to the isolation level. To review the isolation levels:

  • Read uncommitted. Before a transaction commits, its changes can be seen by other transactions.
  • Read the submission. After a transaction commits, the changes it makes are seen by other transactions
  • Repeatable. The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Uncommitted data is not visible to other transactions
  • Serialization. For the same row, write will add write lock, read will add read lock. When a read-write lock conflict occurs, the last accessed transaction must wait for the previous transaction to complete before execution can continue

When was the snapshot generated at the read uncommitted isolation level?

There is no snapshot, because you don’t need it. Whether submitted or not

When was the snapshot generated at the read Committed isolation level?

When the SQL statement starts execution.

When was the snapshot generated at the repeatable read isolation level?

When the transaction starts

Let’s take an example to understand

1. Read unsubmitted

create table t(a int) engine=InnoDB; Insert into T (a) values(1); Set session Transaction Isolation level READ UNCOMMITTED;Copy the code
A transaction Transaction A result Transaction B Transaction B result answer
begin transaction begin transaction There’s no concept of a view
select a from t 1
select a from t 1
update t set a = 2
select a from t 2 Session A can read the data that Session B has not yet committed
commit transaction
select a from t 2
commit transaction
select a from t 2

In field recording

2. Read Submitted

create table t(a int) engine=InnoDB; Insert into T (a) values(1); Set session Transaction Isolation level READ COMMITTED;Copy the code
A transaction Transaction A result Transaction B Transaction B result answer
begin transaction begin transaction
select a from t 1
select a from t 1
update t set a = 2
select a from t 1
commit transaction UNDO LOG data = 2
select a from t 2
commit transaction
select a from t 2

In field recording

3. Repeat

create table t(a int) engine=InnoDB; Insert into T (a) values(1); # Default isolation levelCopy the code
A transaction Transaction A result Transaction B Transaction B result
begin transaction begin transaction
select a from t 1
select a from t 1
update t set a = 2
select a from t 1
commit transaction
select a from t 1
commit transaction
select a from t 2

In field recording

How do I know if the statement being executed is currently read or snapshot read?

1. At the default isolation level, the SELECT statement defaults to snapshot reads

select a from t where id = 1
Copy the code

2. Select statement lock is the current read

Select a from t where id = 1 lock in share mode; Select a from t where id = 1 for update;Copy the code

3. The UPDATE statement is the current read

update t set a = a + 1;
Copy the code