ERROR 1206 The total number of locks exceeds the lock table size

[John Rocha]

Hello All,

I have a stored procedure that I'm using during a table change and it fails
with the error message:

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

I've poked around the web and found entries here and on stackoverflow and the
concensous seems to be to increase the size of the innodb_buffer_pool_size
and/or reduce the number of records added/changed in a single action.

Sadly I don't have the option or ability to increase the
innodb_buffer_pool_size, so I'm trying to reduce the number of records changed
per action. However that is failing too.


We had a simple time partitioned table, event_notify_ocurrence_cache, with two
columns:
---------------------------------------------------------------------------
CREATE TABLE `event_notify_occurrence_cache` (
`timestamp` datetime NOT NULL,
`xml` text NOT NULL,
KEY `idx_timestamp` (`timestamp`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(`timestamp`)
(PARTITION p2012_12_10__15_00_00UTC VALUES LESS THAN ('2012-12-10 16:00:00') ENGINE = InnoDB,
PARTITION p2012_12_10__16_00_00UTC VALUES LESS THAN ('2012-12-10 17:00:00') ENGINE = InnoDB,
...
...
...
---------------------------------------------------------------------------



We need to change this to have an 'event type' column, where the event type is
part of the string in the xml column. Doing "LIKE" searches is noticeably slow
so we want to add another indexed column for the event type. We want to change
to the following:
---------------------------------------------------------------------------
CREATE TABLE `event_notify_occurrence_cache` (
`event_type_value` smallint(5) unsigned NOT NULL,
`timestamp` datetime NOT NULL,
`xml` text NOT NULL,
KEY `idx_timestamp` (`timestamp`),
KEY `idx_event_type_value` (`event_type_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(`timestamp`)
(PARTITION p2012_12_10__15_00_00UTC VALUES LESS THAN ('2012-12-10 16:00:00') ENGINE = InnoDB,
PARTITION p2012_12_10__16_00_00UTC VALUES LESS THAN ('2012-12-10 17:00:00') ENGINE = InnoDB,
...
...
---------------------------------------------------------------------------


When we alter the table to match this the event_type_value column gets
populated, as expected and desired, with an invalid event type of zero(0).

This all works so far.

So the next step is to do a series of updates on the table for each possible
event type using logic that essentially equates to:

UPDATE event_notify_occurrence_cache
SET event_type_value = 1
WHERE event_type_value = 0 AND xml LIKE "%<eventType>1</eventType>";

Repeating this for each possible event type.

This fails for one of our test cases where we have 24 partitions with a total
of 9,110,000 records where the first match will hit about 201,827 records.

I've tried using the LIMIT statement with the UPDATE clause and looping until
all entries for a given event type are completed but that doesn't work.

I first tried with a limit of 5000 and it failed after 150,000 records (as per
debug output). I set the limit down to 1000 and and it now fails after 152,000
records.

It's almost like the lock table is per stored procedure rather than per
statement.

One stack overflow post suggested locking the table being written to. I tried
using the [LOCK TABLE] command but it still didn't work.


I am using server version 5.5.8-enterprise-commercial-advanced-log

An example of one of my stored procedure logic attemts is as follows
---------------------------------------------------------------------------
....
OPEN etype;
REPEAT
FETCH etype INTO etype_name, etype_value;

IF done != 1 THEN
SET query_str :=
CONCAT('%<eventType>', etype_name, '</eventType>%');

SET changes_not_done := 1;
WHILE changes_not_done != 0 DO

-- /*** JRR MORE WORK TO DO ***/
SELECT CONCAT(CURRENT_TIMESTAMP(), ' ETYPE:', etype_name,
' CHANGES_NOT_DONE:', changes_not_done)
AS 'Debug Msg:';


UPDATE `UMS`.`event_notify_occurrence_cache`
SET event_type_value=etype_value
WHERE event_type_value=0 AND xml LIKE query_str
LIMIT 1000;

-- set changes_not_done to the number of rows modified by the
-- previous command. If it's zero, then we are done. If it's
-- non-zero then we have more to do.
SELECT ROW_COUNT() INTO changes_not_done;

END WHILE;
END IF;

UNTIL done = 1
END REPEAT;
CLOSE etype;
...
---------------------------------------------------------------------------


I have a table of event types, I loop over that, and for each event type I
search the notfications cache to process it.

Thanks in advance for your help,

-=John