1

MySQL does not open a transaction, and initiates a request containing multiple SQLs. This time, is it a transaction, or is each SQL a transaction?

Does not open a transaction means that a transaction will autocommit. How about the details?

1 Answer 1

1

There are two ways that list of multiple SQL statements would be a single transaction

METHOD #1 : Disabled Auto Commit

You could set autocommit off for your session

SET autocommit = 0;

or

SET SESSION autocommit = 0;

Then after executing all your SQL statements, you launch

COMMIT;

If you forget to run COMMIT; and disconnect, all the SQL will rollback and not be written.

NOTE : If autocommit=0 is in your my.cnf, then COMMIT; must be used after each set of INSERTs, UPDATEs, and DELETEs. You can see the default for the MySQL Instance by running:

SELECT @@global.autocommit;

or

SHOW GLOBAL VARIABLES LIKE 'autocommit';

METHOD #2 : Transaction Block

If autocommit is on (usually is by default), then for a list of SQL Statements it would have to be in a transaction block with START TRANSACTION; or BEGIN; and COMMIT at the end of the block:

START TRANSACTION;
< list of one or more INSERT, UPDATE, of DELETE SQL statements>
COMMIT;

or

BEGIN;
< list of one or more INSERT, UPDATE, of DELETE SQL statements>
COMMIT;

CAVEAT

If you run any DDL (ALTER TABLE, CREATE TABLE, etc) this will a trigger an implicit commit of all pending uncommitted transactions. See my old posts:

EPILOGUE

Unless you did one of these two things, each SQL Statement will be committed individually. For more information on this subject, please read the MySQL Documentation : START TRANSACTION, COMMIT, and ROLLBACK Statements

3
  • 8.0 has made some DDLs transaction-aware.
    – Rick James
    Dec 21, 2022 at 20:48
  • You says:"Unless you did one of these two things, each SQL Statement will be committed individually."autocommit is on and do not start transactions manually.Does it mean that the multiple SQLs included in this request belong to different transactions?(insert a;insert b;insert c;) Dec 22, 2022 at 20:58
  • Yes, it does Each INSERT is a separate transaction. Dec 22, 2022 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.