Will a mysql transaction work if multiple connections are used for the queries?

Question

So here it is ...

// I start transaction $mysqli = new mysqli(...);

// Start transaction $mysqli->autocommit(FALSE);

• here is a select prepared stmt with its own db connection
• here is an insert prepared stmt with its own db connection
• here is an slect prepared stmt with the above db conn
• insert new conn
• insert new conn
• insert new conn

if every prepared query returs true it will($mysqli->commit()) else $mysqli->rollback

I dont know for what reason that transaction will autocommit

I've done some transaction with 3 multiple connections and they rolledback...this one doesnt... Is mysql made to support a transaction that has queries with individual connections?

Solved... if queries from different tables are interdependent like in my case and you need strong data access security , set autocommit to false for for each connection that you open , set a var to false for each failed bussness logic, if anything fails , rollback every set of queries , else comit evry query... Yes the transaction is executed per conection, had something else that managed the rollback when i said that mysql did the rollback for some multiple conn transactions

Re-edit : ofc this wont work like a normal transaction ... in case of a system crash data will be stored untill the moment of the crash

Answer 1

All queries inside of a transaction must be on the same connection. If you have three connections, you have three transactions open (assuming they're not in autocommit mode).

If you actually need distributed transactions, you'll have to use MySQL's XA Transactions—but that should only really be required if you're dealing with transactions in more than one database (or, alternatively, some other non-database transactions).