In InnoDB you have START TRANSACTION;
, which in this engine is the officialy recommended way to do transactions, instead of SET AUTOCOMMIT = 0;
(don't use SET AUTOCOMMIT = 0;
for transactions in InnoDB unless it is for optimizing read only transactions). Commit with COMMIT;
.
You might want to use SET AUTOCOMMIT = 0;
in InnoDB for testing purposes, and not precisely for transactions.
In MyISAM you do not have START TRANSACTION;
. In this engine, use SET AUTOCOMMIT = 0;
for transactions. Commit with COMMIT;
or SET AUTOCOMMIT = 1;
(Difference explained in MyISAM example commentary below). You can do transactions this way in InnoDB too.
Source: http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_autocommit
Examples of general use transactions:
/* InnoDB */
START TRANSACTION;
INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');
COMMIT; /* SET AUTOCOMMIT = 1 might not set AUTOCOMMIT to its previous state */
/* MyISAM */
SET AUTOCOMMIT = 0;
INSERT INTO table_name (table_field) VALUES ('foo');
INSERT INTO table_name (table_field) VALUES ('bar');
SET AUTOCOMMIT = 1; /* COMMIT statement instead would not restore AUTOCOMMIT to 1 */