At the July MySQL User Group Meeting, Jim Starkey wondered aloud, “What happens when I COMMIT on a memory table?” I wrote the question down, to research it later.
The obvious answer is “COMMIT on a non-transactional table does nothing.”
Tonight I was thinking about this, and I realized I do not actually COMMIT “on a table.”
The manual page at: http://dev.mysql.com/doc/refman/4.1/en/commit.html (and the 5.0 and 5.1 equivalents) state:
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.
If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB Cluster), you can disable autocommit mode with the following statement:
SET AUTOCOMMIT=0;
But what does If you are using a transaction-safe storage engine really mean? I ask because I do not specify a table type with that command. So I tried it on a fresh MySQL install (5.0.19 GA, on a RedHat Linux Enterprise machine):
[skritzer]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select distinct table_schema,engine from information_schema.tables\G
*************************** 1. row ***************************
table_schema: information_schema
engine: MEMORY
*************************** 2. row ***************************
table_schema: information_schema
engine: MyISAM
*************************** 3. row ***************************
table_schema: mysql
engine: MyISAM
3 rows in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
As you can see, this machine has no user-created tables (seriously, it was just installed today, that user connection id of 1 is because it was the first time I logged into it). From my playing around, I can extrapolate that If you are using a transaction-safe storage engine really means If you have a transaction-safe storage engine enabled.
Let’s try it out:
mysql> show engines\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
Engine: BLACKHOLE
Support: NO
Comment: /dev/null storage engine (anything you write to it disappears)
*************************** 6. row ***************************
Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 8. row ***************************
Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 9. row ***************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 10. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
*************************** 11. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 12. row ***************************
Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows in set (0.00 sec)
mysql> exit
Bye
[skritzer]$ ls -l /etc/my.cnf
ls: /etc/my.cnf: No such file or directory
[skritzer]$ sudo vi /etc/my.cnf
We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these two things:
#1) Respect the privacy of others.
#2) Think before you type.
Password:
[mysqld]
skip-innodb
:wq
"/etc/my.cnf" 2L, 21C written
[skritzer]$ sudo /etc/init.d/mysql stop
Shutting down MySQL.. [ OK ]
[skritzer]$ ps -ef | grep mysql
skritzer 7253 7179 0 21:07 pts/0 00:00:00 grep mysql
[skritzer]$ sudo /etc/init.d/mysql start
Starting MySQL[ OK ]
[skritzer]$ ps -ef | grep mysql
root 7261 1 0 21:07 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql.pid
mysql 7282 7261 0 21:07 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/mysql.pid --skip-locking
skritzer 7290 7179 0 21:07 pts/0 00:00:00 grep mysql
[skritzer]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show engines\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
Engine: BLACKHOLE
Support: NO
Comment: /dev/null storage engine (anything you write to it disappears)
*************************** 6. row ***************************
Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 8. row ***************************
Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 9. row ***************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 10. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
*************************** 11. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 12. row ***************************
Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows in set (0.00 sec)
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
No errors or warnings are generated. So technically speaking, I can SET autocommit=0 no matter what storage engines are in use, for any of the varying definitions of in use.
Comments are closed.