John Hoover wrote:
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?
You can use BDB or InnoDB - InnoDB provides row level locking, BDB
provides page level locking. I'd say InnoDB is the way to go (usually).
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?
What are your insertion operations? Typically, you would use GRANT
statements to add users the these tables - and those statements (if they
fail) won't do any GRANTing. I wouldn't grant access using insert
statements - you'll be flushing your privilege tables regularly.
Unless you are using the Host table, I'd recommend you do the following:
1. Prior to modifying a user, use the 'show grants' statement to find
out what access the user has - store that.
2. Perform all your GRANT operations.
3. If a single operation fails, remove the user and execute the stored
access (from step 1) for the user to restore his/her access. - if the
user didn't already exist, just remove all their access.
For users that won't connect to the database directly, you probably
don't want to create individual accounts - as if the user connects
directly they can perform operations outside the bounds of your
application (where you may implement business logic).
Assuming you stick with grant statements, it shouldn't be too difficult
to maintain integrity when you want to do your "pseudo-transactions".
Use InnoDB everywhere else.
--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
