[Koha-bugs] [Bug 10454] Duplicate card numbers may be generated

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10454

--- Comment #12 from Galen Charlton <gmcharlt at gmail.com> ---
(In reply to M. Tompsett from comment #11)
> > From a glance through code, I feel that this routine fixup_cardnumber is
> > still quite confusing and complicated. Should it really be done this way?
> 
> However, this patch (or something that at least serializes) is still
> necessary, because of the time gap between generation and insertion. Even if
> we shrink that gap, we don't eliminate the possibility of duplicates. This
> patch does.
> 
> The reason this patch is complicated is to deal with the off-chance that
> someone attempts to generate a number, but the server crashes, before the
> calculated next value is inserted back into the sequence table. That would
> leave the previous value not calculated. This is why there is the loop,
> before returning the calculated value for the my_UID record.

I too am suspicious of the complexity of this approach.  I fully agree that
improving concurrency is a worth goal, but I think the task of get a reliable
sequentially-assigned integer can be *much* simpler.

Here's a bit of code that provides an example.  It runs with the MySQL's
documented suggestion for emulating sequences [1].  If you create the table as
per the script's prep() function, then run as many multiple copies of the
script as you care to simultaneously, you won't get any repeats.

If this is wrapped up in a module, and possibly tweaked a bit to allow more
than one sequence to be defined, it could be nicely generalized.

I don't feel that we should be concerned about false skips ... if a staff user
gets a cardnumber value but doesn't use it... so what?

[1]
http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

___BEGIN___
#!/usr/bin/perl

use Modern::Perl;
use C4::Context;

my $dbh = C4::Context->dbh();

### only need to run prep() once
###prep();

$dbh->{AutoCommit} = 0;

my $max = $ARGV[0] || 10;

foreach my $i (0..$max) {
    my $val = fetch_next();
    print "got $val\n";
}

sub prep {
    $dbh->do('DROP TABLE IF EXISTS sequence');
    $dbh->do('CREATE TABLE sequence (id INT NOT NULL) ENGINE myisam');
    $dbh->do('INSERT INTO sequence VALUES (0)');
}

sub fetch_next {
    $dbh->do('UPDATE sequence SET id=LAST_INSERT_ID(id+1)');
    my $sth = $dbh->prepare('SELECT LAST_INSERT_ID()');
    $sth->execute();
    my @val = $sth->fetchrow_array();
    return $val[0];    
}
___END___

-- 
You are receiving this mail because:
You are watching all bug changes.