Ryan,
>How exactly does the BEGIN/COMMIT/ROLLBACK transaction process work? The
>examples I have seen are like:
>BEGIN;
>statement 1;
>statement 2;
>statement n;
>COMMIT;
>Does this imply that if any of the statements fail then a ROLLBACK will be
>called, otherwise it will COMMIT?
you have to code the rollback logic in your application. Since you
send SQL statements to MySQL one at a time, the MySQL server cannot know
what statements are still coming.
In your example, MySQL/Innobase would do the following: if any
single SQL statement fails, then depending on the error, Innobase
will roll back part of the statement, the whole statement, or everything
you have done since the last BEGIN (= the whole transaction).
Then, if you continue sending statements to MySQL, they will be processed
like nothing would have happened to the previous statement.
Currently, in MySQL/Innobase a duplicate key error in an insert of a row
only rolls back that single row insert. A deadlock with other transactions
rolls back the whole transaction.
ANSI SQL standard specifies that an error in an SQL statement
should result in a rollback of that whole statement. We have to
consider in future if MySQL should by default follow the ANSI spec.
Regards,
Heikki Tuuri
Innobase Oy
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
