• MySQL知识库 :: innodb
  • ERROR 1206 (HY000) at line %d: The total number of locks exceeds the lock table size

  • Discussion

    InnoDB allocates a fixed size area for row locking information in RAM. The size of this area is 5 * 4 * NBLOCKS, where NBLOCKS is the number of pages in the buffer pool, equal to it size in bytes divided by 16384.

    Running out of space for locks usually indicates that the small default size of the InnoDB buffer pool is being used or operations on very large tables are being attempted with a huge number of row locks, perhaps comparing several large tables.

    In a dedicated system you can use up to 80% of the total RAM in the system for the InnoDB buffer pool. The example files my-large.cnf, my-huge.cnf and my-innodb-heavy-4G.cnf give examples of my.cnf settings you may want to use for various amounts of RAM allocated to MySQL.

    Solution

    If you are already using a suitable size and the operation you're attempting is rare, you may want to temporarily increase the RAM allocated to the buffer pool.

    If that still doesn't work, you may need to split your work into several pieces, increase the amount of RAM in the system, possibly change from a 32 bit to 64 bit system to allow you to use the RAM or change the MySQL source code to increase the amount of RAM allocated to the locking information. Temporarily copying the data to work tables using the MyISAM storage engine may allow you to do the work without having to use the locking system that InnoDB needs to use to support its ACID properties.