Read Record, Distribute Record Once

[n3mesis125]

Hey Folks,

 

I have built a queueing type system for where I work and how my system works is I upload data into a Mysql Database using .csv files. I then try to distribute those records to employees to read the data, lock the record and begin looking at the information to complete there task. Now one issue I am concerned about is how to only send a unique record to each employee at once without it duplicating similar records to people.

 

Is MySQL smart enough to not let two ppl read the same record at once? Is there some way that I can make it so that MySQL will let someone read a record, lock it and nobody else will see that record. I just don't want the same record to be sent to two people.

 

At the moment I do a pull with a query similar to the below, I immediately after that update the record with the employees ID # and update a column called 'lock' to 1 from 0. The only thing I'm worried about is the system fast enough to do all this or is there a better way to do it then what I am doing.

 

$sql = "SELECT *, CAST(cycle_priority AS SIGNED INTEGER) AS BCycle
	FROM bot_adhoc_upload
	WHERE pid='".$pid."'
	AND CAST(cycle_priority AS SIGNED INTEGER) >= '".date('j')."'
	AND `lock`='0'
	AND `hold`='0'
	AND `complete`='0'
	AND `syslock`='0'
	ORDER BY BCycle ASC LIMIT 1";

$res = mysql_query($sql) or die(mysql_error());

$adhoc = mysql_num_rows($res) ? mysql_fetch_array($res) : 0;


if($adhoc){
$sql2 = "UPDATE `bot_adhoc_upload`
		 SET `lock`='1', `lockedby`='".$user."'
		 WHERE `id`='".$adhoc['id']."'";
$res2 = mysql_query($sql2) or die(mysql_error());
} else {
        header("Location: bot_adhoc_empty.php?q=".$func->name);
}

[Pikachu2000]

2. Users will not mark their post as being "URGENT" by either making the post ALL CAPS or adding any hint that they need it done Quick or ASAP. All posts are treated equal, your post is no more important than any other user's post on this forum. If it is "URGENT" pay someone to do it.

[n3mesis125]

I apologize for the title and I am using version 5.1.41 if that helps any.

[fenway]

There's no "locking read" -- that's pessimistic locking that you'll have to build into your application.