- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 13.5.10 InnoDB Transaction Model and Locking
- 13.5.10.1 InnoDB Lock Modes
- 13.5.10.2 InnoDB and AUTOCOMMIT
- 13.5.10.3 InnoDB and TRANSACTION ISOLATION LEVEL
- 13.5.10.4 Consistent Non-Locking Read
- 13.5.10.5 SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads
- 13.5.10.6 Next-Key Locking: Avoiding the Phantom Problem
- 13.5.10.7 An Example of Consistent Read in InnoDB
- 13.5.10.8 Locks Set by Different SQL Statements in InnoDB
- 13.5.10.9 Implicit Transaction Commit and Rollback
- 13.5.10.10 Deadlock Detection and Rollback
- 13.5.10.11 How to Cope with Deadlocks
In InnoDB, all user activity occurs inside a
transaction. If the autocommit mode is enabled, each SQL
statement forms a single transaction on its own. By default,
MySQL starts new connections with autocommit enabled.
If the autocommit mode is switched off with SET
AUTOCOMMIT = 0, then we can consider that a user
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 users. A
ROLLBACK statement, on the other hand,
cancels all modifications made by the current transaction. Both
statements release all InnoDB locks that were
set during the current transaction.
If the connection has autocommit enabled, the user can still
perform a multiple-statement transaction by starting it with an
explicit START TRANSACTION or
BEGIN statement and ending it with
COMMIT or ROLLBACK.
User Comments
To see what autocommit is set to you can:
mysql> select @@autocommit;
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!
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.