MySQL - Disable autocommit globally

• autocommit

  Command-Line Format	--autocommit[={OFF|ON}]
  System Variable	autocommit
  Scope	Global, Session
  Dynamic	Yes
  SET_VAR Hint Applies	No
  Type	Boolean
  Default Value	ON

  The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement. See Section 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

  By default, client connections begin with autocommit set to 1. To cause clients to begin with a default of 0, set the global autocommit value by starting the server with the --autocommit=0 option. To set the variable using an option file, include these lines:

   

  [mysqld]
  autocommit=0

Copied from: https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_autocommit

6.2.2.2 Using Option Files

Most MySQL programs can read startup options from option files (sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program.

To determine whether a program reads option files, invoke it with the --help option. (For mysqld, use --verbose and --help.) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

A MySQL program started with the --no-defaults option reads no option files other than .mylogin.cnf.

A server started with the persisted_globals_load system variable disabled does not read mysqld-auto.cnf.

Many option files are plain text files, created using any text editor. The exceptions are:

• The .mylogin.cnf file that contains login path options. This is an encrypted file created by the mysql_config_editor utility. See Section 6.6.7, “mysql_config_editor — MySQL Configuration Utility”. A “login path” is an option group that permits only certain options: host, user, password, port and socket. Client programs specify which login path to read from .mylogin.cnf using the --login-path option.

  To specify an alternative login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable. This variable is used by the mysql-test-run.pl testing utility, but also is recognized by mysql_config_editor and by MySQL clients such as mysql, mysqladmin, and so forth.

• The mysqld-auto.cnf file in the data directory. This JSON-format file contains persisted system variable settings. It is created by the server upon execution of SET PERSIST or SET PERSIST_ONLY statements. See Section 7.1.9.3, “Persisted System Variables”. Management of mysqld-auto.cnf should be left to the server and not performed manually.

• Option File Processing Order

• Option File Syntax

• Option File Inclusions

Copief from: https://dev.mysql.com/doc/refman/8.4/en/option-files.html

mysql> SET PERSIST autocommit=0;
Query OK, 0 rows affected (0.00 sec)

Exit mysql and re-connect, you can find it works:

mysql> SELECT * FROM user;
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
| id | name        | email                  | hashed_password                                              | created             |
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
|  3 | ZhangZhihui | [email protected] | $2a$12$uTPD9RGtk4nt4rfS6yBF9.GcbV2vM0K557CdhJtEcwpCt1u5QQvp. | 2024-09-06 08:33:02 |
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> UPDATE user SET id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM user;
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
| id | name        | email                  | hashed_password                                              | created             |
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
|  1 | ZhangZhihui | [email protected] | $2a$12$uTPD9RGtk4nt4rfS6yBF9.GcbV2vM0K557CdhJtEcwpCt1u5QQvp. | 2024-09-06 08:33:02 |
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM user;
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
| id | name        | email                  | hashed_password                                              | created             |
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
|  3 | ZhangZhihui | [email protected] | $2a$12$uTPD9RGtk4nt4rfS6yBF9.GcbV2vM0K557CdhJtEcwpCt1u5QQvp. | 2024-09-06 08:33:02 |
+----+-------------+------------------------+--------------------------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql>