Sharding and Transactions with MySQL

Question

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?

Answer 1

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.

Answer 2

You can implement cross shard serializable transaction on the client side if each shard supports per key linearizability and compare and set (which is true for MySQL). This approach is used in Google's Percolator and in the CockroachDB but nothing prevents you from using it with MySQL.

I've created a step-by-step visualization of such transactions. I hope it will help you to understand them.

If you're fine with read committed isolation level then it makes sense to take a look on RAMP transactions by Peter Bailis. They also can be implemented in the sharded MySQL environment.