i have program reads raw data , insert few thousand records table. uses lookup table replace values id. values not in lookup table , has update lookup table , update has committed before main update loop continues(?). far understand, update/commit of lookup table commits updates of main table. there way commits distinct tables?
although not recommend it, , it's not transaction stands for, , , should redesign code (i cannot picture problem necessary, though maybe easier) or @ least use several connections, can technically trying mixing innodb- , myisam-tables. myisam doesn't support transactions, can disable transaction table completely.
but true other queries use table, too, decision table-decision , can not enabled on-the-fly. , design might result in strange behaviour @ other times or queries if not careful (because transactions nice thing have).
so if still want it:
create table table1 (id int primary key) engine = innodb; create table table2 (id int primary key) engine = myisam; start transaction; insert table1 (id) values (1); insert table2 (id) select id table1; rollback; table1 empty after that, table2 not.
the rollback give warning transactional changes on table2 couldn't have been rolled back, wanted anyway.
btw, don't have recreate table, can switch existing table myisam e.g.
alter table table2 engine=myisam; 