For the mvcc Implementation of innodb in mysql, find a category on the Internet and continue with blog. csdn. netchen77716articledetails6742128Mysql in the future. How does one implement MVCC? There are countless people asking this question, but there is no answer in google. This article attempts to find the answer from the Mysql source code. In Mysql, MVCC is obtained in the Innodb Storage engine.
For the mvcc Implementation of innodb in mysql, find a category on the Internet and continue to implement MVCC in blog.csdn.net/chen77716/article/details/6742128 Mysql? There are countless people asking this question, but there is no answer in google. This article attempts to find the answer from the Mysql source code. In Mysql, MVCC is obtained in the Innodb Storage engine.
The mvcc Implementation of innodb in mysql. Find one on the Internet for classification and continue later.
Blog.csdn.net/chen77716/article/details/6742128
How does Mysql implement MVCC? There are countless people asking this question, but there is no answer in google. This article attempts to find the answer from the Mysql source code.
In Mysql, MVCC is supported in the Innodb Storage engine. Innodb implements three hidden fields for each row of records:
- 6-byte transaction ID (
DB_TRX_ID)
- 7-byte rollback pointer (DB_ROLL_PTR)
- Hidden ID
A 6-byte transaction ID is used to identify the transactions described in this row. A 7-byte rollback pointer needs to understand the transaction model of Innodb.
1. Innodb Transaction related concepts
To support transactions, Innbodb introduces the following concepts:
- Redo log
Redo log is used to save the executed SQL statement to a specified Log file. when Mysql executes the recovery, it can re-execute the SQL operation of the redo log record. When the client executes each SQL statement (update statement), The redo log is first written into the log buffer. When the client executes the COMMIT command, the content in the log buffer is refreshed to the disk as needed. Redo log exists as an independent file on the disk, that is, the Innodb log file.
- Undo log
Opposite to redo log, undo log is used for rollback. The specific content is to copy the database content (ROW) before the transaction to the undo buffer, refresh the content in the undo buffer to the disk at the appropriate time. Like the redo buffer, the undo buffer is also a ring buffer, but when the buffer is full, the content in the undo buffer will be refreshed to the disk. Unlike the redo log, there is no separate undo log file on the disk. All undo logs are stored in the primary ibd data file (tablespace), even if each table is set to a data file on the client.
- Rollback segment
The concept of rollback segments comes from the Oracle transaction model. In Innodb, the undo log is divided into multiple segments, and the undo log of a Specific Row is stored in a specific segment, it is called a rollback segment. It can be considered that the undo log and rollback segment share the same meaning.
- Lock
Innodb provides row-based locks. If the number of rows is very large, the number of locks under high concurrency may be relatively large. According to Innodb documentation, innodb effectively optimizes the lock space. Even if the concurrency is high, the memory will not be exhausted.
There are two types of row locks: exclusive locks and shared locks. The exclusive lock and the exclusive lock are completely equivalent to the read/write lock. If a transaction is updating a row (exclusive lock), other transactions, whether read or write, must wait. If a transaction reads a row (shared lock ), other read tasks do not have to wait, but write tasks do. The shared lock ensures no waiting among multiple reads, but the lock application depends on the transaction isolation level of Mysql.
- Isolation level
The isolation level is used to limit the degree of direct interaction of transactions. Currently there are several industrial standards:
-READ_UNCOMMITTED: Dirty read
-READ_COMMITTED: Read and submit
-REPEATABLE_READ: Repeated read
-SERIALIZABLE: SERIALIZABLE
Innodb supports all four types of data. There are not many dirty read and serialized application scenarios, and it is widely used for read submission and repeated read. The implementation method will be introduced later.
2. Row update process
The following shows the update process of a transaction to a row record:
1. Initial Data row
F1 ~ F6 is the name of a row or column, 1 ~ 6 is the corresponding data. The following three hidden fields correspond to the transaction number and rollback pointer of the row respectively. If the data is just inserted, the ID is 1 and the other two fields are blank.
2. Transaction 1 changes the value of each field in the row
When transaction 1 changes the value of this row, the following operations are performed:
- Lock this row with exclusive locks
- Redo log
- Copy the value before the row modification to the undo log, that is, the lower row
- Modify the value of the current row and enter the transaction number to point the rollback pointer to the row before the modification in the undo log.
3. Transaction 2 modify the value of this row
Same as transaction 1. At this time, the undo log contains two rows of records and is linked together through the rollback pointer.
Therefore, if the undo log is not deleted, the initial content of the row will be traced back through the rollback pointer of the current record. Fortunately, the purge thread exists in Innodb, it queries undo logs earlier than the oldest active transactions and deletes them to ensure that the undo log file does not grow infinitely.
4. transaction commit
When a transaction is committed normally, Innbod only needs to change the transaction status to COMMIT without additional work, while Rollback is slightly more complex, you need to find the transaction version before modification from the undo log based on the current rollback pointer and restore it. If there are many rows affected by the transaction, rollback may be less efficient. Based on experience, there are no rows in the transaction: 1000 ~ Between 10000, Innodb efficiency is still very high. Obviously, Innodb is a storage engine with a higher COMMIT efficiency than Rollback. It is said that the implementation of ipvss is exactly the opposite.
5. Insert Undo log
The above process exactly describes the transaction process of UPDATE. In fact, the undo log is divided into insert and update undo log because the original data does not exist during insert, therefore, the insert undo log can be discarded during rollback, while the update undo log must follow the above process.
3. Transaction Level
As we all know, update (insert, delete) is a transaction process. In Innodb, query is also a transaction and read-only transaction. When read/write transactions concurrently access the same row of data, what content can be read depends on the transaction level:
- READ_UNCOMMITTED
When the read is not committed, the read transaction reads the master record directly, regardless of whether the update transaction is complete.
- READ_COMMITTED
When a read transaction is committed, it reads the latest version of the undo log each time. Therefore, two reads to the same field may read different data (phantom read ), however, the latest data can be read every time.
- REPEATABLE_READ
Read the specified version every time, so that no phantom read is generated, but the latest data may not be read.
- SERIALIZABLE
Lock tables, read/write mutual blocking, less used
A read transaction is generally triggered by a SELECT statement, which ensures non-blocking in Innodb. Except for select statements with for update, SELECT statements with for update will apply an exclusive lock to rows, wait until the update transaction is complete and read its latest content. The design goal of Innodb is to provide efficient and non-blocking query operations.
4. MVCC
Before the above update, an undo log is created. MVCC is used for non-blocking reading based on various policies, and the row in the undo log is multiple versions in MVCC, this may differ greatly from the MVCC we understand. We generally think that MVCC has the following features:
- Each row of data has a version, which is updated each time the data is updated.
- When the modification is made, the current version is copied and modified without interference between transactions.
- Compare the version number when saving. If it succeeds (commit), it overwrites the original record; if it fails, it discards copy (rollback)
That is, each row has a version number, which determines whether the lock is successful based on the version number. It sounds like an optimistic lock. the Innodb implementation method is as follows:
- The transaction modifies the original data in the form of exclusive locks.
- Store the data before modification in the undo log, and associate it with the master data through the rollback pointer.
- If the modification succeeds (commit), nothing is done. If the modification fails, the data in the undo log is restored (rollback)
The most essential difference between the two is: Do I have to lock the data when I modify it? Isn't it MVCC if I lock it?
The implementation of Innodb is really not MVCC, because it does not implement multi-version coexistence of the core. The content in the undo log is only the result of serialization and records the process of multiple transactions, does not belong to multi-version coexistence. However, the ideal MVCC is hard to implement. When a transaction only modifies one row of records and uses the ideal MVCC mode, the transaction can be rolled back by comparing the version number; however, when a transaction affects multiple rows of data, the ideal MVCC data is powerless.
For example, if Transaciton1 executes the desired MVCC and Row1 is modified successfully, but Row2 fails to be modified, Row1 needs to be rolled back. However, because Row1 is not locked, its data may be modified by transaction2, if the Row1 content is rolled back at this time, the modification result of Transaction2 will be damaged, resulting in Transaction2 violating ACID.
The root cause of the difficulty in implementing the ideal MVCC is the attempt to replace the second commit with an optimistic lock. Two rows of data are modified, but to ensure consistency, there is no difference with modifying the data in the two distributed systems. Second commit is the only way to ensure consistency in this scenario. The essence of the second commit is locking. The essence of optimistic locks is to eliminate locks, which are in conflict. Therefore, the ideal MVCC cannot be applied in practice. Innodb only uses the MVCC name, read is not blocked.
5. Summary
It doesn't mean that MVCC is nowhere to be available. For some scenarios with low consistency requirements and scenarios with single data operations, it can still play a role. For example, multiple transactions change the number of online users at the same time, if a transaction fails to be updated, re-calculate and retry until the transaction is successful. Using MVCC will greatly increase the concurrency and eliminate the thread lock.
6. References
- Mysql Official Website
- Http://blog.chinaunix.net/link.php? Url = http://forge.mysql.com % 2 Fwiki % 2FMySQL_Internals
- Understanding MySQL Internals
########################################
The above blog is over. This introduces the storage format of innodb. I found two articles and put them here. Unfortunately, most of the documents I see are not referenced.
Row format of MyISAM and InnoDB
MyISAM has three row storage formats: fixed/dynamic/compressed;
Fixed is the default format and is only used when the table does not contain variable-length fields (varchar/varbinary/blob/text, therefore, it is easy to obtain the specific location of the row on the page. The access efficiency is relatively high, but the disk space is occupied;
Dynamic
Each row has a header containing bitmap to record the columns that are empty (the NULL column is not empty );
Compared with fixed, fixed has the following features:
All string columns are dynamically stored unless the length is smaller than 4;
If the character type is 0 or the number type is 0, the storage space is not occupied. It is marked by bitmap and the NULL value is not included;
If you want to update a row, it is easy to cause the row link to generate fragments after expansion. Once the crash is lost, it is more difficult to recover, and the fixed mode update will not generate fragments;
Compressed can only be created through myisampack and is read-only;
The index file of MyISAM contains a flag to record whether the base table is properly disabled. If -- myisam-recover-options is specified during mysqld startup, the system checks and automatically repairs the table when the table is opened.
InnoDB Row Storage
Innodb plugin introduces Barracuda, which contains two row formats: compressed and dynamic, and the earlier compact/redundant belongs to antelope;
Barracuda VS antelope
Determined by the innodb_file_format (dynamic) parameter. Currently, the optional values are Antelope and Barracuda. The default value is the former. If you want this parameter to take effect,
Because the default value of the shared tablespace is Antelope, to use Barracuda as the default value, you must first declare innodb_file_per_table;
Innodb_file_format is used to control the row format. The global variables can be dynamically adjusted. The default value 5.5 is still Antelope;
The following figure shows the antelope format.
Redundant row Structure
|
Field Length offset list |
Record header information |
Column 1 Data |
Column 2 Data |
.... |
The row header contains the field length offset information, including the variable length and non-variable length. It also contains three hidden columns: RowID (used when there is no primary key)/Transaction ID/Roll Point; compact only supports variable length, saving space;
The redundant row format does not have a NULL flag. For redundant format, if varchar is Null, no space is occupied,However, if char is NULL, space is required.Because it does not have a Null flag;
The record header occupies 6 bytes, one byte more than compact;
For a fixed-length char, if it is NULL, the entire field is still filled, and varchar is Null, it does not occupy space;
The record header information. Compared with compact, the record_type is missing because it contains more black characters.
|
Name |
Length bit |
Function |
|
Deleted_flag |
1 |
Deleted? |
|
Min_rec_flag |
1 |
1 indicates that the record is the pre-defined minimum record. |
|
N_owned |
4 |
Total number of records owned by this record |
|
Heap_no |
13 |
Sorting records of the row in the Index |
|
N_fields |
10 |
Number of columns in the record |
|
1byte_offs_flag |
1 |
Whether the offset list is 1 byte or 2 byte |
|
Next_recorder |
16 |
Relative Position of the next record |
|
() |
1 |
Unknown |
|
() |
1 |
Unknown |
Create table test (t1 varchar (10), t2 varchar (10), t3 char (10), t4 varchar (10) charset = latin1 row_format = redundant;
-- The table has three variable-length columns.
Insert into test values ('A', 'bb ', 'bb', 'ccc ');
Use hexdump-C-v test. idb to view its binary code
-- Length offset list,
Compact row format
|
Field Length offset list |
NULL flag |
Record header information |
Column 1 Data |
Column 2 Data |
.... |
5.0 Introduction
The row header stores the length of the variable-length field in the row. When the column is smaller than 255 bytes, it occupies 1 byte, and when the column is greater than 255 and less than 65535, it occupies 2 bytes; therefore, the maximum length of varchar is 2 to the power of 16-1;
2nd indicate whether the row has a NULL value, occupying 1 byte; the NULL column does not occupy data storage space;
Record header information: a total of 40 BITs in 5 bytes, used to link row-level locks of adjacent record cases
|
Name |
Length bit |
Function |
|
Deleted_flag |
1 |
Deleted? |
|
Min_rec_flag |
1 |
1 indicates that the record is the pre-defined minimum record. |
|
N_owned |
4 |
Total number of records owned by this record |
|
Heap_no |
13 |
Sorting records of the row in the Index |
|
Record_type |
3 |
Row Type 0 = normal 1 = B + node pointer |
|
Next_recorder |
16 |
Relative Position of the next record |
|
() |
1 |
Unknown |
|
() |
1 |
Unknown |
In addition, each page has two hidden fields:
DB_TRX_ID: 6 bytes, which records the latest transaction identifier
DB_ROLL_ID: 7 bytes, pointing to rollback log records
-- If no primary key exists, DB_ROW_ID: 6 bytes will be included in the clustered index.
Create a table in the compact row format
Create table test (t1 varchar (10), t2 varchar (10), t3 char (10), t4 varchar (10) row_format = compact;
-- The table has three variable-length columns.
Insert into test values ('A', 'bb ', 'bb', 'ccc ');
Use hexdump-C-v test. idb to view its binary code
First line
03 02 01-variable length field length list (reverse), the actual order is 01 02 03, which is also the actual length of t1, t2, t4
00-Null flag, the first line does not have NULL
00 00 10 00 2c-record header information, 5 bytes, the last 4 bytes point to the next record next_recorder
00 00 00 2b 68 00-6 bytes rowid, because there is no primary key
00 00 00 00 06 05-transaction ID, 6 bytes
80 00 00 00 32 01 10-rollback pointer, 7 bytes
61-column 1
62 62-column 2
62 62 20 20 20 20 20 20 20 20-column 3, char fills the remaining part
63 63 63-column 4
The remaining data is column data. t3 is filled with 10 bytes due to fixed length;
Row 2
Insert into test values ('D', null, null, 'fff ');
03 01 -- variable length field length list, in reverse order
06 -- Null flag, which has a NULL value and is converted to binary 00000110, indicating that column 2/3 is null.
......
64-column 1 Data
66 66 66-column 4 data,Column 2/3 is NULL and does not occupy storage space.
Note: For redundant format, when varchar is Null, it also does not occupy space, but char is NULL, because it does not have a Null flag
Row Overflow
The Innodb table is IOT and adopts the B + data type. Therefore, each page must store at least two rows of data. If the row is too large, row overflow occurs;
In theory, mysql varchar can store 65525 bytes, which is better than oracle's 4000, but the actual upper limit for InnoDB is 65532, and this value is the total length of all varchar columns in the table; For utf8 character sets, A single character occupies three bytes, and its upper limit is reduced to 1/3;
If a varchar (65535) field is forcibly created, it is implicitly converted to mediumtext if SQL _mode is not restricted;
Whether it is varchar or blob/text, as long as a 16 K page can accommodate two rows of data, it should not overflow;
Once a row overflows, the first 768 bytes of the field are still stored on the current page. The data is generally stored on the B-tree Node page, and the overflow rows are stored on the Uncompress Blob page;
Barracuda uses full row overflow, that is, only the first 20 bytes of the field are retained;
###########################
Difference between MySQL Antelope and Barracuda
Http://www.jb51.net/article/52530.htm
This article mainly introduces the differences between MySQL Antelope and Barracuda. Both Antelope and Barracude are in a file format. For more information, see
Antelope is the file format of innodb-base, Barracude is the file format introduced after innodb-plugin, and Barracude also supports Antelope file format. The difference is:
| File Format |
Supports row format |
Features |
| Antelope
(Innodb-base) |
ROW_FORMAT = COMPACT
ROW_FORMAT = REDUNDANT |
The difference between Compact and redumdant lies in the difference between the first storage content.
The storage format of compact is a list of non-NULL variable length fields.
The redundant storage format is the header of a Field Length offset list (each field occupies the byte length and its corresponding displacement ).
In Antelope, if the variable length field is smaller than 768 bytes, the overflow page is not stored. In some cases, the result set IO is reduced. |
| Barracuda
(Innodb-plugin) |
ROW_FORMAT = DYNAMIC
ROW_FORMAT = COMPRESSED
|
The two are mainly functional differences. In addition, the variable-length field in a row differs from Antelope in 20 bytes, while other overflow pages store data.
In addition, innodb_file_per_table = 1 must be enabled.
(This feature is useful for some optimizations) |
Note:
Note that to use compression, innodb_file_format = Barracuda must be used first. Otherwise, it will not work.
Let's take a look at the differences below:
The Code is as follows:
(Testing) root @ localhost [(none)]> use wubx;
Database changed
(Testing) root @ localhost [wubx]> create table t1
-> (C1 int primary key)
-> ROW_FORMAT = COMPRESSED
-> KEY_BLOCK_SIZE = 8;
Query OK, 0 rows affected, 4 warnings (0.01 sec)
Four warnings are reported. Check whether the error is returned:
The Code is as follows:
(Testing) root @ localhost [wubx]> show warnings;
+ --- + -- + ------------------------ +
| Level | Code | Message |
+ --- + -- + ------------------------ +
| Waring | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format> Antelope. |
| Waring | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE = 8. |
| Warning | 1478 | InnoDB: ROW_FORMAT = COMPRESSED requires innodb_file_format> Antelope. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT = COMPACT. |
+ --- + -- + ------------------------ +
4 rows in set (0.00 sec)
We can see from the above error that compression is not supported. But let's take a look at the table structure as follows:
The Code is as follows:
(Testing) root @ localhost [wubx]> show create table t1;
+ --- + ------------------------------------------------ +
| Table | Create Table |
+ --- + ------------------------------------------------ +
| T1 | create table t1 (
C1 int (11) not null,
Primary key (c1)
) ENGINE = InnoDB default charset = utf8 ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8 |
+ --- + ------------------------------------------------ +
1 row in set (0.00 sec)
This is a pitfall, so pay attention to the use of compression.
The Code is as follows:
(Testing) root @ localhost [wubx]> create table t2 (c1 int (11) not null, primary key (c1 ));
(Testing) root @ localhost [wubx]> insert into t2 select * from t1;
Query OK, 5417760 rows affected (37.12 sec)
Records: 5417760 Duplicates: 0 Warnings: 0
Create tables that support compression:
The Code is as follows:
(Testing) root @ localhost [wubx]> set global innodb_file_per_table = 1
(Testing) root @ localhost [wubx]> set global innodb_file_format = Barracuda;
(Testing) root @ localhost [wubx]> create table t3
(C1 int primary key)
ROW_FORMAT = COMPRESSED
KEY_BLOCK_SIZE = 8;
(Testing) root @ localhost [wubx]> insert into t3 select * from t1;
Query OK, 5417760 rows affected (1 min 10.98 sec)
Records: 5417760 Duplicates: 0 Warnings: 0
The physical size of the table is as follows:
The Code is as follows:
-Rw -- 1 mysql 8.4 K Jul 5 t1.frm
-Rw -- 1 mysql 136 M Jul 5 t1.ibd
-Rw -- 1 mysql 8.4 K Jul 5 t2.frm
-Rw -- 1 mysql 136 M Jul 5 t2.ibd
-Rw -- 1 mysql 8.4 K Jul 5 t3.frm
-Rw -- 1 mysql 96 M Jul 5 :47 t3.ibd
It can be seen that t1 and t2 are not compressed, and t3 supports compression.