MySQL Bugs: #24970: Falcon: error 1020 not handled

Bug #24970	Falcon: error 1020 not handled
Submitted:	11 Dec 2006 19:20	Modified:	21 Dec 2007 10:22
Reporter:	Peter Gulutzan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.1.13-falcon-alpha-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	cwp, falcon

Description:
I have a stored procedure which will surely cause
ERROR 1020 (HY000): Record has changed since last read in table 't25'
if there are two interleaving transactions --
but there is a CONTINUE handler for error 1020.

I call the procedure from two interleaving transactions.

The CONTINUE handler does not catch the error.
Instead, I see an EXIT.

How to repeat:
Set up two connections. Call them T1 and T2:

On T1, do:

create table t25 (s1 int, s2 int) engine=falcon;
insert into t25 values (1,0);
delimiter //
create procedure p25 ()
begin
  declare v int default 0;
  while v < 10000 do
    select v;
    begin
      declare error_count int default 0;
      declare continue handler for 1020
      begin
        select 'error',error_count;
        if error_count = 0 then set error_count = 1; set v = v - 1; end if;
        end;
      select 'update';
      update t25 set s2 = s2 + 1 where s1 = 1;
      select 'commit';
      commit;
      select 'end';
      end;
    set v = v + 1;
    end while;
  end//
delimiter ;
commit;
set @@autocommit=0;
start transaction;
call p25();

On T2, while T1 is running, do:

call p25();

After a while, you'll see:

+--------+
| update |
+--------+
| update |
+--------+
1 row in set (4.08 sec)

ERROR 1020 (HY000): Record has changed since last read in table 't25'

Thank you for the bug report. Verified on Suse 10.1 32-bit.

ERROR 1020 (HY000): Record has changed since last read in table 't25'
T1>

Please see bug: http://bugs.mysql.com/bug.php?id=25557

Update Conflicts necessarily occur more often in Repeatable Read Isolation within Falcon than they do in other engines due to the fact that the isolation is truely a Consistent Read. Users will be able to get around this when Select For Update is implemented.  However, it would seem that a single autocommit transaction with repeatable read isolation whould be able to be accomplished without this error.  In order to acheive this, retry logic must be added to the server code.
I opened WL#3715 to describe and track this fix.
I am not sure who will do the work, so I am assigning it to Calvin for now.

Disregard the previous comment about WL#3715.  This bug is specifically about the stored procedure not handling the error when it occurs.  Bug#25557 is about getting the UpdateConflict to occur less often in Falcon.

Miguel, Please retest this bug, since it seems to be a problem with the stored procedure code, and we are now embedded with 5.2.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I wasn't able to repeat anymore with current source server.

+-----+
| end |
+-----+
| end |
+-----+
1 row in set (1 min 10.55 sec)

Query OK, 0 rows affected (1 min 10.55 sec)

I re-tested with a different way of causing error 1020.

T1:
delimiter //
drop procedure if exists p//
drop table if exists t//
create procedure p ()
begin
  declare continue handler for sqlexception select 'error';
  update t set s1 = 3 where s1 = 1;
  end//
delimiter ;
create table t (s1 int, key(s1)) engine=falcon;
insert into t values (1);
commit;
set @@autocommit=0;
update t set s1 = 2 where s1 = 1;

T2:
set @@autocommit=0;
call p(); /* hangs */

T1:
commit;

At this point T2 displays 'error'.
That shows it caught the error.
So this bug is, somehow, fixed.