Undo and MVCC, crash recovery

• undo Features : avoid dirty reads, transaction rollback, non-blocking reads, MVCC, crash recovery
• Transactional Workflow (Figure 2)
• MVCC principle Mechanism
• Crash recovery : Redo Roll forward, undo rollback
• long business, large business : Hazard, judgment, treatment
• Undo Optimizations : implementing undo Detach, shrink undo table Space

0. Undo Physical Storage Research

1>ibdata Fifth data block (System Transaction table) stores the address of the segment header block of 128 undo segments

2> each undo segment header has 1024 rows, two rows record one transaction, and a total of 512 transactions can be recorded

3> XID, rollpointr in a data row

4> a data row is modified

1. New Transaction ID

2. The new rollpointr

3. Modified data

The above three pieces of data will be entered into the rollback block. See more: Transactional workflow ...

First, undo features

1. Avoid dirty reading

1> before any data is manipulated, the data is first backed up to the undo page before the data is modified;

2> cannot see data not submitted by other sessions;

3> when you want to read the data rows of a modified data page, you point to the data that is backed up in the Undo page, avoiding dirty reads.

2. Rollback of a transaction

Undo the most basic role is rollback, the old data first put in the undo inside storage, and so on rollback when the undo data back back.

3. DML does not block read

To increase concurrency, if another user is modifying a page of data, the transaction is not committed, it is now necessary to read the data page and find that the transaction is not committed, based on the rollpointer on the data row to find the original data (on the Undo page), combined with the data page to return the data to the user.

4. MVCC (Consistent reading)

Multi-versioning Multiversion Concurrency Control

5. Crash Recovery (rollback)

Automatic rollback of uncommitted transactions;

Redo roll forward, undo rollback, uncommitted transaction active rollback, uncommitted transaction information is written in the transaction slot. Database during the run, suddenly collapsed, after the database started, need to redo forward, there will be many uncommitted transactions (the transaction session is broken, it is impossible to continue to complete, you need to rollback the uncommitted transaction) also rolled back: READ UNCOMMITTED transaction transaction slot information, the UNCOMMITTED transaction rollback.

Ii. Transaction Workflow: Storage structure

1. Assign a transaction ID, increment the transaction ID in turn

2. Assign a transaction slot to write transaction information to the transaction slot

3. Start modifying data rows, store transaction ID in data row, rollback block address used by pre-modified data

4, the rollback block to store the data before the modification

5. Each rollback block belonging to a transaction is linked together

6. The address in the header block of the rollback segment points to the last rollback block in the rollback block list

7, a rollback block can only hold one transaction of data

8, transaction commit is to change the transaction state to committed in the transaction slot

Three, MVCC principle mechanism

not deep in understanding (turn from: http://www.cnblogs.com/chenpingzhao/p/5065316.html)

1, MVCC of several characteristics:

1> There is one version per row of data, which is updated every time the data is updated

2> Modify the current version of the copy at random, no interference between the transactions

3> Compare version number when saving, overwrite original record if successful (commit), discard copy (rollback) if failed

That is, each line has a version number, save according to the version number determines whether the success, sound contains the flavor of optimistic lock;

2, non-blocking read InnoDB implementation mode:

4> transaction modifies raw data as an exclusive lock

5> storing the pre-modified data in undo log, which is associated with the master data by a rollback pointer

6> Modify Success (commit) do nothing, fail to recover data in undo log (rollback)

3. Different understanding

The most essential difference is whether to lock the data when it is modified;

InnoDB implementation is really not MVCC, because there is no multi-version of the core coexistence, undo log content is only the result of serialization, the process of recording multiple transactions, not part of the multi-version coexistence. But the ideal MVCC is difficult to implement, and when a transaction modifies only one row of records using the ideal MVCC pattern is no problem and can be rolled back by comparing the version number, but when the transaction affects multiple rows of data, the ideal MVCC is powerless.

For example, if Transaciton1 executes the desired MVCC, the modification Row1 succeeds, and the modification Row2 fails, the Row1 is rolled back, but because Row1 is not locked, the data may be modified by Transaction2, and if the contents of Row1 are rolled back at this time, Damage to the Transaction2 results, causing Transaction2 to violate acid.

The fundamental reason why the ideal MVCC is difficult to achieve is the attempt to replace the two-paragraph submission with optimistic locking. Two rows of data are modified, but to ensure consistency, there is no difference between modifying data in two distributed systems, and two commits are the only means of ensuring consistency in this scenario at this time. Two paragraph of the nature of the submission is locked, the essence of the optimistic lock is to eliminate the lock, the contradiction between the two, so the ideal MVCC difficult to really be applied in practice, InnoDB just borrowed MVCC the name, provides read non-blocking only.

Iv. Crash Recovery

Redo roll forward, undo rollback ...

1, two guarantees

The 1> database guarantees that all redolog of committed transactions are written to redo logfile

The 2> database ensures that all dirty blocks are redolog redo logfile, and that only the dirty blocks are written to the disk, redo log can be overwritten

Conclusion: Redo log has sufficient capacity to construct the dirty blocks.

2. Redo log how to determine which logs to use to construct dirty blocks

1> starting point: Checkpoint start

A flush list link exists in the 1.INNODB buffer pool

2. The redo log that corresponds to the oldest end of the list is the starting point for the future database crash Recovery Redolog roll forward

3.clean thread periodically will need to flush list the oldest dirty block corresponding to the redo log address written to Ibdata

2> end point: Redo log Current last log

3. The process of crash recovery

The first stage is roll forward :

Roll forward corresponding redo log start and end has been determined: Redolog not afraid to run, because Redolog has a version, the data block version, if the Redolog than the data block is older, the use of empty running way

The second phase is rollback:

A transaction that is not committed at the time of the crash is also rolled back, and the transactions are all dead, because the corresponding user sessions have ended, the corresponding data blocks have been read, the data block has uncommitted transactions, the transaction information corresponding to the uncommitted transaction has been read, and the data block has been actively rolled back.

Encounter a dead transaction corresponding to the data block, who uses who to roll back.

V. Big business, long business

1. Harm of long business

Start a transaction, do not commit for a long time, all the data need undo to save, may produce a lot of undo data, and can not be emptied overwrite, persisted to the transaction commits. Very serious.

2, the harm of large business

Modify the bulk of the data, take up too much of the undo page (the resulting undo data is mainly delete generated, but MySQL optimized delete, add the DELETED_FLAG flag bit, reduce the use of undo), so the harm is not very large, And the normal transaction scenario does not appear to be large.

3, how to judge the big business and long business

Mysql> desc INFORMATION_SCHEMA. Innodb_trx;

Key parameters:

1.trx_started: The time at which a transaction begins, if the time is far worse than the current one indicates a long transaction

2.trx_rows_modified: The number of rows modified by the transaction, if the value is large indicates a large transaction

3.trx_mysql_thread_id: The corresponding thread ID of the firm (kill thread Cleanup transaction)

4, solve the difficult big business, long business

Deal with large transactions: Kill-9 mysql_process_id: Deal with large transactions, directly kill the MySQL instance, will not take the initiative to roll back the speed block, and then restart. (unless forced to do so, the production environment to restart the server is a huge thing)

Handle Long transactions: If the start time is not very long and the number of rows is not many, kill the thread that is on the transaction directly. (Kill in database, may slow reaction)

Vi. Optimal handling of undo

1. Realizing undo separation

MySQL5.5 and before, in addition to the natural increase in the amount of data, once a large transaction, the use of the undo log occupied by the space will remain in the ibdata1 inside, even if the transaction is closed. as the database goes online more and more, the Ibdata1 file becomes larger and larger, and the physical backup files are growing ...

MySQL 5.6 Adds the following parameters to remove the Undo log from the IBDATA1 and store it separately.

Mysql>Show variables like '%undo%';+--------------------------+------------+|Variable_name|Value|+--------------------------+------------+|Innodb_max_undo_log_size| 1073741824 ||Innodb_undo_directory|./||Innodb_undo_log_truncate|  on         ||Innodb_undo_logs|  -        ||Innodb_undo_tablespaces| 3          |+--------------------------+------------+5Rowsinch Set,1Warning (0.00Sec

1> innodb_undo_directory:

Specifies the directory that holds the undo tablespace separately, by default. (That is, datadir), you can set a relative path or an absolute path. Although the parameter instance initialization cannot be changed directly, the parameter can be modified by first stopping the library, modifying the configuration file, and then moving the undo tablespace file.

2> innodb_undo_tablespaces:

Specifies the number of undo table spaces that are stored separately, for example, if set to 3, the Undo table space is undo001, undo002, undo003, and the initial size of each file defaults to 10M. The instance of this parameter cannot be changed after initialization;

3> innodb_undo_logs:

Specifies the number of rollback segments (earlier versions of which the parameter name is innodb_rollback_segments), which is 128 by default. Each rollback segment can support up to 1024 online transactions at a time. These rollback segments are evenly distributed across the undo table space. The variable can be dynamically adjusted, but the physical rollback segment is not reduced, but only the number of rollback segments used is controlled.

Operation Undo Detach: In practice, before initializing an instance, we only need to set the Innodb_undo_tablespaces parameter (which is 3 or greater) to set the undo log to a separate undo table space.

2. Online Shrink undo Table Space

MySQL 5.7 introduces a new parameter,innodb_undo_log_truncate, can be opened after the online contraction of the undo table space, support dynamic settings.

1> to achieve online shrinkage undo conditions

1.innodb_undo_tablespaces>=2: Because the file is in inactive state when truncate undo tablespace, if there are only 1 undo tablespace, then the whole system will be in an unusable state during this process;

2.INNODB_UNDO_LOGS>=35 (default 128): Because in MySQL 5.7, the first undo log is always in the shared tablespace, and the other 32 undo logs are assigned to the temporary tablespace (that is, IBTMP1), at least 2 undo Log to ensure that there are at least 1 undo logs in each of the 2 undo table spaces;

2> meet the above 2 conditions

Innodb_undo_log_truncate=on, you can turn on automatic truncate of the undo table space

1.innodb_max_undo_log_size: Undo tablespace file exceeds this value is marked as retractable, the default 1g,truncate after the space shrinks to 10M;

2.innodb_purge_rseg_truncate_frequency: Specifies how many times the purge operation is aroused before releasing rollback segments. The Undo table space is truncate when the rollback segments in the Undo Tablespace is released. (Maximum is 128, minimum is 1, default is 128) the smaller the parameter, the more frequently the undo tablespace is tried truncate.

Undo and MVCC, crash recovery