One, blow a cow
Interviewer: “Know about MySQL two-phase commit?” Do not know how many people asked cool!
In this article, I’m going to share with you what two-stage commit is all about. Whether you know it or not, trust me! You are sure to get new knowledge in this article!
Before we talk about two-phase commit, we need to know undo-log, redoredo log, and binlog.
Understand them first to better understand what two-phase commit is
Transactions and their characteristics
Before we talk about two-phase commit things, let’s talk about transactions.
Generally, when we have a batch of add, delete, or change in a function, we will add a thing to wrap the set of operations, or the set of operations all successfully executed, as long as one SQL execution failed we will all rollback. I believe you must have heard of this classic Case of transfer. Those of you who have some work experience know that this is actually to protect our database from dirty data. The overall data will become manageable.
For MySQL you can use the following command to display the start, commit, and rollback transactions
Copy# start transaction begin; Start transaction; # submit the commit; # rollback rollback;Copy the codeHowever, programming languages are commonly used to operate databases in daily development. Like Java, Golang… When using frameworks that are specific to the programming language persistence layer, they generally support transaction operations, such as: in Spring you can annotate a method with @transctional to open a transaction. Golang’s Beego also provides functions that you can display to start transactions.
The downside is that while you enjoy the convenience of this programming framework, it also blocks your understanding of MySQL transactions. Makes people too lazy to look at things in detail
You can look at my simple Case below.
I have a data sheet
CopyCREATE TABLE `test_backup` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the codeThen I insert some data into the table
Copymysql> insert into test_backup values(1,'tom');
mysql> insert into test_backup values(2,'jerry');
mysql> insert into test_backup values(1,'herry');
Copy the codeAnd look at the binlog.
Would you be surprised? MySQL > add begin and commit to SQL
The reason is simple: SHARE a parameter as follows:
Most online libraries will set this parameter to ON, your SQL will automatically start an item, and MySQL will automatically submit it for you.
In other words: when this parameter is ON, the SQL that you use the DAO persistence layer framework to send to the database is actually executed in a transaction that is then automatically committed without our being aware of the process. To be more specific, say you use @transctional annotations for a framework, or in Golang you can get a thing as follows:
Copydb := mysql.Client
ops := &sql.TxOptions{
Isolation: 0,
ReadOnly: false,
}
tx, err := db.BeginTx(ctx, ops)
// todo with tx
Copy the codeAnd then all of your operations are performed in this thing.
The persistence framework you are using will send a command to MySQL: ‘begin; ‘or’ start transcation; ‘to ensure that your set of SQL is executed after a SQL, MySQL will not automatically commit things for you.
Setting this parameter to ON is recommended, but you can also turn it off as follows
Copymysql> set autocommit = 0;
Copy the codeHowever, if you turn it off, MySQL will not automatically submit things for you, so it is easy to have long things and create an extremely long undo log chain in memory. There are many disadvantages.
Iii. Take a brief look at the two-phase submission process
Now that you know what a thing is, let’s look at what a two-phase commit is. In fact, the so-called two-phase refers to the submission of an item in two phases. It looks like this.
Above is a sequence diagram of the two-phase submission.
As you can see from the diagram above, when MySQL wants to prepare a transaction, it writes redolog and binlog in two phases.
The first phase of the two-phase commit (Prepare) : Write the rodo-log and mark it as prepare.
And then I write the binlog
The second phase of the two-phase commit (commit phase) : write the bin-log and mark it as commit.
What’s the point of not knowing what these logs are
What is the purpose of two-stage log writing?
Have you ever thought that binlog is turned off by default?
In other words, if you don’t need the features that Binlog brings to you (such as data backup and recovery, setting up a MySQL master/slave cluster), you don’t need to have MySQL write binlog at all, and you don’t need a two-phase commit at all.
Just one Redolog is enough. No matter how your database crashes, redolog records the data in your MySQL memory back to where it was before the crash.
Therefore, the main purpose of two-phase commit is to ensure the security consistency of redolog and binlog data. Only these two log files are logically highly consistent. You can use Redolog to restore the state of the database to the state before the crash. You can use binlog to back up data, restore data, and make master/slave replication. The two-phase commit mechanism ensures that the logic of the two log files is highly consistent. No mistakes, no conflicts.
Of course, two-phase commit can be sufficiently secure if you properly set the fsync timing of redolog and binlog
5, extra meal: sync_binlog = 1 problem
If you understand the following words, you can better understand the two-phase commit oh! Pure dry!
A parameter sync_binlog=1 was mentioned earlier in the binlog sharing article. This parameter controls when the binlog falls and must be set to 1 for your company’s online database.
Notice!!!!!! When this parameter is 1, it means that the binlog will be dropped when the item is submitted.
Now if you take the time in 15 seconds, think about it. The thing in the blue sentence will drop the binlog disk when it is submitted, which is the step1 moment in the picture below? Step2 moment?
The answer: step1 moment!
It’s important to know this, so let me describe a scenario.
If you were to execute an UPDATE statement, you would know to write redolog first (for subsequent rollback of update transactions). Your UPDATE logic then changes the cached page in the Buffer Pool to dirty.
When you are ready to commit something (step1 phase), redolog is written and marked as the Prepare phase. And then write the binlog, and drop the binlog.
Then something happened. MySQL went down.
When you restart MySQL, will update changes to BufferPool be rolled back or committed?
The answer is: the modified recovery will be produced according to Redolog and then submitted.
So why do you do that?
In general, whether mysql crashes, commits or rolls back depends on whether mysql can determine whether binlog and Redolog agree logically. Commit as long as the logical agreement is reached; otherwise, rollback is required.
For example, in the scenario described above, binlog is already written, but MySQL eventually chooses to roll back. That means your binlog is one more update than the actual data in the BufferPool (or Disk). If you use this binlog to recover data later, is it always wrong?
How to determine whether binlog and Redolog agree
This knowledge point but pure dry goods!
When MySQL finishes writing to redolog and marks it as prepare, it records an XID in redolog that uniquely identifies the transaction globally. When you set ‘sync_binlog=1’, mysql will correspond to the binlog and flush it directly to disk after writing redolog in the first stage above.
The following figure shows binlog records in row format on disk. There is also an XID at the end of the binlog.
As long as the XID is consistent with the XID recorded in Redolog, MySQL will assume that binlog and Redolog are logically consistent. If only the XID is recorded in rodolog and not in binlog, MySQL will RollBack
The original intention of two-phase commit design – distributed transaction
Two-phase commits are more commonly used in distributed transaction scenarios.
I’m going to describe a scene like this in plain English, and you can imagine it for yourself:
MySQL standalone originally supports transactions, but the so-called distributed transactions here actually refers to cross-database, cross-cluster transactions. Let’s say your company’s business is so hot that it generates so much data every day that it can’t be stored in a single table or even a single repository (it has reached the bottleneck of server hardware storage).
So what do you do? Can only split single library into many libraries?
Then you have a new problem when you break it up into multiple repositories. Suppose Tom transfers money to Jerry, but because you split the database, you split Tom’s and Jerry’s information, which was in the same database and table, into table A in database A and table B in database B. And when you initiate the transfer logic again, in case it fails. How do ROLLBACK ensure data security? This is the problem with distributed transactions.
Most companies have their own middleware that supports distributed transactions, which essentially handles two-phase commits between database nodes.
Simply put: middleware to coordinate each data node.
Phase 1: The middleware tells each database node to start XA transactions and then determines whether all database nodes have been prepared
Stage 2: The middleware determines whether the transaction is committed or rolled back. Commit if all nodes are prepared. If a node fails, the system rolls back the node.
This is just a brief mention of the origins of two-phase commits and distributed transactions.
MySQL > select * from ‘MySQL’
Let’s get back to the MySQL two-phase logging.
As you can probably imagine, the two-phase commit from the previous article was ostensibly a two-phase write to the log.
From my previous description, you must also know that the log-aligned tags of the two log files have the same XID.
It is this two-phase mechanism that ensures that two logs (multiple data nodes in the case of distributed transactions) are logically consistent.
Nine, leave an egg
If you think about it, the example scenario I described in Part 3 above when sharing sync_binlog=1 is actually a simple scenario for standalone MySQL.
The scene could have been more complicated!
Cascading MySQL clusters, synchronous, semi-synchronous, asynchronous master-slave replication relationships, and here’s two-phase commit, log drop timing, ghost transactions! It works better in one scene.
Three things to watch ❤️
If you find this article helpful, I’d like to invite you to do three small favors for me:
-
Like, forward, have your “like and comment”, is the motivation of my creation.
-
Follow the public account “Java rotten pigskin” and share original knowledge from time to time.
-
Also look forward to the follow-up article ing🚀
-
[666] Scan the code to obtain the learning materials package
