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.
To disable autocommit use the following statement:
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.
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).
If you need to rollback only some statment of your transaction (for example if you test a DML statements , you could use this statement:
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 ---
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