John Hoover <[EMAIL PROTECTED]> wrote on 12/31/2005 04:18:34 PM: > 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) >
You are going to need to setup your own application-level locking scheme and rollback procedures if you don't want to use or cannot use the locking and transaction support built into InnoDb. For the tables that do not support transactions (because they are MyISAM or something else) you will probably need to take snapshots of the initial state of each table, ensuring that no other process tries to update or insert to that table while you have it snap-shotted (or you will invalidate the OTHER process should you need to roll back). The table-wide locks are generally good enough for that kind of protection but they are performance killers if you have any sort of concurrency or if your transaction takes too long. I would strongly recommend NOT using the mysql tables for your application's security needs. I would roll my own application-level permission tables and use them to control access through the front end. Generally, my end users do not get direct read-write access to the tables behind any application. They may get read-only access but that's through their own accounts, not the accounts I use to access the DB with from the application itself. If you still need to create user accounts on the fly, stick with the GRANT and REVOKE statements and do that part of the processing either first or last (outside of your other transactions) That way you can know for sure if you got the account changed or not either before you start the transaction or just before you commit or rollback. Lookup and be aware that certain commands contain an implicit COMMIT when they are executed so your transaction may end earlier than you planned if you use one of those commands. These are usually data definition statements (ALTER TABLE, etc) but it's better for you if you know them all. Sorry I can't be more specific but it's time to blast and dinner is waiting. Cheers! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
