- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How can a user start new MySQL transaction?
By running the command START TRANSACTION, a user can start new MySQL transaction. The behavior of the transaction will depend upon the SQL AUTOCOMMIT mode. The default mode is ‘AUTOCOMMIT ON’ mode where each MySQL statement is considered as a complete transaction and committed by default when it finishes. It can be started by setting the session variable AUTOCOMMIT to 1 as follows −
SET AUTOCOMMIT = 1 mysql> SET AUTOCOMMIT = 1; Query OK, 0 rows affected (0.07 sec)
If a user wants to change such kind of behavior of MySQL transaction then he/she can set ‘AUTOCOMMIT OFF’ SQL mode in which the subsequent series of MySQL statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued. In this mode, the first executable statement of a new session will implicitly start a new multi-statement transaction. It can be started by setting the session variable AUTOCOMMIT to 0 as follows −
SET AUTOCOMMIT = 0 mysql> SET AUTOCOMMIT = 0; Query OK, 0 rows affected (0.00 sec)
For Transaction in InnoDB instead of using SET AUTOCOMMIT = 0, commit with COMMIT command.
In both of the SQL modes, the transaction will be started with the START TRANSACTION command as follows −
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
Actually, the above query notifies MySQL that the statements that follow should be treated as a single work unit until the transaction has been ended.
- Related Articles
- How can a user explicitly end current MySQL transaction?
- How can a user implicitly end current MySQL transaction?
- How can we implement a MySQL transaction?
- How to start a transaction in JDBC?
- What will happen to the current MySQL transaction if a START TRANSACTION command is executed in the middle of that current transaction?
- How can I start MySQL Server?
- Which statement, other than START TRANSACTION, is used for starting a transaction?
- Create a new user with password in MySQL 8?
- How can we start MySQL event scheduler?
- How can we grant privileges to a MySQL user?
- How can we revoke privileges from a MySQL user?
- How can we check the current MySQL transaction isolation level?
- How to start learning a new language?
- What happens when we use COMMIT in MySQL stored procedure and one of the transaction, under START transaction, fails?
- How would you insert a new user into a MySQL table from within Laravel?