 Hi, welcome all welcome to the second MySQL presentation today my name is Ajo Robert I work for the MySQL server team in Oracle. So, today we will talk about two topics two different topics first one is not much explored one feature which was there for long time second is a newly introduced feature in MySQL. So, safe harbor statement that is how few seconds here. So, today is agenda we will go to we will understand what is a distributed transaction the basics I will go through the basics later fast and we will see the XA standard which is in which is an industry standard for XA and how MySQL is participating in it or we can how we can use MySQL as part of XA. And the second part of the talk is about the NoSQL story from MySQL how we can use the NoSQL syntax with MySQL at the same time where we can use the SQL as well on the same data. So, before starting some basics what is a transaction? Transaction is nothing but a few set of statements we need to execute or few set of actions we need to execute to achieve a task. So, what is the importance of being a proper transaction we need asset properties that is what we are looking for. So, we are all from database so we know the importance of asset right. So, what about distributed transaction? How is it different from regular transaction? Regular transaction we talk about is when it is executed in single instance or one machine distributed transaction is where it is executed across machines whether it could be geographically distributed machines or it could be heterogeneous machines it could be functionally different. But what we need? We need asset properties on the transaction which is performed across these nodes. So, how can we do it? There are there could be different ways to do it there is one of the way is the XA transaction XA protocol which is from open group. So, we will see how is it and what is it before going there let us see how popular is it? It is been adopted by different functional and logical entities like databases file systems, message queues considering database world it is been supported by most of the famous databases including MySQL, Oracle, DB2 and other system among other systems. So, the basics XA standard uses the two phase commit protocol the famous two phase commit protocol to ensure the asset properties. So, for those who do not know the two phase commit protocol. So, what we do is once a transaction board is over we go for two phase commit that means we execute the prepare transaction prepare on all the participating nodes. So, once the prepare is successful on all the nodes we go for a commit on all the nodes. The property of prepare is that if prepare is successful the commit can never fail it is a nodes responsibility to make sure the commit is successful if prepare is success. So, if the prepare is fail on one node we will execute rollback on all the nodes. So, how to achieve the success of commit after prepare success that is the responsibility of individual nodes implementation I mean it is up to the node it can implement in whichever way it want whatever logging mechanics and double write or whatever mechanism it want to use. So, what are the modules in this XA topology three parts one is the application which which want to perform the XA transaction across nodes. Second one is a transaction manager which performs the two phase commit on behalf of the application. Third one is a resource manager which performs actual operation on a resource whether it is file system database or message queue. So, my SQL will act as a resource manager only as a resource manager. So, whoever want to use XA with my SQL should either implement the transaction manager as part of the application or you can use a independent transaction manager which is available in the market. So, how do we go the general use case is there is no point in having one resource manager when it is XA because XA is all about across node right. So, we will have multiple resource managers and we need to execute one transaction and transaction manager is pretty simple. So, most of the times you can implement that task part of your application. So, let us consider one use case the application level sharding. In my SQL you have to use sharding at times due to geographic constraint or scalability constraints or whatever reasons you have to implement it. So, what if you have to execute a transaction across the nodes as it comes in transaction across the nodes that is going to be nightmare right I mean if it is possible with XA it is super easy super simple. So, we will we will see an example later, but this is one use case where XA is very much relevant. Another one is a heterogeneous transaction you want to have a transaction which is across a file system database or a message queue I mean I picked two from the market which supports XA standard. So, nothing to nothing like a recommendation, but this is another use case where XA is very useful. So, let us see an example in this case we have two MySQL instances that is a one is in India one is in US. So, we distributed the user accounts depends on that geography and due to some reason we have to move one user from one account to another one I mean one instance to another one. So, we can have a asset complaint transaction to do that. So, what do we do we start the transaction with XA start command whatever commands there you see which starts with XA is the transaction manager commands. If you have a transaction manager you want to implement transaction manager as part of your application these are the XA commands you will be using. If you have a dedicated transaction manager then this will be taken care by the transaction manager. So, but as per the standard this is how it goes. So, you define your transaction body with XA start and XA end that is a transaction body and you execute whatever you want within that. So, any number of resource manager can join at that time and you can perform the operation you can have a independent connection to each resource manager and execute the commands which what you want once that is done you execute prepare. Here is important you have to wait the success from all the resource manager prepare. If you get success from all the resource managers go for commit on all the nodes that is it you have a transaction complete which ensures the asset properties. A short recap what we seen is what is how is a distributor transaction and how MySQL performs MySQL involves in XA transaction and how we can ensure asset properties across heterogeneous or geographical distributor nodes and some use cases with examples. So, those who are interested more about XA there is another session by my colleague Nisha at 330 you can attend that we have more details there. So, the second portion is about document store we will talk about it. So, when a new application or website designs we have these questions right what to choose from. Generally we have these requirements like we need rigid schema we need Jo-in we need asset properties we need foreign keys there are so many requirements we consider, but the new era of people who are developing this they want different set of they have different set of requirements. They need flexible schema they need key value store they want to store the JSON object they have naturally as part of the JavaScript framework they will be using and there is complete set of different requirements. So, always we get this question. So, what do you go with a DBMS or a NoSQL solution. So, what if we do not have to make that choice from MySQL version 5712 we have MySQL document store which is available in the community edition of MySQL which supports JSON data store and the NoSQL kind of syntax for all CRDU operations and it is available on all the famous interfaces API interfaces and the new MySQL as well. And the JSON here supports all the basic data types and there are some extended data types as well available. You can check the documentation for more details on the supported data types and availability on that, but let us see how it looks like. So, this is the syntax this is how you created document collection I mean the similar time for the NoSQL world for tables. So, DB this is the new MySQL cell and DB is a object JavaScript object I am using the JS by default it looks in as JS interface you can use JavaScript syntax here. So, JS is a DB is a JavaScript object associated with the current database I mean the schema and the system MySQL instance. So, you create use create collection to create a document collection. So, here we are going to create a superheroes collection. So, before going to have some superheroes let us see what happened to our internal MySQL right. This is the NoSQL interface we have seen. So, it was MD database. So, when you you can easily I think you have noticed the first slash SQL. So, it will switch to the SQL mode suddenly in the shell itself. So, you execute all the SQL commands here. So, we see we see a table with the same name and the schema is it has one real column and one generated column on the ID field I will talk about it later. Let us have two superheroes one from the SQL interface and one from the NoSQL interface. As you can see the SQL interface syntax is super easy as you can see is a familiar NoSQL syntax we use and you can pass the old JSON object as it is to the NoSQL interface. At the same time you can use the familiar SQL interface to insert documents there. One thing you may have noticed is the SQL interface as a ID that is a mandatory field if you are inserting using a SQL interface. In case of NoSQL we generate it internally. This ID is being used as key value pair key for the key value pair use case of NoSQL. So, if you provide it in the NoSQL interface we do not create it we use that as a key for this particular JSON object. If not one will be created because we have a index on that field. So, how do we retrieve data? So, SQL we know we can do a select regular select we will it will return the normal JSON objects or we can go for JSON table to get it in a much more comfortable way or you could create a view with the fields you are interested in and you can use it with a regular joins or whatever way you want to use it. So, this is kind of you want to make one part of the schema rigid for the regular SQL use case while the other portion is JSON for the NoSQL interface which is flexible you can add any field but this part of the schema will stay as it is. So, this is way you can ensure that. So, NoSQL it is very easy superheroes dot find will return all the columns but it is not what we want we need filters it is available there is lot of filtering options available one example I have shown here there is limit and all other set of filters available you can see the documentation for set of available filters I have just showed for example. So, I am skipping delete and update because it is similar but another question which will come to mind is since it is being powered by a SQL databases integrated constraints can we enforce it let us try that. So, since we have superheroes which are from DC and Marvel let us try to enforce that we need superheroes only from DC and Marvel. Let us have a table with publisher and we created a generated column on the superheroes table and yeah I missed to tell you this the table superheroes you can add any number of additional columns it still will be available in the NoSQL interface you can have generated columns or you can have your own other columns which you want to use in the SQL interface till the table will be available as a document collection in that NoSQL interface. So, we create a generated column and we add a foreign key with our newly created table and let us try to insert a known DC no Marvel superhero is Superman Shaktimaan and is a Indian superhero and it is from Dash comics ok let us see what happens to this command. So, clearly it shows a error saying that there is a integrated constraint failure. So, that is it we have the integrated constraint available in NoSQL at the same time we can use this capacity in the SQL interface as well. So, primarily that is it the summary we have a powerful NoSQL solution from MySQL which works great and the data is available via SQL and NoSQL at the same time and whatever the power and capacity available with MySQL you can achieve or get that via NoSQL interface as well. Thank you. Thank you. That is it. So, we have not done that benchmarking yet, but you are welcome to try it. So, thank you.