Lock table size issue when deleting on MySQL I'm attempting to delete errant records from a table based on certain dates and running into the following error:

mysql&gt; 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&gt; 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&gt; 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.
Schematically (draft code!):

CREATE PROCEDURE loop_until_done()
BEGIN
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 &lt;= records_count_after THEN
        LEAVE cycle;
    END IF;
END LOOP;
END


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()
BEGIN
REPEAT
    /* SELECT SLEEP(1) INTO @dummy; */
    DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
UNTIL NOT ROW_COUNT() 
END REPEAT;
END