When Spring declarative transactions are used, it is found that abnormal transactions can be rolled back when "update" is done. However, when "insert" is executed, the background log shows that the data has been saved in the database although it is rolled back. It was originally thought that there was a configuration error, but later it was found that it was the problem of Mysql storage engine. The Mysql version we use is "5.1.73", and the default storage engine is "MyISAM".

View mysql version:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

View mysql storage engine

mysql> SHOW ENGINES;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

The reason is clear here. When creating a table, the default is "MyISAM". The differences between "MyISAM" and "InnoDB" and other storage engines will not be expanded here. We just need to change our table storage engine to "InnoDB".

SHOW TABLE STATUS FROM database;


ALTER TABLE table_name ENGINE = InnoDB;

mysql> ALTER TABLE table_name ENGINE = InnoDB;
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

If it is the main database, it is better to change the default storage engine to "InnoDB".

If you feel that the article is helpful to you, you can pay attention to wechat public account [colorful color] to encourage you