Skip navigation links

Additional languages
13.2.10.3 InnoDB and TRANSACTION ISOLATION LEVEL »
Section Navigation      [Toggle]

13.2.10.2. InnoDB and AUTOCOMMIT

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts new connections with autocommit enabled.

If autocommit mode is disabled within a session with SET AUTOCOMMIT = 0, the session always has a transaction open. An SQL COMMIT or ROLLBACK statement ends the current transaction and a new one starts. A COMMIT means that the changes made in the current transaction are made permanent and become visible to other sessions. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction. Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.

If the session has autocommit enabled, a multiple-statement transaction can be performed by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with COMMIT or ROLLBACK.


User Comments

Posted by Darl Kuhn on March 10 2006 7:37pm[Delete] [Edit]

To see what autocommit is set to you can:

mysql> select @@autocommit;

Posted by Patrick Gibson on March 31 2006 12:38am[Delete] [Edit]

To set the default behaviour for all incoming connections, add the following into your my.cnf or my.ini:

[mysqld]
init_connect='set autocommit=0'

WARNING: this sets autocommit to 0 for ALL connections, not just the command-line client!

Posted by Bob Hansen on April 14 2006 3:53pm[Delete] [Edit]

Keep in mind that some calls will Auto-commit and end the transaction, even if autocommit is disabled. For example, CREATE TABLE.

http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

Add your own comment.