(A late answer, but alwayx good to have it when people find this issue in google)
A solution without having to alter the innodb_buffer_pool_size or creating an index can be to limit the amount of rows to be deleted.
So, in your case DELETE from pricedata where pricedata > '20120413' limit 100;
for example.
This will remove 100 rows and leave 167 behind. So, you can run the same query again and delete another 100.
For the last 67 it's tricky... when the amount of rows left in the database is less than the given limit you will again end up with the error about the number of locks. Probably because the server will search for more matching rows to fill up to the 100.
In this case, use limit 67
to delete the last part.
(Ofcourse you could use limit 267
already in the beginning as well)
And for those who like to script... a nice example I used in a bash script to cleanup old data :
# Count the number of rows left to be deleted
QUERY="select count(*) from pricedata where pricedata > '20120413';"
AMOUNT=`${MYSQL} -u ${MYSQL_USER} -p${MYSQL_PWD} -e "${QUERY}" ${DB} | tail -1`
ERROR=0
while [ ${AMOUNT} -gt 0 -a ${ERROR} -eq 0 ]
do
${LOGGER} " ${AMOUNT} rows left to delete"
if [ ${AMOUNT} -lt 1000 ]
then
LIMIT=${AMOUNT}
else
LIMIT=1000
fi
QUERY="delete low_priority from pricedata where pricedata > '20120413' limit ${LIMIT};"
${MYSQL} -u ${MYSQL_USER} -p${MYSQL_PWD} -e "${QUERY}" ${DB}
STATUS=$?
if [ ${STATUS} -ne 0 ]
then
${LOGGER} "Cleanup failed for ${TABLE}"
ERROR=1
fi
QUERY="select count(*) from pricedata where pricedata > '20120413';"
AMOUNT=`${MYSQL} -u ${MYSQL_USER} -p${MYSQL_PWD} -e "${QUERY}" ${DB} | tail -1`
done