paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL, auto commit, set transaction and savepoint

Autocommit is "on" (=1) to default in MySQL, so every DML statement (Insert, Update, Delete) cannot be rollbacked. There two ways (more savepoint) to disable auto commit, only for InnoDB engine. because MyISAM doesn't support transaction. Implicit and explicit. Note: in case of DDL (like create table, indexes, alter table, etc…) in any case isn't possible to rollback.

Explicitly
To disable autocommit use the following statement:

SET autocommit=0;

And, of course, you'll need to finalize the transaction, you will use the statement COMMIT or ROLLBACK to persist the information on the DB or to revert them.

Implicit
To disable autocommit mode implicitly for a single series of statements, you have to use the START TRANSACTION statement, like that:

START TRANSACTION;
SELECT ...
UPDATE ...
COMMIT; or ROLLBACK

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK.
Note, Then the autocommit mode then reverts to its previous state (ON or 1).

Savepoint:
If you need to rollback only some statment of your transaction (for example if you test a DML statements , you could use this statement:

SAVEPOINT nameSavePoint

Tyically example:

CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;

START TRANSACTION;

INSERT
INTO    t_test
VALUES  (1);

SELECT  *
FROM    t_test;

 id
---
  1

SAVEPOINT tran2;

INSERT
INTO    t_test
VALUES  (2);

SELECT  *
FROM    t_test;

 id
---
  1
  2

ROLLBACK TO tran2;

SELECT  *
FROM    t_test;

 id
---
  1

ROLLBACK;

SELECT  *
FROM    t_test;

 id
---

Note:
If you have a lot of delete, it needs to keep track of the undo log entries for each modification. This builds up in the memory and will eventually go into disk. It's not enough disable the auto commit cause you'll need to execute the commit at the end of your transaction. Much better to use the LIMIT option into the delete bulk statement or you can use the primary key into a where condition, like this

delete from table limit 100
or
delete from table id >0  and id <1000