Get next id for a autoincrement field and operate on it [Solved]

[eluser]ajitabhpandey[/eluser]
I am writing an application in CodeIgniter. The application will be accessed by many users over the web. The table structure is as below:

id int(11) autoincrement
field_1 varchar not null
id_base64_hash varchar not null
.....
.....

Here is the logic of what I plan to do and the issues I think can occur.

Code Logic -

(1.) User submits a form
(2.) After validation the code gets the next id number of the auto-increment field (select max(id)+1 as next_id from mytable
(3.) Calculates the base64 hash of the next_id value
(4.) Inserts a new record in the same table with the submitted form values and the base64 hash of the next_id value.

NOTE - It is mandatory that the next_id calculated at step 2 does not change till the insertion at step 4 takes place.

Issues -

(1.) I am not comfortable with the approach of getting the next id from the mysql table as it is scanning the whole table so obviously speed will depend on the number of records in the table.
(2.) There are many users who are accessing and submitting the form at the same time over the web, so while step 3 from above is being done, there is a possibility that other users also get the same next_id value and process it resulting in same base64 hash value.

I wanted to avoid locking at the database level as the same table will be read by other sections of the application also and putting an exclusive lock can hold all other operations (although for fraction of microseconds, but assuming a high transaction website this can cause significant delay).

I am happy to have a feasible solution at mysql level such as appropriately written trigger or procedure etc.

Appreciate any inputs/thoughts on this.

Thanks.

[eluser]WanWizard[/eluser]
Very dangerous, unless you lock the table for write. Some database engines allow concurrent inserts (p.e. InnoDB), so nothing guarantees you the max(id)+1 will be your next id. There are also situations where the auto increment value is not 1.

I suggest to turn it around, insert the record first (without the hash), get the new insert_id, calculate the hash, then update the record with the hash using the retrieved insert_id as key.
Safe, and no locks needed.

[eluser]ajitabhpandey[/eluser]
Hmm, I thought about that as well....

I see one issue there, the table I have designed specifies the hash value column as "not null". I will have to remove it, if there is no work around....

I will proceed for now with the insert first logic and let us see if somebody else has some other suggestions....


Thanks WanWizard.

[eluser]mddd[/eluser]
If the hash cannot be null, just enter some other value in there. Simple.
I agree with WanWizard: The way you were planning is an accident waiting to happen.

[eluser]ajitabhpandey[/eluser]
Yeah, thanks. I have removed the "not null" condition from the hash column for now and this logic is working fine.

Thanks all for participating and suggesting ideas.

[eluser]Unknown[/eluser]
$next = $this->db->query("SHOW TABLE STATUS LIKE 'table_name'");
$next = $next->row(0);
$next->Auto_increment;
echo $next->Auto_increment;