multiple database and transaction problem

Question

hello!

If i use two database, i have to start to transaction? Is this correct, or this code is wrong? If i make a mistake in the second query, then call rollback(), but unfortunatelly won't rolli'n back the frist query...

 $conn_site=mysql_connect("localhost", "us", "ps");
 mysql_select_db("site",$conn_site); 
 $conn_forum=mysql_connect("localhost", "us", "ps");
 mysql_select_db("forum",$conn_forum); 

     function begin() {

         @mysql_query("BEGIN",$conn_site);
         @mysql_query("BEGIN",$conn_forum);
     }
    function commit_reg() {
        @mysql_query("COMMIT",$conn_site);
        @mysql_query("COMMIT",$conn_forum);
    }
    function rollback(){
        @mysql_query("ROLLBACK",$conn_site);
        @mysql_query("ROLLBACK",$conn_forum);
    }
   begin();
    mysql_query("insert into users (....) or rollback();
       mysql_query("insert into forumusers (....) or rollback();
    commit();

Answer 1

That won't do squat. Transactions are isolated within a single "database". In order to have transactions span across multiple databases, you need what's called "distributed transaction management". Standards to do this include XTA. More enterprise oriented frameworks, including Java J2EE, include this sort of thing as standard.

Since it looks like you're using PHP, you're going to have to roll your own, so to speak. I'll assume Mysql supports nested transactions (I don't know). So, if the inner transactions on the two db's both succeed, you're good... commit the two outer transactions. If either of the inner transactions fail, rollback both outer transactions.

Answer 2

What you should really do is, use a single database connection to use both databases.

In MySQL, "database" is really more like a "catalog" or "schema" in other servers. You can use tables from another database in the same connection as permissions allow.

Answer 3

mysql supports XA transactions, which allow for the two-step commit like robert suggests, but in a more formal manner. the PHP interfaces don't directly support XA transactions, so you'll have to send the transaction commands yourself as statements.