Why MySQL transactions cannot be rolled back

Why MySQL transactions cannot be rolled back

When using MySQL, if you find that the transaction cannot be rolled back, but the Hibernate, Spring, JDBC and other configurations do not have obvious problems, don't worry, first check whether the table created by MySQL has any problems, that is, the table type.

InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.

MyIASM is a new version of the IASM table and has the following extensions:

• Binary hierarchy portability.
• NULL column index.
• There are fewer fragments for Long-varying rows than the ISAM table.
• Supports large files.
• Better index compression.
• Better key? statistical distribution.
• Better and faster auto_increment processing.

The following are some differences between details and specific implementations:

1. InnoDB does not support FULLTEXT indexes.

2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.

3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.

5. the load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

Any type of table is not omnipotent. You only need to select a proper table type for the business type to maximize the performance advantage of MySQL.

In MySQL 5.0, the performance of MyISAM and InnoDB Storage engines is not very different. For InnoDB, innodb_flush_log_at_trx_commit is the main option that affects performance. If it is set to 1, therefore, data is automatically submitted each time it is inserted, resulting in a sharp decline in performance. It should be related to refreshing logs. Setting it to 0 can significantly improve the efficiency. Of course, similarly, you can submit "set autocommit = 0" in SQL to SET the performance. In addition, I also heard that setting innodb_buffer_pool_size can improve the performance of InnoDB, but I did not find it significantly improved.

Basically, we can consider using InnoDB to replace our MyISAM engine, because InnoDB has many good features, such as transaction support, stored procedures, views, row-level locking, etc, in the case of a lot of concurrency, I believe that InnoDB must be much better than MyISAM. the configuration in cnf is also critical. A good configuration can effectively accelerate your application.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: