InnoDB Transaction Model any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values. The  innodb_autoinc_lock_mode  variable controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations. For more information, see Section 15.6.1.6, “AUTO_INCREMENT Handling in InnoDB” . InnoDB  supports  SPATIAL  indexing of columns containing spatial data (see Section 11.4.9, “Optimizing Spatial Analysis” ). To handle locking for operations involving  SPATIAL  indexes, next-key locking does not work well to support  REPEATABLE READ  or  SERIALIZABLE  transaction isolation levels. There is no absolute ordering concept in multidimensional data, so it is not clear which is the “next”  key. To enable support of isolation levels for tables with  SPATIAL  indexes,  InnoDB  uses predicate locks. A SPATIAL  index contains minimum bounding rectangle (MBR) values, so  InnoDB  enforces consistent read on the index by setting a predicate lock on the MBR value used for a query. Other transactions cannot insert or modify a row that would match the query condition. The  InnoDB  transaction model aims to combine the best properties of a multi-versioning database with traditional two-phase locking.  InnoDB  performs locking at the row level and runs queries as nonlocking consistent reads by default, in the style of Oracle. The lock information in  InnoDB  is stored space- efficiently so that lock escalation is not needed. Typically, several users are permitted to lock every row in InnoDB  tables, or any random subset of the rows, without causing  InnoDB  memory exhaustion. Transaction isolation is one of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time. InnoDB  offers all four transaction isolation levels described by the SQL:1992 standard:  READ UNCOMMITTED ,  READ COMMITTED ,  REPEATABLE READ , and  SERIALIZABLE . The default isolation level for  InnoDB  is  REPEATABLE READ . A user can change the isolation level for a single session or for all subsequent connections with the SET TRANSACTION  statement. To set the server’s default isolation level for all connections, use the  -- transaction-isolation  option on the command line or in an option file. For detailed information about isolation levels and level-setting syntax, see Section 13.3.7, “SET TRANSACTION Statement” . InnoDB  supports each of the transaction isolation levels described here using different locking strategies. You can enforce a high degree of consistency with the default  REPEATABLE READ  level, for operations on crucial data where ACID compliance is important. Or you can relax the consistency rules with READ COMMITTED  or even  READ UNCOMMITTED , in situations such as bulk reporting where precise consistency and repeatable results are less important than minimizing the amount of overhead for locking. SERIALIZABLE  enforces even stricter rules than  REPEATABLE READ , and is used mainly in specialized situations, such as with XA transactions and for troubleshooting issues with concurrency and deadlocks. The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used. 3085 15.7.2.1 Transaction Isolation Levels 15.7.2 InnoDB Transaction Model Predicate Locks for Spatial Indexes