Does MySQL need to commit?

Whether mysql needs to commit when performing operations such as insert depends on your storage engine. If it is an engine that does not support transactions, such as myisam, commit or not will not be effective.

Recommended courses: MySQL Tutorial.

If it is an engine that supports transactions, such as innodb, you need to know whether your transaction supports automatic submission of transactions (i.e. commit)

To see if your database is automatically committed, you can use mysql> show variables like '%autocommit%'; to view, if it is OFF, it will not be automatically committed, and manual commit operation is required (the command line can directly "commit;" command), otherwise it will be automatically committed.

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

These three types are explained below.

Explicit submission

The submission completed directly using the COMMIT command is an explicit submission.

Implicit submission

Submit completed indirectly using SQL commands is implicit submission. These 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, and delete statements are executed, the system will automatically Make a commit, which is an automatic commit. The format is: SQL>SET AUTOCOMMIT ON;

COMMIT / ROLLBACK. Be careful when using these two commands. COMMIT/ROLLBACK are used after executing DML statements (INSERT/DELETE/UPDATE/SELECT). After the DML statement is executed, the processed data will be placed in the rollback segment (except for the SELECT statement), waiting for the user to commit (COMMIT) or rollback (ROLLBACK). When the user executes COMMIT/ROLLBACK, it will be placed in the rollback segment. The data in the 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 to query, it is extracted directly from the shared pool without having to extract it from the database, which improves the speed of data query.)

All DML statements must be submitted explicitly, that is to say, COMMIT must be executed after the DML statement is executed. Others, such as DDL statements, are submitted implicitly. In other words, after running those non-DML statements, the database has already made an implicit commit, such as CREATE TABLE. After running the script, the table has been created and you no longer need to make an explicit commit.

The above is the detailed content of Does MySQL need to commit?. For more information, please follow other related articles on the PHP Chinese website!