Does mysql need to commit?

In mysql, whether commit is required depends on the storage engine: 1. If it is a storage engine that does not support transactions, such as myisam, you do not need to use commit; 2. If it is a storage engine that supports transactions, such as innodb, then You need to know whether the transaction is automatically committed, so you need to use commit.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

Does mysql require commit?

Whether mysql requires commit when performing additions, deletions, and modifications depends on your storage engine.

• If it is an engine that does not support transactions, such as myisam, it will not be valid whether it is committed or not.

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

Look at your own You can use

mysql> show variables like '%autocommit%';

to check whether the database is automatically committed. If it is OFF, it will not be automatically committed and requires manual commit operation (the command line can directly use the "commit;" command), otherwise it will be automatically committed.

Extended knowledge:

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 go to the database to extract it, 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.

Recommended learning:

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!