MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:January 3 2006 9:19am
Subject:Re: Table Lock issue on insert
View as plain text  
John,

this SQL statement:

>>>INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT (
>>>
>>>CUID
>>>
>>>) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID
>>>
>>>FROM `TV_LOG_ALL`
>>>
>>>group by 2,3;

sets locks on all the records it scans in TV_LOG_ALL. 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. The default size of the buffer pool is just 8 
MB.

Fix: modify my.cnf and increase innodb_buffer_pool_size. But do not make it 
bigger than about 70 % of your computer's RAM.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php


----- Original Message ----- 
From: "Danny Stolle" <danny-boy@stripped>
Newsgroups: mailing.database.myodbc
Sent: Monday, January 02, 2006 9:31 PM
Subject: Re: Table Lock issue on insert


> Hi John,
>
> I have looked around a bit and you might be interested in this part:
>
> InnoDB: Do not intentionally crash mysqld  if the buffer pool is
> exhausted by the lock table; return error 1206 instead ...
>
> check this link: http://dev.mysql.com/doc/refman/4.1/en/news-4-1-8.html
>
> it is quite interesting and i am trying to get this error on my mysql
> databases by changing the bufferpool and inserting a lot of data;
>
> Best regards,
>
> Danny
>
> Brittingham, John wrote:
>> They are InnoDB and max_write_lock_count=4294967295.
>> The same thing happens when I create a copy of the table.
>>
>> -----Original Message-----
>> From: Danny Stolle [mailto:danny-boy@stripped]
>> Sent: Saturday, December 31, 2005 2:59 AM
>> To: Brittingham, John; mysql@stripped
>> Subject: Re: Table Lock issue on insert
>>
>> Hi John,
>>
>> What kind of engine are you using on your table? MyIsam or InnoDB or are
>>
>> you using merged tables?
>> If you query your system variables what is your max_write_lock_count?
>>
>> If you create a copy of the table: mysql>create table cp1 like
>> USERS_PER_HOUR; and you try the insert again; is the error gone?
>>
>> Danny
>>
>>
>>
>> Brittingham, John wrote:
>>
>>>I am having trouble with table lock.
>>>
>>>The query is as follows:
>>>
>>>INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT (
>>>
>>>CUID
>>>
>>>) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID
>>>
>>>FROM `TV_LOG_ALL`
>>>
>>>group by 2,3;
>>>
>>>
>>>
>>>I keep getting this error: #1206 - The total number of locks exceeds
>>
>> the
>>
>>>lock table size
>>>
>>>
>>>
>>>How do I fix this?
>>>
>>>
>>>
>>>
>>
>>
>>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
> 

Thread
Table Lock issue on insertJohn Brittingham31 Dec
  • Re: Table Lock issue on insertDanny Stolle31 Dec
Re: Table Lock issue on insertDanny Stolle2 Jan
Re: Table Lock issue on insertDanny Stolle2 Jan
Re: Table Lock issue on insertHeikki Tuuri3 Jan