MySQL Error 1206

“The total number of locks exceeds the lock table size”
I encountered this error when executing the SQL below.

DELETE perfdata_service_raw
FROM perfdata_service_raw, perfdata_host
WHERE perfdata_service_raw.host_name = perfdata_host.host_name AND
perfdata_host.is_deleted = 1

Googled to find a resolution and found this link where the resolution is to increase the innodb_buffer_pool_size variable in /etc/my.cnf. The default value is 8M, so I set it to 256M, restart the mysqld service (service mysqld restart), and the problem is resolved.

[mysqld]
set-variable=innodb_buffer_pool_size=256M

The part I want to understand is what this variable has to do with table locks. The description of the innodb_buffer_pool_size variable in the MySQL 4.x Documentation states:

“The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.”

I continue to search, but not finding anything at the moment that will help me understand how lock table size and innodb_buffer_pool_size are related. The quest for an answer continues …

Update (21-Oct-2006)
Found an explanation here which says:

“The SQL statement sets locks on all the records it scans. If you have a small buffer pool, then the InnoDB lock table may indeed grow so big that it does not fit in the buffer pool. “

I want to understand more about the InnoDb lock table, so a little more research will need to be done.

Update (23-Oct-2006)
Found what I was looking for about the InnoDB lock table and it’s behavior at MySQL Performance Blog. It gives some background on the InnoDB lock table:

“in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set.”