Re: On GET_LOCK ()

[Thimble Smith]

Jennifer, the whole GET_LOCK() conversation drifted pretty far away
from your original question.

If your application is a web one, probably GET_LOCK() isn't the best
method for you to choose.  The reason is that with web apps (CGI),
the program (and the connection to the database) starts and stops
with each page view.  You want the user to be "using" the database
not just when the page is loading in their browser, but while they
are reading the page(s) as well.

I agree with the other user who said that, if you are writing a web
application, some kind of time field should be used.  Probably you
will have a table of users:

CREATE TABLE Users (
    client_id   INT UNSIGNED NOT NULL,  /* points to the Clients table */
    name        CHAR(20) NOT NULL,
    /* other fields related to the user - password, access rights, etc. */
    in_use      TIMESTAMP,
    in_use_by   CHAR(15) NOT NULL,      /* IP address of current user */
    PRIMARY KEY (client_id, name)
)

Now, when your application starts up, you would do something like this
to prevent concurrent access to the database (Perl code here - if you
need some translation, let me know):


    # Here's the top of the script...

    my $dbh = DBI->connect(...);        # connect to db

    # You get the client id and username from a CGI parameter or
    # something...
    my $client_id = param('client_id');
    my $user_name = param('user_name');

    # Use the IP address to identify who's using this.  You might
    # prefer to use cookies or something else to identify a real
    # live person out there.
    my $ip_address = $ENV{'REMOTE_ADDR'} || '0.0.0.0';

    if (need_to_logout()) {
        # you could check the return value and print a "success" or
        # "you didn't need to logout" message, but there's no need
        # to tell the user that, probably.
        release_db_access($dbh, $client_id, $user_name, $ip_address);

        return logged_out_message();
    }

    acquire_db_access($dbh, $client_id, $user_name, $ip_address)
        or return db_is_in_use();

    # now use $dbh to access any information you want

    exit 0;


    sub acquire_db_access {
        my ($dbh, $client_id, $user_name, $ip_address) = @_;

        my $timeout_interval = '0:3:30';        # hours:minutes:seconds

        my $rc = $dbh->do(<<"EOS");
UPDATE  Users
SET     in_use = NULL,
        in_use_by = '$ip_address'
WHERE   client_id = $client_id
AND     user_name = ?
AND     (in_use_by = '$ip_address' OR
         in_use < DATE_SUB(NOW(), INTERVAL '$timeout_interval' HOUR_SEC)
EOS
        $rc or die "update failed: ", $dbh->errstr;

        if ($rc == 0) {
            # There either the username / client id didn't match, or
            # (more likely), someone else has accessed the database

            return 0;
        }
        elsif ($rc == 1) {
            # we are granted access to the database now

            return 1;
        }

        die "impossible: $rc";
    }

    sub release_db_access {
        my ($dbh, $client_id, $user_name, $ip_address) = @_;

        my $rc = $dbh->do(<<"EOS", undef, $user_name);
UPDATE  Users
SET     in_use = 00000000000000,
        in_use_by = ''
WHERE   client_id = $client_id
AND     user_name = ?
AND     in_use_by = '$ip_address'
EOS

        $rc or die "update failed: ", $dbh->errstr;

        if ($rc == 0) {
            # we didn't have access, so we couldn't release it

            return 0;
        }
        elsif ($rc == 1) {
            # we released access to the database

            return 1;
        }

        die "impossible: $rc";
    }