Movatterモバイル変換

How do transactions work in MySQL? How does MySQL support transactions?

MySQL started supporting transactions in version 3.23.0. In MySQL, support for transactions was added using new storage engines – InnoDB and Berkeley DB. The reason that a new storage engine was used to add transaction support was because of upward compatibility – so that the software written to support transactions would also be able to run successfully on newer versions of the software.

Does MyISAM support transactions?

MyISAM is the default storage engine in MySQL, and if a table is created using the MyISAM storage engine, then that table does not actually have transaction support.

If you want to enable transaction support in MySQL then you must explicitly say that you want to use either the BDB (which stands for Berkeley DB) or InnoDB storage engine when creating a table. Here is an example of how to say which storage engine to use in MySQL:

How to specify to use InnoDB or BDB in MySQL

CREATE TABLE EMPLOYEE(  EMPLOYEE_ID   CHAR(5)  NOT NULL,   EMPLOYEE_NAME VARCHAR(50) NOT NULL,PRIMARY KEY (EMPLOYEE_ID))ENGINE = INNODB;

If an when you decide to use a different storage engine in MySQL, there are a lot of considerations to take into account, because different storage engines behave differently. Read up on the MySQL documentation to see what exactly is different.

The two modes of transaction support in MySQL

If a table is created with one of the storage engines that supports transactions in MySQL – either BDB or InnoDB – then there are two modes in which transaction support is available:

• Autocommit mode. You can turn autocommit mode on or off with a SET statement. If it’s set to 0 then that turns it off, and a 1 will turn it on. Here’s what the SQL would look like for that:

SET AUTOCOMMIT=0;SET AUTOCOMMIT=1;

• Implicit mode. If autocommit mode is turned off then implicit mode will be in place. These SQL statements can be used in implicit transaction mode:

COMMITROLLBACKSAVEPOINTSTART TRANSACTION