0

I have a question on this

Assume I have a few connections

$conn_site = mysqli_connect("localhost", "us", "ps");
mysqli_select_db("site", $conn_site);
$conn_forum = mysqli_connect("localhost", "us", "ps");
mysqli_select_db("forum", $conn_forum);

And want to do a transaction between these connections. Since mysqli extension in PHP don't have transactions support, I have to do it manually like this

function begin()
{
    @mysqli_query("BEGIN", $conn_site);
    @mysqli_query("BEGIN", $conn_forum);
}

function commit()
{
    @mysqli_query("COMMIT", $conn_site);
    @mysqli_query("COMMIT", $conn_forum);
}

function rollback()
{
    @mysqli_query("ROLLBACK", $conn_site);
    @mysqli_query("ROLLBACK", $conn_forum);
}

And then use it

begin();
mysqli_query("insert into users (....)") or rollback();
mysqli_query("insert into forumusers(....)") or rollback();
commit();

So since I handle states manually, what's the difference between a plain and XA transaction in PHP? If I start a plain transaction on other connection, a transaction in the first will be automatically cancelled by mysqli extension? Or what? Can someone clarify this?

Community
  • 1
  • 1
efpies
  • 3,625
  • 6
  • 34
  • 45

1 Answers1

0

Very simple: what do you do if you fail in the second commit statement? The first one can't be rolled back anymore (it's already committed) and therefore your consistency guarantee is broken. The correct way to address this problem is 2PC. That is exactly what XA provides.

steve
  • 5,870
  • 1
  • 21
  • 22
  • 1
    2PC is query `XA PREPARE` to 2 connections + query `XA COMMIT` still to 2 connections. So if the second `COMMIT` fails, how can I rollback the committed first? – efpies May 23 '14 at 13:23