List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:July 29 1999 1:59am
Subject:Re: On GET_LOCK ()
View as plain text  
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";
    }
Thread
Restrict Accesstoxalot27 Jul
  • Restrict Accesssinisa27 Jul
    • Re: Restrict AccessThimble Smith27 Jul
      • Re: Restrict AccessPaul DuBois27 Jul
        • Re: Restrict AccessMartin Ramsch27 Jul
          • Re: Restrict AccessPaul DuBois28 Jul
            • Re: Restrict AccessThimble Smith28 Jul
        • Re: Restrict Accesssinisa28 Jul
    • Re: Restrict Accesstoxalot28 Jul
      • On GET_LOCK ()sinisa28 Jul
        • Re: On GET_LOCK ()Benjamin Pflugmann28 Jul
          • Re: On GET_LOCK ()sinisa28 Jul
            • Re: On GET_LOCK ()Paul DuBois28 Jul
          • Re: On GET_LOCK ()Jim Faucette28 Jul
            • Re: On GET_LOCK ()Paul DuBois28 Jul
              • Re: On GET_LOCK ()Thimble Smith28 Jul
          • Re: On GET_LOCK ()Gerald Clark28 Jul
        • Re: On GET_LOCK ()Paul DuBois28 Jul
          • Re: On GET_LOCK ()sinisa28 Jul
            • Re: On GET_LOCK ()Paul DuBois28 Jul
  • Re: On GET_LOCK ()Scott Hess28 Jul
    • getting rid of duplicatesJoel Bremson28 Jul
    • Re: getting rid of duplicatesChristian Mack28 Jul
    • Re: On GET_LOCK ()sinisa29 Jul
      • Re: On GET_LOCK ()Paul DuBois29 Jul
    • Re: On GET_LOCK ()Scott Hess29 Jul
Re: On GET_LOCK ()toxalot28 Jul
  • Re: On GET_LOCK ()sinisa28 Jul
    • Re: On GET_LOCK ()Benjamin Pflugmann29 Jul
  • Re: On GET_LOCK ()Sasha Pachev29 Jul
    • Re: On GET_LOCK ()Benjamin Pflugmann31 Jul
Re: On GET_LOCK ()Thimble Smith29 Jul
  • Re: On GET_LOCK ()Fraser MacKenzie29 Jul
    • Re: On GET_LOCK ()Thimble Smith29 Jul
      • Re: On GET_LOCK ()Fraser MacKenzie29 Jul
Re: On GET_LOCK()R. Mentink31 Jul