I am reading up on transactions in MySQL and am not sure whether I have grasped something specific correctly, and I want to be sure I understood that correctly, so here goes. I know what a transaction is supposed to do, I'm just not sure whether I understood the statement semantics or not.
So, my question is, is anything wrong, (and, if that is the case, what is wrong) with the following:
By default, autocommit mode is enabled in MySQL.
Now, SET autocommit=0;
will begin a transaction, SET autocommit=1;
will implicitly commit. It is possible to COMMIT;
as well as ROLLBACK;
, in both of which cases autocommit is still set to 0 afterwards (and a new transaction is implicitly started).
START TRANSACTION;
will basically SET autocommit=0;
until a COMMIT;
or ROLLBACK;
takes place.
In other words, START TRANSACTION;
and SET autocommit=0;
are equivalent, except for the fact that START TRANSACTION;
does the equivalent of implicitly adding a SET autocommit=0;
after COMMIT;
or ROLLBACK;
If that is the case, I don't understand http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_serializable - seeing as having an isolation level implies that there is a transaction, meaning that autocommit should be off anyway?
And if there is another difference (other than the one described above) between beginning a transaction and setting autocommit, what is it?
This question is related to
mysql
sql
transactions
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 */
If you want to use rollback, then use start transaction and otherwise forget all those things,
By default, MySQL automatically commits the changes to the database.
To force MySQL not to commit these changes automatically, execute following:
SET autocommit = 0;
//OR
SET autocommit = OFF
To enable the autocommit mode explicitly:
SET autocommit = 1;
//OR
SET autocommit = ON;
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html
The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this:
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
Source: Stackoverflow.com