help with perl dbi and update without locks

Discussion in 'Perl Misc' started by joe, Jun 24, 2004.

  1. joe

    joe Guest

    i have a table with the fields pk and user; i am trying to avoid conflics
    when 2 or more users do a select max(pk) and then insert pkmaxvalue, user.
    i created a function that assigns max(pk) to a variable and then uses this
    variable to create a record with the variable and another field for the
    user that created this record. I am trying to avoid conflicts by retriving
    the user from the record after the update. if the user does not match i
    created a loop to create a record until the user in the record is equal to
    the user that created the record. would this work?
    i am trying to make the script work with mysql, unixodbc and iodbc and i
    am trying to avoid locks because i dont know if i can do
    $dbh->do("lock table my table in exclusive mode");
    for all dbi and dbiodbc implementations.
    joe, Jun 24, 2004
    #1
    1. Advertising

  2. joe <> wrote in
    news:Xns9511C2C8B6106josephthecianet@207.69.154.202:

    > i have a table with the fields pk and user; i am trying to avoid
    > conflics when 2 or more users do a select max(pk) and then insert
    > pkmaxvalue, user. i created a function that assigns max(pk) to a
    > variable and then uses this variable to create a record with the
    > variable and another field for the user that created this record. I
    > am trying to avoid conflicts by retriving the user from the record
    > after the update. if the user does not match i created a loop to
    > create a record until the user in the record is equal to the user that
    > created the record. would this work?


    I am not sure exactly what you are doing based on the description above.
    It is preferable to post a short example in Perl of what you are doing so
    as to remove any ambiguity.

    > i am trying to make the script work with mysql, unixodbc and iodbc
    > and i am trying to avoid locks because i dont know if i can do
    > $dbh->do("lock table my table in exclusive mode");
    > for all dbi and dbiodbc implementations.


    I might be missing something but just using a lock file is probably going
    to be much simpler:

    use Fcntl qw:)flock);

    sub update {

    # ...

    open my $lock, '>', 'lockfile'
    or die "Cannot open lockfile: $!";
    flock $lock, LOCK_EX
    or die "Cannot obtain exclusive lock on lockfile: $!";

    # Do the updating etc.

    }

    --
    A. Sinan Unur
    (reverse each component for email address)
    A. Sinan Unur, Jun 24, 2004
    #2
    1. Advertising

  3. joe

    joe Guest

    well i am working with dbi, i put all the queries in a pm file.
    the frist function inserts the record with the following statements
    $QRY1="select max(pk)+1 from $vparam1";
    $QRY2="insert $vparam1( pk, mwuser ) values( ? , ? )";
    the second function verifies that the user that inserted the record was the
    right user.
    $QRY1="select mwuser from mmsqlmsgtable where pk = ?";
    if the user is different because of concurrent transaction
    i repeat the function 1 until funtion 2 returns the right user.
    i am trying to make this work on dbdmysql dbdoracle and dbdodbc.
    joe, Jun 24, 2004
    #3
  4. joe

    Thomas Kratz Guest

    joe wrote:

    > i have a table with the fields pk and user; i am trying to avoid conflics
    > when 2 or more users do a select max(pk) and then insert pkmaxvalue, user.
    > i created a function that assigns max(pk) to a variable and then uses this
    > variable to create a record with the variable and another field for the
    > user that created this record. I am trying to avoid conflicts by retriving
    > the user from the record after the update. if the user does not match i
    > created a loop to create a record until the user in the record is equal to
    > the user that created the record. would this work?
    > i am trying to make the script work with mysql, unixodbc and iodbc and i
    > am trying to avoid locks because i dont know if i can do
    > $dbh->do("lock table my table in exclusive mode");
    > for all dbi and dbiodbc implementations.


    If I understand correctly you want to auto increment a numeric primary key
    while inserting new values into a table?
    This is heavily dependent on the database you are using. I would suggest
    asking in a MySQL newsgroup.

    Most databases can do this internally with something like (This is MSSQL):

    CREATE TABLE [dbo].[mytable] (
    [pk] [int] IDENTITY (1, 1) NOT NULL ,
    ....

    Which means begin with 1 and increase by 1 for every insert.

    Another common method is to use an insert trigger that calculates the next
    primary key value. There maybe others.

    Doing the increment on the user's side is the worst of all methods.

    Thomas

    --
    open STDIN,"<&DATA";$=+=14;$%=50;while($_=(seek( #J~.> a>n~>>e~.......>r.
    STDIN,$:*$=+$,+$%,0),getc)){/\./&&last;/\w| /&&( #.u.t.^..oP..r.>h>a~.e..
    print,$_=$~);/~/&&++$:;/\^/&&--$:;/>/&&++$,;/</ #.>s^~h<t< ..~. ...c.^..
    &&--$,;$:%=4;$,%=23;$~=$_;++$i==1?++$,:_;}__END__#....>>e>r^..>l^...>k^..
    Thomas Kratz, Jun 24, 2004
    #4
  5. joe

    Guest

    joe <> wrote:
    > i have a table with the fields pk and user; i am trying to avoid conflics
    > when 2 or more users do a select max(pk) and then insert pkmaxvalue,
    > user.


    This is generally the worst way to do it. Each database (even MySQL) has
    some built-in way to accomplish this. Create different modules for each
    database.

    > i created a function that assigns max(pk) to a variable and then
    > uses this variable to create a record with the variable and another field
    > for the user that created this record. I am trying to avoid conflicts by
    > retriving the user from the record after the update.


    This doesn't make much sense. If your database checks pk for uniqueness,
    then you will get an error if you try to insert a value that has just been
    inserted by someone else. If your database doesn't check pk for
    uniqueness, then there may be more than one user retrieved by the same PK.
    So in one case you don't need to do a select to check, and in the other
    case your check is inadequate.

    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
    , Jun 24, 2004
    #5
  6. joe

    joe Guest

    thanks for the replies.
    i am glad i got the idea thourgh. i am considering a couple alternatives, i
    want to use standar sql because this is the only small glitch that would
    not allow my run the script in mysql mssql and oracle.
    1. set a table for each user
    2. combine the sql insert and the select max pk statements into one.
    3. leave it the way it is a dig a couple of errors here and there
    4. use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    joe, Jun 24, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    516
    Juha Laiho
    Jul 22, 2004
  2. Jerome Hauss
    Replies:
    0
    Views:
    161
    Jerome Hauss
    Oct 13, 2004
  3. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    167
  4. Tim Haynes
    Replies:
    3
    Views:
    132
    Ron Reidy
    Sep 13, 2003
  5. sam
    Replies:
    2
    Views:
    256
    J. Gleixner
    Jan 18, 2005
Loading...

Share This Page