What are the reasons why Mysql transactions cannot be rolled back _mysql

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

InnoDB and MyISAM are the most commonly used two table types in MySQL, each with its own pros and cons, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing, such as transaction processing, but InnoDB type support. Tables of the MyISAM type emphasize performance, which executes more than INNODB types faster, but does not provide transactional support, while InnoDB provides transactional support for advanced database features such as foreign keys.

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

Portability of the binary hierarchy.

The null column index.

Fewer fragments are available for variable-length rows than for ISAM tables.

Support for large files.

Better index compression.

A better key? Statistical distribution.

Better and faster auto_increment processing.

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

1. InnoDB does not support fulltext types of indexes.

2. InnoDB does not save the exact number of rows in the table, that is, when the select count (*) from table is executed, INNODB scans the entire table to calculate the number of rows, but MyISAM simply reads out the number of saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.

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

4. When you delete the From table, InnoDB does not re-establish the table, but deletes one row at a time.

5. The LOAD table from master operation does not work for InnoDB, and the solution is to first change the InnoDB table to a MyISAM table, import the data and then change it to the InnoDB table, but not the table for the extra InnoDB attributes (such as foreign keys) used.

In addition, row locks on innodb tables are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the entire table, such as 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, in order to maximize the performance of MySQL advantage.

In MySQL 5.0, MyISAM and InnoDB storage engine performance difference is not very large, for InnoDB, the impact of performance is mainly innodb_flush_log_at_trx_commit this option, if set to 1, Then every time you insert the 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 SQL submitted "Set autocommit = 0" To set to achieve good performance. In addition, it is also heard that by setting up innodb_buffer_pool_size can improve the performance of InnoDB, but I test found no particularly noticeable improvement.

Basically we can consider using InnoDB to replace our MyISAM engine, because InnoDB own many good characteristics, such as transaction support, stored procedure, view, row level lock and so on, in many cases, believe that 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.