Here is the scenario I have a lead table and two agents. When an agent queries the leads table to get a lead they have access to then the agent gets the next available lead. The lead id is added to a 2nd table to show it is checked out. 2nd agent queries the table 5 seconds later and is able to pull the same record. Now the query checks to make sure that the record that is being pulled is not in the check out table.
I it possible that this is an issue with the Multi threading of the web server?
I am at a loss.
What is the best way to lock a record?
What MySQL engine should I be using?