Why MVCC

When multiple database transactions are running at the same time, the visibility of the intermediate state generated by their modified data to other transactions is limited by the database isolation level, and there are four types of visibility constraints

  • Read uncommitted
  • Reading has been submitted
  • Repeatable read
  • serialization

Read uncommitted the weakest among them, more than one transaction share a data, the strongest and serialization, read and read lock, write data to add write locks, and read committed and repeatable read all need to have a “not read uncommitted data” and “read only create a transaction data” before such requirements, and implement such a demand, naturally want to introduce the concept of “snapshot”, In MySQL, snapshot is implemented with the help of MVCC, namely multi-version concurrency control. The following will mainly describe how to organize snapshots and how to use snapshots.

How are snapshots organized?

MySQL snapshot relies on the version chain, also known as UndoLog. UndoLog keeps the original version of the data and records the latest version of the data every time it changes the data. Since it is a chain, there is a pointer, which is in the hidden field of each row of data. The transaction has its own unique, increment transaction Id, unique is a necessary condition to identify the transaction, and increment, gives the meaning of sequential transaction Id, sequential Id is very important for the later use of snapshot stage, of course, the transaction Id and undo pointer are hidden in each row of data field.

Among themtrx_idIs the transaction Id,roll_pIs a pointer

If A record is A, the following operations are performed

Transaction 2 Transaction 3
begin;
Change A to B begin;
Change B to C(point 1)
commit;
commit;

Then the version chain at time 1 is as follows:

Version chains have now, but how to use, by only one version of the chain is the lack of enough information to realize the read committed and repeatable read, we still lack of the transaction to create some additional information, like, read submitted need to know what is submitted, further, which the transaction is committed, and so on, This information forms the runtime context in which MVCC is used. Let’s summarize the context information in which MVCC is used.

How do I use snapshots?

Also need to record some create moments when creating a snapshot of basic information, such as create a transaction time, what are the transaction is running, the data is convenient to define which transaction has been submitted, I can read, also needs the current transaction transaction Id, also need to create a snapshot of the moment, the logo for the current transaction, Which transactions are not visible to the future of the current transaction. For these data, the following figure shows:

Creator_trx_id is the creator transaction. Min_trx_id is the minimum value of m_IDS. A transaction Id less than min_trx_id is not a problem for the current transaction. Max_trx_id is the next Id of the current transaction Id.

How snapshots work depends on the information described above with the undoLog chain:

  • If access to the undolog versiontrx_idAttribute valuescreator_trx_idSame, it is their own record, visible
  • iftrx_idLess thanmin_trx_idIt indicates that the record has been submitted and is visible
  • iftrx_idGreater than or equal tomax_trx_idIs the transaction after the view was created and is not visible
  • iftrx_idIn amin_trx_idandmax_trx_idBetween, you need to judge the present momenttrx_idWhether inm_idsIf it is still there, it is not visible.

The difference between read committed and repeatable reads

The basic information described above is contained in a ReadView

  • In the case of a read commit, a new view is created for each read. In this case, the data in the read view in a transaction is constantly changing, so the latest commit can be read each time
  • For repeatable reads, a view is created each time a transaction is read and is used forever.

When the version chain is cleared

  • Run the Purge thread in the background when to delete it

Matters needing attention

In order to support MVCC, the updated data can be retained at the same time as the old data. The deleted data will not be deleted immediately, but will be marked with deletion. Therefore, it is necessary to pay attention to the impact of long transactions on MySQL itself

During development, you can set Set AutoCOMMIT =1

Query long transactions can be viewed with the following statement:

select
    * 
from information_schema.innodb_trx
where 
TIME_TO_SEC(timediff(now(),trx_started))>60
Copy the code

The resources

Gold digger little book Geek Time