Select FOR UPDATE

[dwhalen]

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();
}