Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select FOR UPDATE

Status
Not open for further replies.

dwhalen

Programmer
Feb 22, 2002
105
0
0
CA
Hello,

I am trying to use the SELECT FOR UPDATE and I want to know if I am using it correctly. I want to make sure the below sql will lock the row so I can get it, update it and then commit and no one else can have access to it. I think my code is a little strange because SELECT FOR UPDATE cannot
be used with aggregation. I couldn't find an example of how it was used so I just tried to guess. It works, as far as I can tell, but I am the only one accessing the database right now. I want to know if this will work with many people accessing it at once. Here is my code:

#we want to turn off autocommit so that we can lock the row with a for update
#then update the row and then commit
$databh->{AutoCommit}=0;
#I set the raiseError just to be sure before we do the commit
$databh->{RaiseError}=1;

#FOR update cannot be used with aggreates so I do this first to the the client id
my $query1 = "SELECT min(client_id) FROM available_client_ids WHERE aci_status = 0 AND aci_host ='$host'";
my $client = $databh->selectcol_arrayref($query1);
my $query2 = "SELECT client_id FROM available_client_ids WHERE aci_status = 0 AND aci_host ='$host' AND client_id='$$client[0]' FOR UPDATE";
my $newClient = $databh->selectcol_arrayref($query2);
my $update = "UPDATE available_client_ids SET aci_status =1,aci_usedby_id ='$session_info[2]',aci_used_date=now()
WHERE client_id ='$$newClient[0]'";
$databh->do($update);

if($@){
$databh->rollback();

}
else{
$databh->commit();
}
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top