MVCC Analysis (Turn)

When a database is concurrently read and written, the read operation may be inconsistent with the data (dirty Read). In order to avoid this situation, it is necessary to implement concurrent access control of the database, the simplest way is locking access. Because the lock will serialize the read and write operations, there will be no inconsistent state. However, read operations are blocked by write operations, significantly reducing read performance. In the Java concurrent package, there are classes of the Copyonwrite series that are specifically designed to optimize read-to-write scenarios. and its optimization means, in the writing operation, the data copy, will not affect the original data, and then modify, after the completion of the modification of the atom to replace the old data, and read operation will only read the original data. Writing in this way does not block read operations, thus optimizing read efficiency. The write operation is mutually exclusive, and each write operation will have a copy, so it is only suitable for reading more than the case of writing.

The MVCC principle is similar to the Copyonwrite, the full name is Multi-version Concurrent control, that is, multi-version concurrency control. Under the MVCC protocol, each read operation sees a consistent snapshot and can implement non-blocking reads. MVCC allows data to have multiple versions, this version can be a timestamp or a globally incrementing transaction ID, and at the same point in time, different transactions see different data.

Implementation principle:

------------------------------------------------------------------------------------------> Timeline

|-------R (T1)-----|

|-----------U (T2)-----------|

For example, suppose there are two concurrent operations R (T1) and U (T2), T1 and T2 are transaction id,t1 less than T2, the system contains data A = 1 (T1), R and W operations as follows:

R:read A (T1)

U:a = 2 (T2)

The version of R (read operation) T1 represents the version of the data to be read, and then the write operation will not update the version, and the read operation will not. On the timeline, R is later than U, and because u commits after r, it is not visible to r. Therefore, R will only read the T1 version of the data, i.e. A = 1.

Because the consistency of existing data cannot be affected until the update operation is committed, the old data is not changed and the update operation is split into insert + DELETE. You need to mark the deletion of old data, insert new data. Subsequent read operations are not affected until the update is committed. For read operations, however, the write operation that is being performed is not visible to all of the writes before it.

It says a bunch of imaginary theories, and here's a little work to see how MySQL's InnoDB engine is MVCC. InnoDB adds two fields for each row, representing the version and deleted version of the row, and fills in the version number of the transaction, which is incremented as the transaction is created. At the isolation level of repeated read (see this article for the isolation level of the transaction), the implementation of the various database operations is as follows:

Select: InnoDB returns the row data if the following two conditions are met: (1) The created version number of the line is less than or equal to the current version number and is used to guarantee that all operations have been performed before the select operation. (2) The deleted version number of the row is greater than the current version or is empty. Deleting a version number that is larger than the current version means that there is a concurrent transaction that deletes the row.

Insert: Sets the created version number of the newly inserted row to the version number of the current system.

Delete: The deleted version number of the row you want to delete is set to the version number of the current system.

Update: Do not perform in-place update, but instead convert to insert + DELETE. Set the delete version number of the old row to the current version number and insert the new row with the same setting to create the version number as the current version number.

Where write operations (insert, delete, and update) are performed, you need to increment the system version number.

Because the old data is not really deleted, so the data must be cleaned up, InnoDB will open a background thread to perform cleanup work, the rule is to delete the deletion version number is less than the current system version of the row delete, this process is called purge.

Through MVCC very good realizes the isolation of the transaction, can reach repeated read level, to implement serializable also must lock.

http://blog.csdn.net/chosen0ne/article/details/18093187

MVCC Analysis (Turn)