 And was that in the morning session? Like one of you mentioned the XA session of this, and this is bad session. So Nisha will be presenting about the enhanced XA support for the replication in MySQL. Oh, oh, MySQL. Yes. Yeah. Here you go. Good evening, everyone. My name is Nisha. I'm part of the MySQL team in Oracle. And I'm based sort of Bangalore, India. I've been with MySQL for about more than six years now. And today I'll be discussing about the improvements that has been made in 5.7 for the XA support. As Venkat mentioned, again, I have reference to replication in my slide deck too. So I think by now this would be by heart, so I'm going to skip this slide. So today I would be discussing about what is XA, the protocols that is used by XA, and how does MySQL go about supporting the XA standard, and how do you perform an XA transaction within MySQL, and also discuss about the different state transition when you perform an XA transaction, how it goes through. And then I would discuss about the improvements that has been made for XA transaction recovery and how we have ensured that it works well with replication. And also I would discuss about the improvements that has been made in 8.0. So XA stands for the extended architecture. It is a standard developed by the open group for distributed transaction processing. XA addresses the problem of addressing the asset properties within a single transaction across distributed set of resources. As discussed by my colleague in the morning, the distributed set of resources can be message queues, different database servers, or even the file system. So this describes the standard for interaction between the global transaction manager and the local resource manager. I wouldn't get into the details of the use case because it's already discussed in the morning. So the components involved, you have the global transaction manager where the logic can be embedded into the application. And then you have your local resource managers, which can be different database servers. And then the transaction manager initiates your XA transactions and the resource manager handles all these XA transactions. So what is the protocol used by XA? It uses a two-phase commit protocol where the first is the request to commit and followed by the actual commit. So you perform all your read-write operations and that's when your two-phase protocol kicks in. During the first phase, the global transaction manager sends a prepared to commit to all the branches involved in the XA transaction. And then once it receives a positive response from all the individual branches on a request to commit is sent to all the branches involved. If at all any single branch receives, I mean sends a negative response, then the transaction is aborted. So there was, I mean, XA transaction is generally requested by the Java community. MySQL first introduced the XA support in 5.0, but we have made a lot of improvements in 5.7. And as seen in the previous diagram, the MySQL server acts as a local resource manager and the clients connecting into the MySQL server acts as a global transaction manager. All the XA transactions begin with the keyword XA and it is identified by a global transaction identifier which is mandatory. The optional identifiers are the branch identifiers in order to identify the individual branches within the global transaction. And then you have a format specifier in order to specify what the format was used for the previous two components. Most of the time it's string, but if you want to avoid the confusion of the cassette, preferably in the hex format, and then you can specify the hex format was used. Together it's called as the XID. So as mentioned, you have the transaction manager and then you have your MySQL server and then you initiate this transaction using XA start or begin. And here the XA test. So the XA test is the global transaction identifier and then you perform the read and write statements. Then you end the transaction using XA and XA test. And so as I mentioned previously, you have two phases that is first, you send a request to prepare for commit followed by the actual commit. So when you do the first phase is the XA prepare XA test, you're preparing the transaction for commit. In case you see a positive or a negative response, if it is positive, you do XA commit or you roll back the transaction. And you can see in the dotted lines the XA recover and XA recover is when see your connection, your transaction connection is disconnected. And then you want to discover all the pending transactions which have not been committed. Then XA recover is issued in order to discover all the XIDs which are pending in a prepared state and required to be committed. So as described, XA start or begin starts an XA transaction. XA end specifies the end of active transaction. So you have two options now, XA commit one phase. The one phase is optional. XA commit one phase do not take the two phase commit. It actually prepares and commits within a single step. And in case you want two phase commit, you would utilize your XA prepare, which prepares the transaction for commit. And then you would issue a XA commit which would then commit the transaction. XA rollback, in case the feedback from your local resource managers is negative, then you would actually want to roll back the transaction and you can do that by XA rollback. As mentioned, the XA recover displays the information about all the prepared transaction. And this is nothing but the XID information which can be later utilized in order to commit or roll back the transaction. So this is the state transition of an XA transaction. Say you do a XA start. It puts that into an active state. And then you do a read write. And then you end the XA transaction. It moves it into an idle state. And in case you don't want to go for your two phase commit, you would actually do a XA commit one phase and it moves it into a committed state. Now in order to retain the asset property, say you want to actually go for XA prepare. And then it moves it into a prepared state. And then in case there is a failure from any individual branch involved in the single transaction, then XA rollback is issued. So as described, XA start puts the transaction in an active state. And once all the statement are executed by the active transaction, an XA end puts it into an idle state. Now you can either issue a XA prepare or you can issue an XA commit one phase. I had described this previously. So what are the improvements that have been made in 5.7 for XA transaction recovery? Prior to 5.7.7, if you had a prepared transaction and the client disconnected or a server exited gracefully, what happens is the transaction would have been rollback. Ideally, the XA standard describes that any prepared transaction should be recovered and committed or rollback based on the response from the individual branches involved. So the improvement that has been made in 5.7.7 is all the prepared transactions are marked, specifically by InnoDB and retained in the transaction cache so that when the server comes back up, you can discover these transactions and then perform the commit or rollback. As you can see, XA prepare test and your server is killed and then you have another client connection starting and then you perform a XA commit test and it says unknown XID. And then you would actually perform the XA recover and obviously it does not give any information, any pending XID because it's already been rolled back. Now let's look at after 5.7.7. What's been done is if you look, the client is killed and then you do a XA recover and you can see the test that being your global transaction identifier and the data length. And then you could actually go ahead and commit the transaction, which was not possible previously because you could, the information did not exist and it was rolled back. So the improvement that's been made, what happened was prior to 5.7.7, if your XA transaction was on a prepared state and the server exited abnormally and you could actually resume this transaction, you could actually do XA recover, get the information. But then you do a XA commit of that particular transaction, it would not be logged in the binary log. So this caused the master and the slave to be not in sync. So here you try to do XA recover. Here is where you kill the server and then you try to recover what is XA test and you can commit. On your master, you can commit the transaction. But when you do a show bin log events, you can see it's actually empty. But on the server, if you can see, the table has been updated. Let's look at after 5.7.7. So you have killed the server and then you get the information about the pending XA transaction and then you perform your XA commit. I think someone you had asked how would you figure out the statements within the bin log. So you can do a show bin log events and if you notice, you have the insert. And yeah, all the statements until prepared is actually retained within the binary log and you can also see that the XA commit is also retained. So how does this work? Is we now log the XA transaction in two different phases. All the transaction until XA prepare is logged with a separate event and it is the XA prepare log event and with a separate GTID. And then during the second phase, the XA commit or rollback is again logged with a separate GTID. This allows the transaction to be interleaved and hence there is no confusion on the slave side as well. And this does work well when the GTID is on and you turn off your binary logs since you don't want it to be replicated. So the improvement made in a.o is, as I said, XA recover is actually used to discover all the transactions which are pending on the server, which is in a prepared state, but not really committed. Then again, any user which is connected to the server was able to discover information about all the pending transaction, not just his phone. So this is a security issue. And what we have done is introduced a new privilege called as XA recover admin. This ensures that any user with this privilege would be able to discover all the information about the pending transactions, which require to be committed. So you can look up the XA standard, which is published by the open group. And also the MySQL documentation is a good source for information about the XA. And also you can look up my blog on whatever is discussed here. And we currently do not support XA resume or join. And if it is important for the user community, we request you to come and write a comment in one of the blogs. And we could take a look. Any questions? OK. Thank you. Thank you.