If you just google, you may find autocommit a vaguely defined term.
Each individual database interaction (i.e., each SQL statement) submitted through the database connection in autocommit mode will be executed in its own transaction that is implicitly committed. A SQL statement executed in autocommit mode cannot be rolled back.
In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error.
Despite all these statements, you may only care one question: will there be an automatically created transaction committed IMMEDIATELY after I enter a query and then hit the ENTER in console, or my interpreter run over a statement like
session.run('query-goes-here')? The answer is NO. Look carefully. NO.
Let’s find a better explanation for this term.
Enabling auto-commit causes a transaction commit after each individual SQL statement as soon as that statement is complete. The point at which a statement is considered to be “complete” depends on the type of SQL statement as well as what the application does after executing it:
- For Data Manipulation Language (DML) statements such as
Delete, and DDL statements, the statement is complete as soon as it has finished executing.
Selectstatements, the statement is complete when the associated result set is closed.
CallableStatementobjects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved.
Insert for example. The question now is: does the returning of my function call
session.run('insert-query') mean my
insert-query is “complete”? No, because its underlying implememtation might be a message queue and asynchronous execution (and the return result is lazily initialized.) If your programme just exits before
insert-query is “complete”, your data will not be inserted at all and you may blame autocommit.
Always manage your own transactions in your client programmes.