CODEWITHSUNDEEP
mysqldatabasetransactionsinnodb
Prabu
Prabu

Reputation: 1255

MySQL Transaction across several InnoDB databases on one server

I have some quick questions on MySQL InnoDB engine Transaction. I have some databases in this format (db_1, db_2, db_3 etc..)

I want to insert / update to any of the database within the transaction like as below,

BEGIN
USE DB_1;
//Insert / Update into DB_1 tables

USE DB_2;
//Insert / Update into DB_2 tables
COMMIT

Is it ok ? or do I need to put separate transaction for every database as below,

BEGIN
USE DB_1;
//Insert / Update into DB_1 tables
COMMIT

BEGIN
USE DB_2;
//Insert / Update into DB_2 tables
COMMIT

Are there any problems switching multiple database within a single transaction as in the first method. Is transaction database level or at global level ?

Upvotes: 2

Views: 1933

Answers (1)

O. Jones
O. Jones

Reputation: 108706

This should work. Obviously DB_1 and DB_2 are on the same server. If they are ever going to be on different servers, clustering is in your future! :-)

You might consider qualifying the names of your tables rather than employing USE statements. It's possible this will simplify your logic.

For example, 

  BEGIN;
  INSERT INTO DB_1.mytable (myid, mycol) VALUES (1,'a');
  INSERT INTO DB_2.histable (hisid, hiscol) VALUES (1,'b');
  SELECT whatever
    FROM DB_1.mytable AS m
    JOIN DB_2.histable AS h ON m.myid = h.hisid;
  COMMIT;

Upvotes: 1

Related Questions