Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Transaction Management: Commit or Rollback

By: FYIcenter.com

Part:   1  2   3  4  5  6  7  8  9  10 

(Continued from previous part...)

How To Create a Table for Transaction Testing?

If you want to learn transaction management, you should create a table with the InnoDB storage engine. The default storage engine MyISAM does not support the transaction concept. The tutorial exercise below shows you a good example of creating a new table with the InnoDB storage engine:

>\mysql\bin\mysql -u dev -piyf fyi

mysql> DROP TABLE fyi_links;

mysql> CREATE TABLE fyi_links (id INTEGER PRIMARY KEY,
  url VARCHAR(16) NOT NULL,
  notes VARCHAR(16),
  counts INTEGER,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP())
  ENGINE = InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql> SHOW CREATE TABLE fyi_links;
CREATE TABLE `fyi_links` (
  `id` int(11) NOT NULL,
  `url` varchar(16) NOT NULL,
  `notes` varchar(16) default NULL,
  `counts` int(11) default NULL,
  `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

You should keep this table for to practice other tutorial exercises presented in this collection.

How To Switch between Autocommit-On and Autocommit-Off Modes?

By default, your connection session will be in Autocommit-On mode, where every server executable statement will start a new transaction, and end the transaction when the execution ends. Changes will be committed. So consider this mode as single-statement autocommitted transaction mode.

If you don't like the default Autocommitt-On mode, you can use the "SET AUTOCOMMIT = 0" to switch to the Autocommitt-Off mode. Of course, you can switch back the Autocommitt-On mode with the "SET AUTOCOMMIT = 0" command. The following tutorial exercise shows some good examples:

>\mysql\bin\mysql -u dev -piyf fyi

mysql> -- You are in the default Autocommit-On mode now

mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.03 sec)

mysql> -- You are in the Autocommit-Off mode now

mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.03 sec)

mysql> -- You are back in the Autocommit-On mode now

How To Find Out the Current Transaction Mode?

If you are not sure about your current transaction mode, you can use the "SELECT @@AUTOCOMMIT FROM DUAL" statement to find out as shown in the following tutorial exercise:

>\mysql\bin\mysql -u dev -piyf fyi

mysql> SELECT @@AUTOCOMMIT FROM DUAL;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT @@AUTOCOMMIT FROM DUAL;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> SET AUTOCOMMIT = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@AUTOCOMMIT FROM DUAL;
+--------------+
| @@AUTOCOMMIT |
|            1 |
+--------------+
1 row in set (0.00 sec)

(Continued on next part...)

Part:   1  2   3  4  5  6  7  8  9  10