No problem with code configuration, why transactions are not rolled back (understanding MySQL Database Engine)

A few days ago in the development of a problem encountered in the process of debugging, found that the configuration in the service layer of the transaction does not work, after throwing an exception, the database update is successful, the transaction does not roll back, and then start a variety of checks to see if the spring transaction is configured correctly, Whether the transaction is turned on when the Update method is entered, and after the check finds that everything is OK, why does the transaction not roll back? The problem is in a place that's hard to think about (the tables created in the database don't support transactions).

MySQL Database default creation table engine is: MYISAM, the use of this engine table efficiency is high, but does not support transactions, so we should be in the table when it is best to manually specify the required engine according to the requirements, the following is a can support the transaction of the SQL table, you can refer to:

CREATE TABLE   Test (ID varchar (+)  PRIMARY KEY, name varchar (+), create_time datetime, Create_user varchar (+), a ddress varchar (not NULL, remark varchar) ENGINE = InnoDB DEFAULT Charset=utf8

Note:ENGINE = InnoDBThis configuration is what engine to choose, InnoDB this engine to support the transaction.

Here are a few of the common MySQL database engine description, you can understand, the next time you create a table we can choose the appropriate engine.

ISAM: Indexed sequential access method (index sequential access Mode)

ISAM is a well-defined and time-tested form of data management that, at design time, takes into account that the number of times the database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two main disadvantages of ISAM are that it does not support transaction processing or fault tolerance, and if your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application.
Default storage engine for Myisam:mysql

MyISAM is the ISAM extended format for MySQL and the default database engine. In addition to providing a number of functions for index and field management not available in ISAM, MyISAM also uses a table-locking mechanism to optimize multiple concurrent read and write operations, at the expense of the need to run the Optimize Table command frequently to restore space wasted by the updated mechanism. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space. MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most web hosting providers and internet platform providers only allow the use of the MyISAM format. An important flaw in the MyISAM format is the inability to recover data after a table is corrupted.

HEAP:

The heap allows temporary tables that reside only in memory. Residing in memory makes the heap faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before shutting down, all the data will be lost. The heap does not waste a lot of space when data rows are deleted. The heap table is useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using the table.

InnoDB:

The InnoDB database engine is a direct product of the technology that makes MySQL flexible, and the technology is the mysql++ API. Every challenge you face when using MySQL comes from the ISAM and MyISAM database engine does not support transactional processing (transaction process) or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB includes support for transactional and foreign keys, which are not available in the top two engines for two points. If your design requires accesses than either or both of these features, then you will be forced to use one of the InnoDB and Berkley engines, and we are often using InnoDB.

InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row level and also provides an Oracle-style, non-locking read in the SELECT statement, which adds multiuser deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query. This is the official description of MySQL.

If you want to know more then go online and check it out ...