What’s Wrong With XA/Two Phase Commit

27. June, 2011

To recap, two phase commit (XA) means that you have two or more systems which take part in a single transaction. The first phase ask all system “are you 100% sure that you can commit?” (prepare) and the second phase is the actual commit.

Of course, the answer to the first question can be a lie. You can never be 100% sure that a commit will go through. The system might crash in the middle of the actual commit or the network my break between prepare and commit. Doom.

So XA doesn’t when it should. How can you solve this?

By using a less brittle protocol. Imagine you want to copy data from database A to B. You could use XA and clean up the mess every once in a while.

Or you could add a field to A which says “this has been copied to B” and when inserting data into B, you must ignore data that is already there. Here is the pseudo code:

  1. Create the connections
  2. Find all rows in A that don’t have the flag set
  3. Read all the rows and insert them into B. If a row already exists, skip it.
  4. Set the flags for all copied rows in A.

Note: You can’t use MAX(ID) or MAX(TIMESTAMP) here. Why not? Imagine:

  1. TX1 is created in A and a row is inserted. MAX(ID) == 2
  2. TX2 is created in A and a row is inserted. MAX(ID) == 3! At this time, you can’t read WHERE ID == 2 but you can conclude from the gap in the ID values that it will soon exist.
  3. TX2 is committed.
  4. TX3 is created to copy the data to B. TX1 is still running! MAX(ID) == 3 but row 2 will not be copied!
  5. TX3 ends without row 2. Since MAX(ID) is now 3, it won’t ever be copied.

If you’d rather avoid a “to copy” or “has been copied” flag in each row, create a “to transfer” table which contains IDs of the rows to copy. In step #4, delete the rows that have to be copied.

Advantages:

  1. Resilient. If the transfer fails in the middle for any reason, it can pick up where it left of. In the worst case, a lot of data will be copied again but data will never be lost. In the usual case, no data will be copied twice.
  2. If you make a mistake, chances are that it won’t have matter much. Say you forget to set the “has been copied” flag. Well, for every transfer, too much data will be copied but it will still work. Slower than expected but you won’t lose data. The database will always be consistent!
  3. Say something goes wrong in B and you need to transfer the data again. With my approach, you just reset the flags. It doesn’t matter if you can’t say for sure which flags to reset. If in doubt, reset all of them. The algorithm will heal itself.
  4. You can copy the data in chunk sizes of your choice. It doesn’t matter if you copy everything in one go or in blocks of 1’000 rows or row by row.
  5. It’s a simple algorithm, so it will be quick to implement and there is only a small chance for bugs. Even if there is a bug, in most cases, you will be able to resolve the situation with one or two simple SQL queries.

Disadvantages:  The XA sales guy won’t make a buck from it. Expect some resistance.

 

Leave a Comment » | Software | Tagged: , , , | Permalink
Posted by digulla