MySQL Transaction failure reason

A problem with using the MySQL database in the recent project, when using Spring's transaction annotations (@transaction) to manage transactions, found that in the method of @transaction markup with transaction annotations, if an exception occurred, the transaction was not rolled back, but it was submitted directly to the database. At first, I thought it was a problem with the transaction configuration of spring, after checking it again or not, finally looked up a lot of data to find out that it was the MySQL database storage engine problem.

MySQL has a variety of storage engines, some versions (mysql5.5.5 previously default is myisam,mysql5.5.5 after the default is InnoDB) installed when the default storage engine is MyISAM, and the MyISAM storage engine does not support transaction processing, That's why a method transaction in the service layer in the project is invalidated, and the solution is to change the storage engine of the table that needs transaction management to InnoDB, which details several of the MySQL storage engines and how to modify the storage engine:

　　First, view the storage engine supported by the MySQL database

Show ENGINES;

　　

　　The statement will list all of the storage engines supported by this version of MySQL, and you can see the default storage engine as InnoDB (this is the one I modified, the default is MyISAM after installing MySQL), or you can use the following statement to view the default storage engine for MySQL:

SHOW VARIABLES like ' storage_engine ';

　　

　　Second,MyISAM storage engine

The MyISAM is based on the ISAM storage engine and extends it. It is one of the most commonly used storage engines in the Web, data warehousing, and other application environments. MyISAM has a high insertion and query speed, but does not support transactions . MyISAM Main features are:

1. Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files

2, when the deletion and update and insert operation mixed use, the dynamic size of the line to produce less fragmentation. This is done by merging adjacent deleted blocks, and if the next block is deleted, it expands to the next piece of auto-completion

3. The maximum number of indexes per MyISAM table is 64, which can be changed by recompiling. The maximum number of columns per index is 16

4, the maximum key length is 1000 bytes, which can also be compiled to change, for a key length of more than 250 bytes, a key more than 1024 bytes will be used

5. BLOBs and text columns can be indexed

6, NULL is allowed in the column of the index, this value occupies the 0~1 bytes of each key

7. All numeric key values are stored with high bytes first to allow a higher index compression

8. Each MyISAM type table has a auto_increment internal column, which is updated when the insert and update operations, and the Auto_increment column is refreshed. So, the Auto_increment column update for the MyISAM type table is faster than the InnoDB type auto_increment

9, you can put the data files and index files in different directories

10. Each character column can have a different character set

11, the table with varchar can be fixed or dynamic record length

12, varchar and char columns can be up to 64KB

Creating a database using the MyISAM engine will result in 3 files. The name of the file begins with the name of the table, the file type of the extension: frm file store table definition, data file extension. MYD (MYData), the extension of the index file. MYI (Myindex)

Third,InnoDB storage engine

　　InnoDB is the preferred engine for transactional databases, supports transaction security tables (ACID), supports row locking and foreign keys, and sees that InnoDB is the default MySQL engine. InnoDB Main features are:

1. InnoDB provides MySQL with a thing-safe (acid-compatible) storage engine with Commit, rollback, and crash resilience. InnoDB locks the row-level and also provides a non-locking read similar to Oracle in the SELECT statement. These features increase multi-user deployment and performance. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query

2. InnoDB is designed to handle the maximum performance of large amounts of data. Its CPU efficiency may be any other disk-based relational database engine lock unmatched

3. The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB its tables and indexes in a logical table space, the tablespace can contain several files (or raw disk files). This differs from the MyISAM table, such as in the MyISAM table where each table is stored in a detached file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system

4. INNODB supports foreign key integrity constraints, when storing data in a table, each table's storage is stored in the primary key order, and if no primary key is specified when the table definition is displayed, InnoDB generates a 6-byte rowid for each row, which is used as the primary key

5. InnoDB is used in many large database sites that require high performance

InnoDB do not create a directory, when using InnoDB, MySQL will create a 10MB size auto-extended data file named Ibdata1 in MySQL Data directory, and two log files of 5MB size named Ib_logfile0 and Ib_logfile1

　　four . MemoryMemory engine

The memory storage engine stores the data in the table in the RAM, providing quick access without querying and referencing other table data. The main features of memory are:

1. The memory table can have up to 32 indexes per table, 16 columns per index, and a maximum key length of 500 bytes

2, memory storage engine execution hash and btree miniature

3, you can have a non-unique key value in a memory table

4, Memory table using a fixed record length format

5. Memory does not support BLOB or text columns

6. Memory supports auto_increment columns and indexes on columns that can contain null values

7. The memory table is shared between the clients (like any other non-temporary table)

8. Memory table is stored in memory, memory table and server in the Idle query processing, the creation of internal table sharing

9. When the contents of the memory table are no longer needed, to release the RAM used by the memories table, you should execute delete from or TRUNCATE TABLE, or delete the entire table (using drop table)

　　Five, selection of storage engines

Different storage engines have their own characteristics to suit different needs, as shown in the following table:

function	MYISAM	Memory	InnoDB	Archive
Storage limits	256TB	Ram	64TB	None
Supporting Things	No	No	Yes	No
Support Full-Text indexing	Yes	No	No	No
Support Number Index	Yes	Yes	Yes	No
Support for Hash indexes	No	Yes	No	No
Support for data caching	No	N/A	Yes	No
Support for foreign keys	No	No	Yes	No

InnoDB is a good choice if you want to provide security for things (acid-compatible) capabilities for commit, rollback, crash resiliency, and require concurrency control

If the data table is used primarily for inserting and querying records, the MyISAM engine can provide high processing efficiency

If the data is only temporarily stored, the amount of data is small, and does not require a high level of data security, you can choose to save the data in memory of the storage engine, MySQL use the engine as a temporary table, the intermediate results of the query stored

If you have only insert and select operations, you can choose Archive,archive to support high concurrency inserts, but it is not transaction-safe by itself. Archive is ideal for storing archived data, such as logging information can be used archive

Which engine to use requires flexibility, multiple tables in a database can use different engines to meet a variety of performance and real needs , and using the right storage engine will improve the performance of the entire database

　　Vi. Modifying the storage engine

　　1. Modify the MySQL default storage engine to InnoDB
Turn off MySQL service: net stop MySQL
Locate the My.ini file in the MySQL installation directory:
Find Default-storage-engine=myisam instead of Default-storage-engine=innodb
Find Skip-innodb changed to #skip-innodb, that is, comment out the line, skip-innodb to ignore the meaning of InnoDB, here need to open InnoDB, it needs to be annotated
Start MySQL service: net start MySQL

2. View the storage engine used by the table

Show table status from Db_name where name= ' table_name ';

　　

3 . Modify the table's storage engine

ALTER TABLE table_name ENGINE=INNODB;

Reference: http://blog.csdn.net/zhangyuan19880606/article/details/51217952

MySQL Transaction failure reason