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

We have a customized Openbravo POS v2.2 running with a dozen workstations operating 24x7 a year around. The size of the database has grown to 10GB, and the performance started to degrade so we decided to archive old data and start fresh. According to Openbravo POS administrative guide, we performed a DELETE operation on transaction data.

delete from ticketlines;
delete from tickets;
delete from payments;  /* Delete payments before receipts */
delete from receipts;
delete from taxlines;
delete from closedcash;
delete from stockdiary;
delete from stockcurrent;
delete from reservation_customers;
delete from reservations;

Upon deleting ticketlines, "ERROR 1206 (HY000): The total number of locks exceeds the lock table size" error occurred. Also, deletion of all table data took 6 hours -- which was a BIG surprise for me. InnoDB stores the lock tables in the buffer pool, and it throws the above error when the buffer pool runs out of memory. By default, MySQL allocates 128MB of space for innodb_buffer_pool_size. To resolve the problem, you'll have to increase the size in /etc/my.cnf file.

## Edit /etc/my.cnf file, and add the following under the [mysqld] heading.
innodb_buffer_pool_size=256M

You'll have to tweak the value of innodb_buffer_pool_size as having large buffer size could introduce Fatal error: cannot allocate memory for the buffer pool problem. You'll have to restart the mysqld service in order to take this new value into effect.

# service mysqld restart