- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can a user start new MySQL transaction?
152 Lectures 16 hours
87 Lectures 5.5 hours
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.
- How can a user explicitly end current MySQL transaction?
- How can a user implicitly end current MySQL transaction?
- How to start a transaction in JDBC?
- How can we implement a MySQL transaction?
- Which statement, other than START TRANSACTION, is used for starting a transaction?
- How can I start MySQL Server?
- What will happen to the current MySQL transaction if a START TRANSACTION command is executed in the middle of that current transaction?
- How to start learning a new language?
- How can we start MySQL event scheduler?
- Create a new user with password in MySQL 8?
- 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?
- Authorize a new user in SAP
- What happens when we use COMMIT in MySQL stored procedure and one of the transaction, under START transaction, fails?