mvcc&purge& Distributed Transactions

Ⅰ, MVCC Introduction

Give me a chestnut.

session1:([email protected]) [test]> select * from t;+------+| a    |+------+|    1 |+------+1 row in set (0.00 sec)([email protected]) [test]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [test]> update t set a = a + 1 where a = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0([email protected]) [test]> select * from t;+------+| a    |+------+|    2 |+------+1 row in set (0.00 sec)session2：([email protected]) [test]> begin;Query OK, 0 rows affected (0.00 sec)([email protected]) [test]> select * from t;+------+| a    |+------+|    1 |+------+1 row in set (0.00 sec)

The first session opens the transaction update record, not commit, at which time the record is locked

Open a new session to select this record, and will not be blocked because of a lock, read the original record

This is the mvcc--multi-version concurrency control, the implementation principle is undo, no additional overhead, read this record is updated read the previous version of this record

After this commit, the previous version of Undo cannot be recovered immediately, because the other threads may still be referencing the previous version of Undo, and the real recycle undo is the purge thread

Ⅱ, purge thread 2.1 purge introduction

The role of purge is to remove undo and really Delete a record (complete update and delete)

delete from table where pk=1；在page中只是标记为删除,page上并没有真正的删除相关参数：innodb_purge_threads    默认是1,5.7中设大一点,4或者8,都是ssd性能比较好

• All purge operations were made by master thread before 5.5.

  The default is only one purge thread

  INNODB_PURGE_THREADS={0|1}

• 5.6

  N Purge Thread

  INNODB_PURGE_THREADS={4}

2.2 Purge specific process

1024 slots------1024 undo rollback segments, each corresponding to a different undo log

Once the transaction is committed, Undo is placed in the Hitory list

Tips

Because records are not ordered, purge operations require a large number of discrete read operations

2.3 On-Line FAQs

Undo is constantly increasing, not effectively recycled, resulting in increased system space,

The main reasons are two:

• Index not added

  Check slow Log

• There is a big business

  Big break.

In fact, a transaction execution time is very long, then the corresponding undo can not be recycled, at least after the completion of the commit to recover

In addition, rollback is much slower than commit, commit quickly, the time required for rollback is the time of transaction execution, logical rollback

Tips

Currently MySQL Support Online recovery undo, see Ali Database Core Monthly

Ⅲ, distributed transactions

Before we talked about the consistency of binlog and redo is guaranteed by an internal XA transaction, here's a simple chat about the external distributed transaction

3.1 See the simple syntax

([email protected])    [test]> xa start ' a '; --Open a Distributed transaction query OK, 0 rows Affected (0.00 sec) ([email protected]) [test]> insert INTO T values (2000); Query OK, 1 row affected (0.09 sec) ([email protected]) [test]> insert into T values (3000);  Query OK, 1 row Affected (0.00 sec) ([email protected]) [test]> xa end ' a ';  --end query OK, 0 rows Affected (0.00 sec) ([email protected]) [test]> xa prepare ' a ';  --Write Preparequery OK, 0 rows affected (0.03 sec) ([email protected]) [test]> xa recover; --Take a look, have a distributed transaction +----------+--------------+--------------+------+| FormatID | Gtrid_length | Bqual_length |        Data |+----------+--------------+--------------+------+|            1 |            1 | 0 | A |+----------+--------------+--------------+------+1 row in Set (0.00 sec) ([email protected]) [test]> xa rollb Ack ' a ';  --rollback query OK, 0 rows affected (0.01 sec) ([email protected]) [test]> xa recover; --Look again, No. Empty Set (0.00 sec)

This is simulated on a single instance, with little meaning.

Two instances in a real application do distributed transactions, requiring both sides of the prepare to be successful before they are finally committed

3.2 Imperfections in distributed transactions

• Client exit causes prepare successful transaction loss
• Binlog lost due to MySQL server outage
• External XA prepare successfully writes no log

Ⅳ, transaction programming 4.1 bad things habits

• Commit a transaction in a loop (too many times fsync)
• Use Auto-commit
• Use automatic rollback

create procedure load1(count int unsigned)begindeclare s int unsigned default 1;declare c char(80) default repeat(‘a‘,80);  while s <= count do    insert into t1 select NULL,c;    set s = s+1;  end while;endcall load1(1000)

The call to the above stored procedure, Auto commit causes the insert to be punished 1000 times Fsync

Correct posture:

begin;call load1(1000)commit;

• The wrong kind if the middle failure rollback can not be back, do not get atomic
• Writing a transaction to a stored procedure is also not good, the error is not easy to get, can not automatically rollback, so the stored procedure only write logic, transaction control application to do

4.2 Major business

Transaction demolition is very small, the reason is binlog in mischief, in fact, not necessarily a big business, large operations are to be dismantled

Calculate interest, split the batch execution

update accountset account_total = account_total + (1 + interest_rate)

Why did you dismantle it? A cliché,

• Write Binlog cost, leading to master-slave delay
• Avoid too big an undo

Off Topic:

Binlog is a bit annoying not like Oracle with redo, historical reasons, bad to say

Also good, big data platform integration is very simple, the MySQL data on the real-time to push the large data platform is too simple, GitHub on a lot of projects directly with

mvcc&purge& Distributed Transactions