•Remember that even a SELECT  statement opens a transaction, so after running some report or debugging queries in an interactive mysql  session, either issue a COMMIT  or close the mysql  session. 

For related information, see Section 15.7.2.2, “autocommit, Commit, and Rollback” . 

## Handling Deadlocks 

You might see warning messages referring to “deadlocks”  in the MySQL error log, or the output of SHOW ENGINE INNODB STATUS . A deadlock is not a serious issue for InnoDB  tables, and often does not require any corrective action. When two transactions start modifying multiple tables, accessing the tables in a different order, they can reach a state where each transaction is waiting for the other and neither can proceed. When deadlock detection is enabled (the default), MySQL immediately detects this condition and cancels (rolls back) the “smaller”  transaction, allowing the other to proceed. If deadlock detection is disabled using the innodb_deadlock_detect  configuration option, InnoDB  relies on the innodb_lock_wait_timeout  setting to roll back transactions in case of a deadlock. 

Either way, your applications need error-handling logic to restart a transaction that is forcibly cancelled due to a deadlock. When you re-issue the same SQL statements as before, the original timing issue no longer applies. Either the other transaction has already finished and yours can proceed, or the other transaction is still in progress and your transaction waits until it finishes. 

If deadlock warnings occur constantly, you might review the application code to reorder the SQL operations in a consistent way, or to shorten the transactions. You can test with the innodb_print_all_deadlocks  option enabled to see all deadlock warnings in the MySQL error log, rather than only the last warning in the SHOW ENGINE INNODB STATUS  output. 

## Storage Layout 

For more information, see Section 15.7.5, “Deadlocks in InnoDB” . 

To get the best performance from InnoDB  tables, you can adjust a number of parameters related to storage layout. 

When you convert MyISAM  tables that are large, frequently accessed, and hold vital data, investigate and consider the innodb_file_per_table  and innodb_page_size  variables, and the ROW_FORMAT  and KEY_BLOCK_SIZE  clauses of the CREATE TABLE  statement. 

During your initial experiments, the most important setting is innodb_file_per_table . When this setting is enabled, which is the default, new InnoDB  tables are implicitly created in file-per-table tablespaces. In contrast with the InnoDB  system tablespace, file-per-table tablespaces allow disk space to be reclaimed by the operating system when a table is truncated or dropped. File-per-table tablespaces also support DYNAMIC and COMPRESSED row formats and associated features such as table compression, efficient off-page storage for long variable-length columns, and large index prefixes. For more information, see Section 15.6.3.2, “File-Per-Table Tablespaces” . 

You can also store InnoDB  tables in a shared general tablespace, which support multiple tables and all row formats. For more information, see Section 15.6.3.3, “General Tablespaces” . 

## Converting an Existing Table 

## Cloning the Structure of a Table 

To convert a non- InnoDB  table to use InnoDB  use ALTER TABLE : 

ALTER TABLE table_name  ENGINE=InnoDB; 

You might make an InnoDB  table that is a clone of a MyISAM table, rather than using ALTER TABLE  to perform conversion, to test the old and new table side-by-side before switching. 

