1

I have a for where you capture new user information. I generate a member number by getting an incremented last number . What happen now, is 2 admin users create 2 new users from different sites, Both get the same last id and when one trying to save it , the getting errors because the first admin users now already saved his user with the same last number. here is basically what I do

id_count is an AI field

The Admin select add user from the front end.

the following query run and bring back the last number 

SELECT id_count FROM members order by id_count DESC Limit 1
member_id = $row['id_count'] +1

now if i have 2 admin users doing this at exactly the same time. both get the same new member number. Admin1 safe his user and it is fine. Now when Admin2 safe the information the new number already exist and information don't safe. member number is unique field

The Wizard
  • 63
  • 9

1 Answers1

0

This is called a race condition.

The application working on the DB should treat this new user as not having an ID yet (maybe working with negative user IDs internally or whatever). Only when creating the new user entry, the final, permanent user ID is assigned, and this is done via an auto increment database field, which is an atomic operation.

glglgl
  • 89,107
  • 13
  • 149
  • 217
  • Ok Ive decided to only generate the ID once the user click save button. The odds for 2 users clicking save at the same tame is bigger. – The Wizard Jun 30 '18 at 10:56
  • @TheWizard Even this case can (and should) be handled. If you do it the way via the DB, it generates the ID while inserting the record, and the generated ID can then be queried. See my answers under https://stackoverflow.com/a/7501531/296974 and https://stackoverflow.com/a/7738037/296974. – glglgl Jul 03 '18 at 09:41