Does MySql need to commit?

Whether MySQL needs to commit when performing operations such as insert depends on your storage engine.

If the engine does not support transactions, such as MyISAM, then commit will have no effect.

If it is an engine that supports transactions, such as InnoDB, you need to know whether your transaction supports automatic transaction submission (commit)

To see whether your database is automatically committed, you can use mysql> show variables like '%autocommit%'; to check. If it is OFF, it will not be automatically committed and you need to commit manually (you can directly use the "commit;" command on the command line). Otherwise, it will be automatically committed.

There are three types of submitted data: explicit submission, implicit submission, and automatic submission.

These three types are described below.

Explicit Commit

A commit done directly with the COMMIT command is an explicit commit.

Implicit commit

The submission completed indirectly by SQL commands is implicit submission. The commands are:

ALTER , AUDIT , COMMENT , CONNECT , CREATE , DISCONNECT , DROP , EXIT , GRANT , NOAUDIT , QUIT , REVOKE , RENAME .

Automatic submission

If AUTOCOMMIT is set to ON, after the insert, modify, or delete statement is executed,

The system will submit automatically, this is automatic submission. The format is: SQL>SET AUTOCOMMIT ON;

Be careful when using the COMMIT / ROLLBACK commands. COMMIT / ROLLBACK are used after executing DML statements (INSERT / DELETE / UPDATE / SELECT). After a DML statement is executed, the processed data will be placed in the rollback segment (except for SELECT statements), waiting for the user to commit (COMMIT) or rollback (ROLLBACK). When the user executes COMMIT / ROLLBACK, the data placed in the rollback segment will be deleted. (After the SELECT statement is executed, the data is stored in the shared pool. When providing the same data to others for query, it is directly extracted from the shared pool without going to the database, which improves the speed of data query.)

All DML statements must be explicitly committed, that is, COMMIT must be executed after the DML statement is executed. Others, such as DDL statements, are implicitly committed. That is to say, after running those non-DML statements, the database has already performed an implicit commit. For example, after running the CREATE TABLE script, the table has been created and you no longer need to perform an explicit commit.