Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > help with perl dbi and update without locks

Reply
Thread Tools

help with perl dbi and update without locks

 
 
joe
Guest
Posts: n/a
 
      06-23-2004
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.
 
Reply With Quote
 
 
 
 
A. Sinan Unur
Guest
Posts: n/a
 
      06-23-2004
joe <(E-Mail Removed)> wrote in
news:Xns9511C2C8B6106josephthecianet@207.69.154.20 2:

> 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
http://www.velocityreviews.com/forums/(E-Mail Removed) (reverse each component for email address)
 
Reply With Quote
 
 
 
 
joe
Guest
Posts: n/a
 
      06-24-2004
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.

 
Reply With Quote
 
Thomas Kratz
Guest
Posts: n/a
 
      06-24-2004
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^..
 
Reply With Quote
 
ctcgag@hotmail.com
Guest
Posts: n/a
 
      06-24-2004
joe <(E-Mail Removed)> 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
 
Reply With Quote
 
joe
Guest
Posts: n/a
 
      06-24-2004
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update statement with Perl DBI for MySQL 5.0 sam Perl Misc 2 01-18-2005 06:27 PM
DBI Mysql storing DBI:binary, trouble with character 26 Jerome Hauss Ruby 0 10-13-2004 03:04 PM
DBI and DBI::Oracle packages configuration ulloa Perl 1 07-22-2004 05:52 PM
DBI problem : How can I load quickly one huge table with DBI ??. Tim Haynes Perl Misc 3 09-13-2003 03:43 AM
Mason, DBI, and DBI::Pg Asby Perl Misc 0 07-24-2003 09:04 PM



Advertisments