OS:RHEL5u4_X64
VER:MYSQL5.1.40
MASTER_ERROR.LOG 报错:
100722 11:56:19 InnoDB: Error: unlock row could not find a 4 mode lock on the record
100722 11:56:19 InnoDB: Error: unlock row could not find a 4 mode lock on the record
100722 11:56:19 InnoDB: Error: unlock row could not find a 4 mode lock on the record
从错误看,非常像BUG 41756 :
http://bugs.mysql.com/bug.php?id=41756
但条件场景不太像,我们系统变量 innodb_locks_unsafe_for_binlog=OFF,而以上BUG innodb_locks_unsafe_for_binlog=ON 才出现。
但在我们的版本中可以用以下语句重现错误:
drop table if exists tb1,tb2;
create table `tb2` (`a` int,`b` int) engine=innodb;
insert into `tb2` values (1,2),(1,2) ;
create table `tb1` (`a` int,`b` int primary key) engine=innodb;
insert into `tb1` values (1,1),(null,2),(1,3),(1,4);
select 1 from `tb1` natural join (select * from `tb2`) as `d` for update;
#报: 100722 12:13:11 InnoDB: Error: unlock row could not find a 5 mode lock on the record
select 1 from `tb1` natural join (select * from `tb2`) as `d` lock in share mode;
#报 :100722 12:17:39 InnoDB: Error: unlock row could not find a 4 mode lock on the record
MYSQL 解释: 出现这个错误的主要原因是:
In JT_EQ_REF (join_read_key()) access method,
MySQL is calling unlock_row twice for the first table (tb1) for each SELECT.
The error message is displayed on the second call.
The MySQL layer is breaking the protocol by invoking unlock_row twice for the same row.
这种情况主要是发生在需要多表关联并加锁的SQL中;
鹰眼应用中,需要加锁的select只有类似于 insert .... select 这样的语句。 询问开发后有这样的语句:
insert opsnodegroup
(nodegroup_id, nodegroup_name, modify_time) select
b.nodegroup_id,
b.nodegroup_name,
b.modify_time
from
ops_nodegroup b
left outer join
opsnodegroup a
on a.nodegroup_id = b.nodegroup_id
where
a.nodegroup_id is null
and b.nodegroup_type =39 ;
但这个语句的执行时间与报错时间不符 ;手动执行也没有报错;
鹰眼应用中,还有update tab1 ,tab2 这样的语句。 但这样的语句比较多,也无法追查;
这个BUG如果是按以上原理分析,对应用没有影响,只是在日志里报一个错误。
我也测试了,SQL语句只要有1行匹配住,就不会报错;
在以下场景下,类似以下语句会报错 ;
通过全表扫描搜索tb1(驱动表) , 但在tb1中0行匹配;
select 1 from `tb1` natural join (select * from `tb2`) as `d` for update;
select 1 from `tb1` inner join (select * from `tb2`) as `d` on tb1.a=d.a and tb1.b=d.b for update;
update tb1 ,tb2 set tb1.a=5 where tb1.a=tb2.a and tb1.b=tb2.b;
报:InnoDB: Error: unlock row could not find a 5 mode lock on the record
以上BUG 在VERSION 5.1.41有修复;
后续继续观察。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/703656/viewspace-1035477/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/703656/viewspace-1035477/