[mysql] Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)

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 */

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to transactions

sql try/catch rollback/commit - preventing erroneous commit after rollback Laravel: Using try...catch with DB::transaction() UnexpectedRollbackException: Transaction rolled back because it has been marked as rollback-only Transaction marked as rollback only: How do I find the cause Transaction isolation levels relation with locks on table Spring transaction REQUIRED vs REQUIRES_NEW : Rollback Transaction When to use SELECT ... FOR UPDATE? Correct use of transactions in SQL Server MySQL : transaction within a stored procedure issue ORA-00001: unique constraint violated coming in INSERT/UPDATE