help with perl dbi and update without locks

[joe]

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.

 

[A. Sinan Unur]

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 qwflock);

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.

}

 

[joe]

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.

 

[Thomas Kratz]

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

 

[ctcgag]

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

 

[joe]

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