Combining a lock, update, select, and unlock into one query - need help

Databases
,
1

I have a database of phone numbers and I need a query that assigns a phone number to a given user (sid). The query needs to pull an available phone number (one without a user assigned to it (sid=‘’)) and also should pull the phone number with the oldest last_updated datetime stamp. It is possible that multiple records exist with the same timestamp. Because the script may be called from multiple users simultaneously, I believe I need to lock the record until the update is complete so we don’t overwrite one record with another. Preferably the lock would queue the request instead of erroring out. Here is my failed attempt at the query. I’m on MySQL 5.5.42. Any help would be greatly appreciated! Thanks!

UPDATE 
   phone_numbers 
SET 
   sid='12'
   , last_update=NOW()
   , issued_on=NOW() 
WHERE  
   id 
IN (
   SELECT 
      id 
   FROM 
      phone_numbers 
   WHERE 
      sid='' 
   ORDER BY 
      last_updated 
   DESC 
   LIMIT 1)

The error I’m getting is “This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’”.

I’d prefer to do it as a single statement so it executes as fast as possible and doesn’t require any external scripting.

2

if that’s true, the LIMIT 1 approach is best, so you might need to run two queries

3

That is a limitation of MySQL, in other databases you would be able to run a query like this.

But you could do something simpler:

UPDATE 
   phone_numbers 
SET 
     sid='12'
   , last_update=NOW()
   , issued_on=NOW() 
WHERE sid=''
ORDER BY last_update, id
LIMIT 1

If you want the oldest last_updated then you wouldn’t use DESC for ordering, that would mean the newest. If your id is an auto-increment number then the additional order by id should get you better sequential ordering when multiple records exist with the same timestamp.

1 Like
4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.