Environment

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.37-log |
+------------+

Preparing the Test Case

mysql> CREATE TABLE trx_demo(
         pk   INT PRIMARY KEY,
         tag  VARCHAR(20)
       ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.29 sec)

mysql> SHOW TABLE STATUS LIKE 'trx_demo'\G
*************************** 1. row ***************************
           Name: trx_demo
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384

Understanding Autocommit Behaviour

By default every statement in MySQL runs in its own transaction and is automatically committed. To group several statements into a single logical unit you must explicitly start a transaction.

Attempt 1 – Misusing BEGIN

mysql> BEGIN
    -> INSERT INTO trx_demo VALUES (1,'segment'),(2,'tablespace');
ERROR 1064 (42000): You have an error in your SQL syntax...

The parser treats BEGIN as a complete statement and the following INSERT as a new, separate command. Because autocommit is on, the INSERT commits immediately, so a subsequent ROLLBACK has nothing to undo.

Attempt 2 – Correct Syntax

mysql> TRUNCATE trx_demo;
Query OK, 0 rows affected (0.06 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO trx_demo VALUES (1,'segment'),(2,'tablespace');
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM trx_demo;
+----+------------+
| pk | tag        |
+----+------------+
|  1 | segment    |
|  2 | tablespace |
+----+------------+

mysql> ROLLBACK;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM trx_demo;
Empty set (0.00 sec)

Attempt 3 – Using START TRANSACTION

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO trx_demo VALUES (1,'segment'),(2,'tablespace');
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM trx_demo;
+----+------------+
| pk | tag        |
+----+------------+
|  1 | segment    |
|  2 | tablespace |
+----+------------+

mysql> ROLLBACK;
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM trx_demo;
Empty set (0.00 sec)

Key Takeaways

• SET autocommit = 0 disables automatic commits for the session.
• BEGIN or START TRANSACTION opens an explicit transaction.
• COMMIT makes all changes permanent; ROLLBACK discards them.
• Without an explicit tarnsaction, each statement is autocommitted.