Issue checking out a lead Mysql Help needed

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?
austinfxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
It is not an issue with the web server, it's called a race condition, a term of art in database work.  And it can be fixed by locking the tables during the time they are being updated or using a transaction (another term of art) to ensure that the tables maintain integrity.
Ray PaseurCommented:
Also, I'm not sure a "checkout table" may not necessarily be a good thing.  Guessing a bit here, but the lead checkout status would be binary, right?  Either it's available or it's checked out.  So a reasonable approach might be to have a column in the lead table that contained either zero (meaning not checked out) or the agent_id of the agent who has checked out the lead.  The logic for a checkout would go something like this...

LOCK TABLE leads
SELECT (next lead with agent_id == zero)
UPDATE leads (set agent-id)
UNLOCK TABLE leads

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry KrogerCTO - FounderCommented:
It seems to me the query would need to filter out already-checked-out leads.
Ray PaseurCommented:
@Jerry: Yes, that would be part of it, but it's more than that.  The agents are selecting the leads and "racing" to capture the lead after the selection process has finished.  This is kind of how it looks:
/** 
 * When only one agent is in play - no problem
 */
Agent #1 selects next available lead
   gets lead number 7           

Agent #1 updates lead table
   captures lead number 7


/** 
 * Risk when two or more agents are in play
 */
Agent #1 selects next available lead
   gets lead number 7           

Agent #2 selects next available lead
   gets lead number 7           

Agent #1 updates lead table
   captures lead number 7

Agent #2 updates lead table
   captures lead number 7

Open in new window

If the selection time is far enough apart (perhaps a second or more) then there is no problem because the select and capture will transpire quickly.  But each of select and capture are separate and atomic events.  There is no guarantee that they will happen in the right order.  Requests to the database are generally processed in the order they are received, and we need a way to guarantee that the select+capture become an atomic request, with each set of paired queries completing in the correct order.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.