Sharding and Transactions with MySQL
mysql, sharding, transactions
Solution
It is possible to do this with distributed transactions. They are supported by the InnoDB storage engine. You will find more information about them and about the syntax of the commands in the MySQL documentation: XA Transactions
I advise against using them directly. If consistency is the most requirements for ypur application, then use a transaction monitor that can take care of it. Java EE does that for you.
However, if availability is more important than consistency, you should avoid distributed transactions. The CAP theorem explains why.
Problem
With sharding, how can you maintain a reliable transaction across multiple database servers? For example, if I had a table named `AccountLedger` on one database server (MySQL instance) and a table named `User` on another database server, is it possible to run a transaction across both database instances that will both reliably commit, or rollback on failure? Example transaction: AccountLedger database server: ``` START TRANSACTION; INSERT INTO AccountLedger SET UserID = @UserID, Date = @Date, Debit = @Debit, Balance = @Balance; ``` User database server: ``` START TRANSACTION; UPDATE User SET Balance = @Balance WHERE UserID = @UserID; ``` AccountLedger database server: ``` COMMIT; ``` User database server: ``` COMMIT; -- What happens if the COMMIT fails here (power goes out or whatever) ``` I've read a quite a lot about sharding, but I can't seem to find any information on using transactions with sharding. Can someone point me in the right direction?