8.1.1 MySQL Design Limitations and Tradeoffs

When using the MyISAM storage engine, MySQL uses extremely fast table locking that permits multiple readers or a single writer. The biggest problem with this storage engine occurs when you have a steady stream of mixed updates and slow selects on a single table. If this is a problem for certain tables, you can use another storage engine for them. See Chapter 14, Storage Engines.

MySQL can work with both transactional and nontransactional tables. To make it easier to work smoothly with nontransactional tables (which cannot roll back if something goes wrong), MySQL has the following rules. Note that these rules apply only when not running in strict SQL mode or if you use the IGNORE specifier for INSERT or UPDATE.

To change the preceding behaviors, you can enable stricter data handling by setting the server SQL mode appropriately. For more information about data handling, see Section 1.8.3, “How MySQL Deals with Constraints”, Section 5.1.7, “Server SQL Modes”, and Section 13.2.5, “INSERT Syntax”.