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

[John Rocha]

Upate.

I modified the logic wrapping the UPDATE in TRANSACTION/COMMIT and it got farther. this time it didn't fail with the 12006 error until approximately 182,000 entries were completed.


START TRANSACTION;
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;
COMMIT;

-=John