I'm attempting to delete errant records from a table based on certain dates and running into the following error:

mysql> delete from myTable where dataDate in ('2011-07-12');
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

The table doesn't yet have indexes (this is post-load clean-up), and consists of ~2B records:

mysql> select table_rows from information_schema.tables where table_name = 'myTable';
| table_rows |
| 2073294315 |
1 row in set (0.51 sec)

Suggestions I found via search suggested upping innodb_buffer_pool_size but I've already upped default pool size by 8x in order to get the data to load in reasonable time and current sits at 512MB:

mysql> select @@innodb_buffer_pool_size;
| @@innodb_buffer_pool_size |
|                1073741824 |
1 row in set (0.00 sec)

I'm planning to reload data for 3 dates (~3M records) after deleting the errant records. Wondering if I should index the table first or if there are other adjustments that will allow this.

  • how much ram do you have available?- because 1G is small (how much does your phone have), especially if your deleting lots of records afrom a moderate table. How many records are being deleted? select count(*) from myTable where dataDate in ('2011-07-12'); Is dataDate at the beginning of any of the indexes (assumed not based on your indexing question)? How often are you planning to reload? Do you have any other date related queries against this data that would use this index? – danblack Mar 11 '20 at 6:12
  • Hi @danblack, 16G total, about 8G available. Single date constitutes ~1M records, haven't done any kind of processing against the data yet. – Chris Mar 11 '20 at 6:37
  • 1
    You may try to delete with chunks - delete from myTable where dataDate in ('2011-07-12') LIMIT ?????; until affected rows=0. In SP... – Akina Mar 11 '20 at 6:37
  • @Akina, how would I process the 2nd, 3rd, nth chunk in that case? – Chris Mar 11 '20 at 7:28
  • Is this even using InnoDB ? .. if a delete exceeds lock-table size .. it kinda smells strongly for MyIsam .. – eagle275 Mar 11 '20 at 13:57

Schematically (draft code!):

CREATE PROCEDURE loop_until_done()
DECLARE records_count_before BIGINT;
DECLARE records_count_after BIGINT;
SELECT COUNT(*) FROM myTable INTO records_count_after;
cycle: LOOP
    SET records_count_before = records_count_after;
    DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
    /* SELECT SLEEP(1) INTO records_count_after; */
    SELECT COUNT(*) FROM myTable INTO records_count_after;
    IF records_count_before <= records_count_after THEN
        LEAVE cycle;
    END IF;

It is safe to add SELECT into some service table for some flag value which stops the operation. For example this can be a delay value (SLEEP argument) - its change may change deletion speed and server loading during it, and the value above some predefined value (or maybe zero) is a signal to leave cycle and stop the deletion operation unconditionally.

Simplified code according to danblack's recommendation:

CREATE PROCEDURE loop_until_done()
    /* SELECT SLEEP(1) INTO @dummy; */
    DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
  • 1
    Or use ROW_COUNT() = 0 to determine the end of the loop. – danblack Mar 12 '20 at 2:05
  • @danblack Thanks, updated. (I don't like ROW_COUNT() reflexively, so I often forget about it...). – Akina Mar 12 '20 at 4:42
  • simplifies it more that I thought (probably lack of stored procedure writing experience). nit - enabling the SLEEP causes ROW_COUNT() to return -1 so reordering the statements. – danblack Mar 12 '20 at 5:09
  • 1
    @danblack That's why I do not like indirect methods... updated. – Akina Mar 12 '20 at 5:40
  • NO. Without INDEX(dataDate), the DELETE` will take longer and longer. Each SELECT COUNT(*) will do a full table scan. – Rick James Mar 13 '20 at 4:56
  • Based on 8G available, set innodb_buffer_pool_size = 6G.

  • If you are using MyISAM, switch to InnoDB.

  • Do add the index. It is usually faster to add it after populating the table. (Still, it will take a long time unless you have 8.0.)

  • See this for multiple techniques for bulk deletion: http://mysql.rjweb.org/doc.php/deletebig

  • Phrase the test thus: dataDate = '2011-07-12'

  • If dataDate is a DATETIME, then you need a range test.

  • SELECT SLEEP(1); does not need an INTO clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.