time lag between data retrieval and updating record.



Howdy, I haven't looked over previous posts, so sorry if someone has asked something similar:

I use php and I have a script to pull in a record and then lock it after it is pulled in. The problem I have is very rarely someone will access the page at the same time and since the script first grabs a record, and then in the next step or two it updates/locks the record, there is a lag that the script can actually give two or more people the same record. I am using a character field to flag if the student is 'locked' or not.

Is there a way to grab a record and update it in the same step, or a function similar to mysql_insert_id to grab the record that was last updated. IF there was a function like this, then I could just lock a record, and then grab the data in the next step.

-- Also, I just thought that I might be able to have each instance of the script have a unique id set to some variable. Then see if it is possible to update one row with a field containing this key, and then in the next step it would grab the data with the key. Such as:
$key = something;
update table set field=$key where locked=no limit 1;
select * from table where field=$key;

I'm not sure if I can put a limit 1 in my update, but I will look into it. I just throught I would throw this up on the board to see if anyone has any good suggestions.

It looks like the update clause has the LIMIT key word, so if that works how I think it will, that should allow me to update one record and then use the key to retrieve the data after I update it. ...problem solved.