[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.