How To Start a New Transaction

Q

How To Start a New Transaction? - MySQL FAQs - Transaction Management: Commit or Rollback

✍: 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.

2007-05-11, 4635👍, 0💬