autocommit

SET autocommit=0;

SET autocommit=1;

-- Enable autocommit (default).
SET autocommit=1;
-- In an implicit transaction, insert a row, triggering autocommit.
INSERT INTO autocommit_test values (1, 'autocommit=1, implicit transaction autocommit');
-- Begin an explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (2, 'autocommit=1, explicit transaction explicit commit');
-- Commit the transaction.
COMMIT;
-- Begin an explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (3, 'autocommit=1, explicit transaction explicit rollback');
-- Explicitly rollback the transaction.
-- Row with id=3 will not appear in final output.
ROLLBACK;
-- In an implicit transaction, insert a row, triggering autocommit.
INSERT INTO autocommit_test values (4, 'autocommit=1, implicit transaction autocommit');
-- In an implicit transaction, insert a row, triggering autocommit.
INSERT INTO autocommit_test values (5, 'autocommit=1, implicit transaction autocommit');
-- Perform rollback.
-- There is no effect since prior transactions were autocommitted.
ROLLBACK;
-- Display the resulting table.
SELECT * FROM autocommit_test;

-- Disable autocommit.
SET autocommit=0;
-- In an implicit transaction, insert a row.
INSERT INTO autocommit_test values (6, 'autocommit=0, implicit transaction commit by next transaction start');
-- Prior implicit transaction is committed when the next transaction is started.
BEGIN;
-- Perform rollback.
-- There is no effect since the prior transaction was committed.
ROLLBACK;
-- Begin a new explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (7, 'autocommit=0, explicit transaction explicit commit');
-- Explicitly commit the explicit transaction.
COMMIT;
-- Start an explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (8, 'autocommit=0, explicit transaction rolled back');
-- Perform explicit rollback of the explicit transaction.
-- Row with id=8 will not appear in output.
ROLLBACK;
-- In an implicit transaction, insert a row.
INSERT INTO autocommit_test values (9, 'autocommit=0, implicit transaction rolled back');
-- In the same implicit transaction, insert another row.
INSERT INTO autocommit_test values (10, 'autocommit=0, implicit transaction rolled back');
-- Perform explicit rollback of the implicit transaction.
-- Rows with id=9 and id=10 will not appear in output.
ROLLBACK;
-- Display the resulting table
SELECT * FROM autocommit_test WHERE id>5;