Ways to Start a New Transaction in MySQL

Q

How To Start a New Transaction in MySQL?

✍: FYIcenter.com

A

MySQL server offers two modes to manage transactions:

  • Autocommit On - Default mode. Can be started with "SET AUTOCOMMIT = 1" command. In this mode, every single SQL statement is a new transaction. All changes will be committed at the end of the statement execution.
  • Autocommit Off - Can be started with "SET AUTOCOMMIT = 0" command. In this mode, multiple SQL statements can be grouped into a single transaction.

When a client program starts a new connection session with the MySQL server, the client program can start a new transaction implicitly or explicitly in the following ways:

  • In "Autocommit On" mode, every executable statement will implicitly start a new single-statement transaction.
  • In "Autocommit Off" mode, the first executable statement of a new session will implicitly start a new multi-statement transaction.
  • In "Autocommit Off" mode, the first executable statement after a previous transaction has been ended will implicitly start a new multi-statement transaction.
  • In "Autocommit On" mode or "Autocommit Off" mode, "START TRANSACTION" will explicitly start a new multi-statement transaction.

 

Transaction Management: Commit or Rollback in MySQL

⇒⇒MySQL Database Tutorials

2016-10-17, 165👍, 0💬