MySQL Forums
Forum List  »  InnoDB

Re: XA transaction isolation level and ACID properties
Posted by: Aaron Staley
Date: April 18, 2016 12:17AM

I'm not using MySQL cluster, just independent mysql databases. As it may be unclear what I'm trying to achieve let me again expand on my example:

Let's imagine I have two user db shards, user1 and user2; let their tables also be called user1 and user2. Users have an id and an email.

With READ COMMITTED, I'm able to take locks on a user row.

e.g (I'm using MYSQL pseudo-code here)

begin;
select email from user where id=1 for update;

# At this point I have a row lock -- nothing else can conduct a write until that lock is released.


Let's say as a simple example I want to move user id=1's email to user id=2. I can accomplish this by doing

begin;
select email into @src_email from user1 where id=1;
update user1 set email=None where id=1;
update user1 set email=@src_email where id=2;
commit;

During this transaction, nothing can acquire the row locks until the commit has happened. In addition, this is all-or-nothing - either both id=1 and id=2 change or they don't.

The problem is if I need to execute cross-shard. The following is in effect two interleaved transactions (pseudo-code - I preface each command with SHARD:, which indicates that shard I'm speaking to)

begin;
user1: select email into @src_email from user1 where id=1;
user1: update user1 set email=None where id=1;
user2: update user2 set email=@src_email where id=2;
user1: commit
user2: commit


Unfortunately, this has lost the all-or-nothing commit properties. It's possible that I commit user1, but fail to commit user2 (db goes down, process executing commands goes down, etc.). Such behavior is undesirable for developers and I wish to provide all-or-nothing commit properties by utilizing XA transactions (nb I recognize that doing this requires building infrastructure to manage failed transactions).

Example of a multi-db (cross-shard) XA transaction:

@xid = some uuid
user1: XA start @xid
user2: XA start @xid
user1: select email into @src_email from user1 where id=1;
user1: update user1 set email=None where id=1;
user2: update user2 set email=@src_email where id=2;
user1: XA end @xid
user2: XA end @xid
# On the server (coordinator) running this code, save the xid to stable storage. Some sort of resume system exists if anything fails after this point. Further details of coordinator control are omitted
user1: xa prepare @xid
user2: xa prepare @xid
# Note that the row locks are still active. Expectation is that they are held across db restarts, until an explicit xa commit or rollback
user1: xa commit @xid
# Note that at this point, row lock on user1 is released, but lock on user2 is still held.
user2: xa commit @xid


I'm able to in the SQL console to execute these commands in READ COMMITTED isolation level. But http://dev.mysql.com/doc/refman/5.7/en/xa.html implies that I'm not achieving ACID properties by doing this. What specific property is being violated?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.