query for smallest record number not used

[showman13]

Not sure the best way to ask this question, but here goes...

I have a Database table that has a column called pos_id that is an auto increment field, and I have used it as a tracking device for the next position number in a company forced matrix system...

Now I need to convert the table to be used in a different manner that will not be tracking position IDs on an incremental basis but rather on a semi random basis, where there may be holes in the numbering system.

Position IDs start at 10,000,000 and currently go up to 10,039,765.

pos_id will no longer be an auto increment field, but will be a generated number based on various criteria at times, and at other times I need to find the Smallest unused pos_id available, if that option is selected by the member...

Baiscally Here is my question...

How would I format the query to find the smallest value of pos_id that doesn't exist yet as a record number??

I hope this makes sense... Here are the fields in the table, but I don't really need to gather information from the table, just need to determine the smallest available pos_id.

Column      Type    

pos_id      int(9)  
mem_id      int(8)      
prev_pos_id int(9)  
create_date datetime 
last_update datetime 
status      char(1)

If more clarification is required, please let me know what you need...

Thank you in advance for your feedback.
Douglas

[showman13]

OK, doing more searching on the net and testing some of the options I came up with, here is the best resolution I have come up with SO FAR... If you have better options, please let me know...

BUT, even with this solution, it raises 1 remaining question which I will pose after I show you what I have come up with... it is a Sub Select so it only works in MySql 4.1 and above.

SELECT A.pos_id+ 1
FROM position_1 AS A
WHERE NOT EXISTS (
    SELECT B.pos_id FROM position_1 AS B 
    WHERE A.pos_id + 1 = B.pos_id)
GROUP BY A.pos_id
LIMIT 1

The REMAINING QUESTION:

How can I ensure that multiple members making the same choice at the same time don't end up receiving the exact same result, and ultimately try to acquire that position at the same time?

Thanks again for any feedback anyone may have.

Douglas

[pritaeas]

How can I ensure that multiple members making the same choice at the same time don't end up receiving the exact same result, and ultimately try to acquire that position at the same time?

If your field is the PK, inserting the same id will fail. Then you can retry getting an id and inserting the record until it succeeds.

Not sure if it is possible in MySQL, but you can insert through a stored procedure that locks the table while determining the correct id.

[showman13]

If your field is the PK, inserting the same id will fail.

Not sure what PK means....
But you are right, if the insert is for a record that already exists, I should be able to program around receiving the error to cause it to go find a new id...

Probably not the most efficient use of time and resources, but unless I can come up with a better resolution, I'm thinking that would probably Work.

Thank you for your response.

If anyone has other thoughts, I'd love to hear them before I embark on this path.

Douglas

[pritaeas]

PK means Primary Key. Making the field unique will also work.

[showman13]

Thank you again for your response... I'm posting here what I believe will be the resolution, but I haven't tested it yet...

I think locking the tables while the position number is determined / assigned / inserted and then unlocking the tables will eliminate any issues at all...

Please let me know what you think or if you see any issues with this plan/process...

Thanks
Douglas

// LOCK TABLES
$tbl_name='position_1';// varies between 16 different tables
$sql_lock="LOCK TABLES ".$tbl_name." AS A WRITE, ".$tbl_name." AS B WRITE";
mysql_query($sql_lock) or die(mysql_error()."<br>".$sql_lock);
//###################################

// find the lowest unused position number in the table
$sql="
    SELECT A.pos_id +1
    FROM ".$tbl_name." AS A
    WHERE NOT EXISTS (
        SELECT B.pos_id
        FROM ".$tbl_name." AS B
        WHERE A.pos_id +1 = B.pos_id
    )
    GROUP BY A.pos_id
    LIMIT 1
";
$result=mysql_query($sql);
    // Assign position to member
    // do whatever processing is required in the script for this position

//###################################
// THEN UNLOCK TABLES
$sql_unlock="UNLOCK TABLES";
mysql_query($sql_unlock) or die(mysql_error()."<br>".$sql_unlock);

[showman13]

Well, I'm not sure, but I think this is solved, so I will mark it as such...

Won't know for a few days, but if I have any great revolations, I will come back and post them for everyone to share.

thanks
Douglas