I need some advice re my choice of a storage engine for transaction-safe processing and including tables that are not transaction-safe within transactions.
The problem: We need to insert related records into several different tables and be sure that all the insertions succeeded. It seems that transactions are the recommended way of achieving this and I was thinking of using InnoDB tables. However, I'm not sure if that is the best engine to use - can anyone give me reasons for selecting a specific transaction-safe engine? Also, some of my insertions will involve the mySQL tables (creation of a new user and granting privileges, for example). According to the manuals, those tables use the myISAM engine and can not be changed to any other engine. What is the best way to handle insertion errors on myISAM tables? I had planned to test for an error after each operation and, if one occurred, manually undo whatever previous operations had already succeeded. That's a lot of work if the operation involves multiple tables and I'd like to know if there is a better alternative. Finally, if I do handle errors manually, what should I do if there is an error in the error handler? For example, suppose I've inserted one record and then an error prevents insertion of the related record so that I have to delete the previously inserted record. Is it possible for the delete to fail? If so, I'll have a partial transaction that can't be completed and can't be undone - what should I do to clean up? Thanks for the help, -- John Hoover [EMAIL PROTECTED] 301-890-6932 (H) 202-767-2335 (W) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
