Mysql Report

MySQL Report

Jessica Swain

Being that I am more familiar with using MySQL, I have chosen to find out how it handles certain occurrences. Deadlock is a situation in which two or more database users are waiting to use resources that are in use by the other or others. Fragmentation means that the physical ordering of the index pages on the disk is not close to the ordering of the records on the pages or there will be many unused pages on the index much like the fragmentation of data on a hard drive.

In the instance where a database suffers deadlock, InnoDB , which is the default storage engine for MySQL., automatically detects the transaction deadlock and rolls back the transaction or transactions to break the deadlock. It tries to pick the smaller transactions to roll back. When it performs a complete roll back of a transaction, all locks set by the transaction are released.

By turning on the innodb_file_per_table configuration option, which stores each newly created table in a separate tablespace file, the issues that come with storing both the tables and indexes on the same system tablespace will be prevented. Thus, there is less fragmentation within the disk file, and when the table is truncated, the space is returned to the operating system rather than still being reserved by InnoDB within the system tablespace.

It appears that MySQL uses InnoDB to automatically handle most or all of its more common occurrences or problems.

"MySQL 5.5 Reference Manual." MySQL :: 5.5 Reference Manual. 2012. Web. 18 Apr. 2012. <http://dev.mysql.com/doc/refman/5.5/en/>.