"The total number of locks exceeds the lock table size" Deleting 267 Records

Question

I'm trying to delete 267 records out of about 40 million. The query looks like:

delete from pricedata
where
pricedate > '20120413'

pricedate is a char(8) field.

I know about adjusting innodb_buffer_pool_size, but if I can do

select from pricedata
where
pricedate > '20120413'

and get 267 records (and that's all there are), no errors, why does it choke on the delete?

And if adjusting innodb_buffer_pool_size doesn't work, what should I do?

Answer 1

It seems that you don't have an index on pricedate (or MySQL does not use this index for some reason).

With REPEATABLE READ (the default transaction isolation level), InnoDB places shared locks on the records read and filtered out by the query and it seems you don't have enough space for 40M locks.

To work around this problem use any of these solutions:

1. Create the index on pricedate if it's not there (may take time)

2. Break your query into smaller chunks:

   DELETE
   FROM    pricedata
   WHERE   pricedate > '20120413'
           AND id BETWEEN 1 AND 1000000
   
   DELETE
   FROM    pricedata
   WHERE   pricedate > '20120413'
           AND id BETWEEN 1000001 AND 2000000
   

   etc. (change the id ranges as needed). Note that each statement should be run in its own transaction (don't forget to commit after each statement if AUTOCOMMIT is off).

3. Run the DELETE query with READ COMMITTED transaction isolation level. It will make InnoDB lift locks from the records as soon as they are read. This will not work if you are using binary log in statement mode and don't allow binlog-unsafe queries (this is the default setting).