Bill, http://www.innodb.com/ibman.php#Implicit_commit: " 8.7 When does MySQL implicitly commit or rollback a transaction?
MySQL has the autocommit mode switched on in a session if you do not do SET AUTOCOMMIT=0. In the autocommit mode MySQL does a commit after each SQL statement, if that statement did not return an error. If an error is returned by an SQL statement, then the commit/rollback behavior depends on the error. See section 13 for details. The following SQL statements cause an implicit commit of the current transaction in MySQL: CREATE TABLE (commits only if version < MySQL-4.0.13 and MySQL binlogging is used), ALTER TABLE, BEGIN, START TRANSACTION, CREATE INDEX, DROP DATABASE, DROP TABLE, RENAME TABLE, TRUNCATE, LOCK TABLES, UNLOCK TABLES, SET AUTOCOMMIT=1. The CREATE TABLE statement in InnoDB is processed as a single transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during his transaction. If you have the autocommit mode off and end a connection without calling an explicit COMMIT of your transaction, then MySQL will roll back your transaction. " Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL support from http://www.mysql.com/support/index.html ----- Alkuperäinen viesti ----- Lähettäjä: "Bill Easton" <[EMAIL PROTECTED]> Vastaanottaja: <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Thursday, December 18, 2003 5:02 PM Aihe: transactions and create table (was Questions about MySQL implementation) > What is the official word on doing a CREATE TABLE inside a transaction? Can > I do one without > causing the transaction to commit? > > By experiment, it appears that 4.0.14 allows this, although, even if the > CREATE TABLE is for an InnoDB > table, a ROLLBACK doesn't remove the created table. It appears that CREATE > TABLE caused > a commit of the current transaction in 3.23.49. > > I'd like to create a temporary table during a transaction--mostly to be able > to emulate things like subqueries > and views that will come in some future production version of MySQL. I > don't have a problem with > the table creation not being rolled back. > > I tried to RTFM. I did find a note on how transactions are treated for > CREATE TABLE SELECT..., but I wasn't > able to find a clear statement that CREATE TABLE will no longer force a > commit. > > > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Subject: Re: Questions about MySQL implementation > Date: Thu, 18 Dec 2003 01:18:39 +0200 > > Chris, > > ----- Original Message ----- > From: "Chris Nolan" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Saturday, December 13, 2003 7:24 AM > Subject: Questions about MySQL implementation > > [snip] > > > 2. I've been told on good authority (by persons on this fine list) that > > Sybase and PostgreSQL (and, from personal experience, SQLBase) support > > ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE > > etc. From what I can gather, neither BDB nor InnoDB do this. > > > > Does anyone know what sort of technical challenges making the above > > statements "undoable" involve over and above INSERT, DELETE and UPDATE > > statements? Would this functionality be something that MySQL AB / > > Innobase Oy would be interested in developing should it be sponsored? > > Not very difficult: we could keep the 'old' table until the transaction > commit. In a rollback we would fall back to the old table. But the demand > for such a feature is so low that most databases do not have a rollback of > DDL statements. > > [snip] > > Chris > > Best regards, > > Heikki Tuuri > Innobase Oy > [snip] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
