8
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?