Tables For related information, see Section 15.7.2.2, “autocommit, Commit, and Rollback” . 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. 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” . 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. 3031 Cloning the Structure of a Table Converting an Existing Table Storage Layout Handling Deadlocks •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.