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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There are two ways that list of multiple SQL statements would be a single transaction
You could set autocommit off for your session
SET autocommit = 0;
SET SESSION autocommit = 0;
Then after executing all your SQL statements, you launch
If you forget to run
COMMIT; and disconnect, all the SQL will rollback and not be written.
NOTE : If
autocommit=0 is in your
COMMIT; must be used after each set of
DELETEs. You can see the default for the MySQL Instance by running:
SHOW GLOBAL VARIABLES LIKE 'autocommit';
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
COMMIT at the end of the block:
START TRANSACTION; < list of one or more INSERT, UPDATE, of DELETE SQL statements> COMMIT;
BEGIN; < list of one or more INSERT, UPDATE, of DELETE SQL statements> COMMIT;
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?
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