Doing transactions with MyISAM tables

[Richard_Mossman]

On Feb. 24, a question was asked about using cftransaction with MySQL. That person's answer was to convert the tables to InnoDB.

I have three tables that must stay "in lockstep". When I add a row to one table, I have to add rows to the other two. If they get out of sync, one of the queries runs excruciatingly slow.

I tried wrapping them in a cftransaction, but now find out that it didn't really do any good because the table format is still MyISAM. Is there anything I can do (short of altering the tables) to do transactions?

Is there a way to get MySQL to process the transactions "manually". Do I have to do a series of "trys" and "catches" with a commit or rollback for each one? Do you know of a place where I can see code like that?

It would be nice if I were just missing something and someone could tell me how to do transactions with MyISAM tables. But, I'll do whatever is necessary to avoid having the tables get out of sync.

Thanks.

[Kronin555]

MyISAM tables aren't designed to be transaction-safe. That's what the InnoDB table type is for.

http://www.webmonkey.com/webmonkey/backend/databases/tutorials/tutorial2.html

From that tutorial:
"MyISAM tables are very fast for SELECTs, but they have some serious drawbacks when it comes to locking. These shortcomings are what prevented MySQL from implementing some key database features, including transactions."

[Richard_Mossman]

After making a copy of the tables:

I used MySQLAdmin to convert each of the tables to InnoDB. It displayed the correct "alter table table_name type=innobd;" box before each change.

Before the changes, the three tables showed the same record count. After the changes, they were wildly different. But, (with no changes to the web pages) the application opens OK and I can call up records. Did the conversion get rid of some records or what?

Also, one of the pages (that uses a lot of convoluted logic for the display) takes a lot longer to appear than it used to. All the other pages are open fine and display their forms, etc.. Updates seem a little slower, but not too bad.

Why is the record count different in the three tables? Why might the one page be running slower? (Do I need to make any changes in the original web page code?)

Prior to the conversion, the three MyISAM tables (*.MYD, *.MYI, & *.frm) were all stored in the C:\\Program Files\MySQL\MySQL Server 5.0\data\my_data directory. Now that directory only holds ".frm" files. Where did the conversion put the new tables?

I haven't yet tested the cftransactions to verify they work. That's next.

Thanks.