The total number of locks exceeds the lock table size

Question

首先感谢ACMAIN_CHM 在以往帖子中的所有帮助
结了所有的帖子重开一贴
在运行存储过程的时候出现这个问题
调试成功，但是运行出错
mysql> call MsgContent_pro//
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
mysql> 
关键的两个参数修改为
innodb_buffer_pool_size＝800M 
max_write_lock_count 是4294967295 附我的存储过程（再谢ACMAIN_CHM ）
mysql> delimiter //
mysql>
mysql> create procedure MsgContent_pro ()
    -> begin
    ->  repeat
    -> update tbContent
    ->  set msgID=(     select mi from (select msgContent,max(msgID) as ma,min(msgID) as mi
    ->          from tbMsgContent
    ->          group by msgContent
    ->          having count(msgContent)>=2
    ->          ) c where ma=tbContent.msgID)
    ->  where msgID in (select ma from (select msgContent,max(msgID) as ma,min(msgID) as mi
    ->          from tbMsgContent
    ->          group by msgContent
    ->          having count(msgContent)>=2
    ->          ) c )
    ->  order by tbContent.msgID
    ->  limit 100;
    ->  until row_count()=0 end repeat;
    ->  delete a from tbMsgContent a inner join tbMsgContent b
    ->          on a.msgContent=b.msgContent and a.msgID>b.msgID;
    -> end;
    ->
    -> //
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> delimiter ;
mysql> call MsgContent_pro();
Query OK, 0 rows affected (0.06 sec)mysql>怎么解决，拜谢

Answer 1

能提供下数据量么？尤其是那个repeat中可能涉及多少行记录

Answer 2

应该是你的数据量太大，你的表中数据是什么？ 能否再说明一遍你想实现的操作，我记得好象是删除什么重复记录，然后保留最小的。建议再贴一下数据。表结构我找到了。create table tbMsgContent
(
   msgID                bigint not null,
   msgContent           tinytext not null,
   primary key (msgID)
);
create table tbContent
(
   reID                 bigint not null,
   msgTypeID            integer not null,
   isRequest            boolean not null,
   msgID                bigint not null,
   textID               bigint,
   fileID               bigint
);delimiter //create procedure MsgContent_pro ()
begin
 repeat
   update tbContent
   set msgID=(     select mi from (select msgContent,max(msgID) as ma,min(msgID) as mi
         from tbMsgContent
         group by msgContent
         having count(msgContent)>=2
         ) c where ma=tbContent.msgID)
   where msgID in (select ma from (select msgContent,max(msgID) as ma,min(msgID) as mi
         from tbMsgContent
         group by msgContent
         having count(msgContent)>=2
         ) c )
   order by tbContent.msgID
   limit 100;
 until row_count()=0 end repeat;
 delete a from tbMsgContent a inner join tbMsgContent b
         on a.msgContent=b.msgContent and a.msgID>b.msgID;
end;//delimiter ;

Answer 3

对表tbMsgContent去重 
msgID是唯一，但是msgContent有可能重复，所以要对msgContent去重，保留msgID最小的那项
因为表tbMsgContent中内容改变了，所以要修改tbContent中msgID项，将其改为相应的最小msgID
表tbContent中的其他项是去其他表联系的
所以涉及到该存储过程的去重问题，只是表tbMsgContent和表tbContent中的msgID项

Answer 4

数据量很大
表tbMsgContent中目前有65410659条数据
表tbContent中目前有420000条数据

Answer 5

看一下这个语句要花多久？ 65，410，659 这么多记录，并且你的 msgContent 上没有索引，估计要很多时间select * from tbMsgContent t 
where exists (select 1 from tbMsgContent where msgContent=a.msgContent)
order by msgContent,msgID;请看一下这个速度，如果可行的话，则看看用游标来解决行不行。

Answer 6

是这样么？
select * from tbMsgContent t 
where exists (select 1 from tbMsgContent where msgContent=t.msgContent)
order by msgContent,msgID;

Answer 7

换一句试一下吧。select * from tbMsgContent where tbMsgContent='adfewr234adfa safewr' order by msgID;随便找一个有多个记录的试一下这句的执行时间。
然后再看这句的时间。select * from tbMsgContent order by msgContent desc limit 20;

Answer 8

222603 rows in set (2 min 5.20 sec)20 rows in set (6 min 8.23 sec)

Answer 9

6min8.23 sec这种更新操作你是一次性工作还是定期日常性工作（比如每天一次，或每小时一次？）或者说你能允许这个过程跑多久？再看一下这个语句的时间。(估计得跑几个小时)看看能不能在20分钟内得到结果
select msgContent,min(msgID)
 from tbMsgContent 
 group by msgContent 
 having count(*)>1
 否则只能用 select * from tbMsgContent order by msgConten 做为游标来逐条判断更新了。

Answer 10

病了一个礼拜，刚回归，汗
执行完下列代码
select msgContent,min(msgID)
 from tbMsgContent 
 group by msgContent 
 having count(*)>1全部变成乱码
时间大概是1个小时45分钟

Answer 11

病了一个礼拜，刚回归，汗 
执行完下列代码 SQL codeselect msgContent,min(msgID)
 from tbMsgContent 
 group by msgContent 
 having count(*)>1全部变成乱码 
时间大概是1个小时45分钟 

Answer 12

1小时45分钟也有些太长了。那你只能试试用 select * from tbMsgContent order by msgConten 做为游标来逐条判断更新了

Answer 13

ACMAIN_CHM 
上面那个存储过程有问题啊
例如
在表tbMsgContent中有，内容重复的MsgID有1 2 3 4，也就是说得在表tbContent中修改msgID是2 3 4的项
上面的这个存储过程只是修改了msgID是4的那一项，而msgID是2 3都没有修改
怎么办？