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?

Question

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?

Answer 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:

• Aug 21, 2015 : Transactional DDL workflow for MySQL
• Feb 02, 2021 : Conditions that can make a MySQL COMMIT query fail?

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