0

Below is my sequence of code

// global autocommit is 1
db->query('SET AUTOCOMMIT=0');
db->query('BEGIN');
db->query('INSERT INTO TRANSACTIONS (CREDIT) VALUES (150)');
db->chagngeDatabase('member');

// here autocommit become 1 automatically after Db change. And the previous query rollback automatically
db->query('INSERT INTO memberAccount(CREDIT) VALUES (150)');
db->query('COMMIT');

Whenever i'm trying to change the database then the autocommit value becomes 1 and its rollback my previous query. How to do transactions while changing the db between queries in same server?

I have checked XA Distributed transactions, But it is mentioned for Transaction between different servers. here i need in a same server

Venkatesan KT
  • 1
  • 2
  • 1
    Possible duplicate of [Multiple database and transactions](http://stackoverflow.com/questions/2239810/multiple-database-and-transactions) – Shadow Oct 29 '15 at 11:43
  • I have checked that answers too, there they have mentioned for two different connections. But here i'm looking for same server with different db and same connection – Venkatesan KT Oct 29 '15 at 12:04
  • I think you should re-read the linked topic, it answers exactly your question. Read longneck's answer in particular (the one with the most upvotes, not the one which is accepted). – Shadow Oct 29 '15 at 12:07
  • XA transactions can be used for single server, multiple database scenario as well. There is nothing that restricts you to do that. Plus, there is no need to explicitly change the database. Just refer to your tables in databasename.tablename format. – Shadow Oct 29 '15 at 14:25
  • I got it. Now i dont have problem with db selection but START TRANSACTION. the BEGIN or START TRANSACTION doesn't changes the autocommit status. so now i manually clear the autocommit but still the transaction get rollback. Moreover it is mentioned like we can't directly use XA method in PHP. – Venkatesan KT Oct 30 '15 at 08:18
  • You have to issue the XA transaction commends as sql queries. – Shadow Oct 30 '15 at 10:18

0 Answers0