Why MySQL transactions cannot be rolled back

When using MySQL, if you find that transactions cannot be rolled back, but hibernate, Spring, JDBC and other configurations do not have obvious problems, do not fret, first look at the MySQL created table there is no problem, that is, the type of table.

InnoDB and MyISAM are the two most common table types used in MySQL, each with its pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

Myiasm is a new version of the Iasm table, with the following extensions:

Portability at the binary level.

NULL column index.

There is less fragmentation than the ISAM table for variable-length rows.

Support for large files.

Better index compression.

A better key? Statistical distribution.

Better and faster auto_increment processing.

The following are some of the details and the specific implementation differences:

1. InnoDB does not support indexes of fulltext types.

2. InnoDB does not save the exact number of rows in the table, that is, when the select count (*) from table is executed, InnoDB will scan through the entire table to calculate how many rows, but MyISAM simply reads out the number of rows saved. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.

3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.

4. When you delete from table, InnoDB does not reestablish the table, but deletes one row at a time.

5. The LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "%aaa%"

Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, to maximize the performance advantage of MySQL.

In MySQL 5.0, MyISAM and InnoDB storage engine performance difference is not very large, for InnoDB, the main impact of performance is innodb_flush_log_at_trx_commit this option, if set to 1, Then each time you insert data will be automatically submitted, resulting in a sharp decline in performance, should be related to the refresh log, set to 0 efficiency can see a significant increase, of course, you can also submit "Set autocommit = 0" in SQL to set up to achieve good performance. In addition, I have heard that setting up Innodb_buffer_pool_size can improve the performance of InnoDB, but I have found no particularly significant improvement in my test.

Basically we can consider using InnoDB to replace our MyISAM engine, because InnoDB itself a lot of good features, such as transaction support, stored procedures, views, row-level locking and so on, in the case of a lot of concurrency, I believe InnoDB performance is certainly much stronger than MyISAM, of course , the corresponding configuration in the MY.CNF is also more critical, good configuration, can effectively accelerate your application.