0

This is something I've never quite managed to master, and want to know if I am doing it right and have it sanity checked.

....

I have a database links, lets keep it simple, 2 columns

ID int(10) PK, Status enum('ready', 'in progress', 'complete');
1 'ready'
2 'ready'
3 'ready'

....

I then have an API that when queried returns the ID of a 'ready' item, this API could be called many time concurrently and I want to ensure that the ID is only given out once.

....

API MySQL queries..

LOCK TABLES `links` WRITE
SELECT ID FROM links WHERE status = 'ready' LIMIT 1 // gets a ready one
UPDATE links SET status = 'in progress' WHERE ID = 'X' // updates the one just got from line above to be in progress (so not selected by another API call
UNLOCK TABLES

So if two API calls hit it 0.01 seconds apart, the first one should lock the tables update it to in progress, after unlock the second one gets it's chance and does the same but selects a different ID to that the first one got.

Is this correct? or the best way to do such a thing? As it seems to cause long delays on MySQL traffic with some queries waiting 5 minutes or so and then MySQL hits max connections and that causes all sorts of other problems.

The table is InnoDB and is relatively small with approximately 165k rows, with an index on status, thank you in advance for any advice or pointers.

blackout2063
  • 13
  • 3
  • Are the ids unique i.e. created with each visitor or are set and assigned to the visitor? –  Aug 27 '13 at 12:07
  • No, it's just a database full of stuff with unique id's and each call to the API should give a different ID, no dupes. – blackout2063 Aug 27 '13 at 13:47

1 Answers1

0

You do not need to lock the whole table.
Just do:

UPDATE links SET status = 'in progress' 
WHERE status = 'Ready'
LIMIT 1;

or, if you want to retrieve an updated id, use:

start transaction;
select id into @xx from links
where status = 'Ready'
limit 1 for update;
update links set status = 'in progress'
where id = @xx;
commit;

These command will work in read commited isloation level and higher.
Since there is the index on the 'status' column, MySql will place a write lock only on one updated row for the duration of the transaction, leaving others rows unlocked.

krokodilko
  • 35,300
  • 7
  • 55
  • 79
  • Thank you, this makes sense and works if I run it in command line or phpmyadmin, however not in PHP as I would need to do two transactions therefore a commit would be sent after each. I need the "id" to be returned as a variable outside of the query as well. – blackout2063 Aug 27 '13 at 17:56
  • I don't know PHP well, but I've found an example how to deal with SQL transactions in PHP, take a look at this link: http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples – krokodilko Aug 27 '13 at 20:27
  • Nice, made sense of transactions in PHP from that link, this appears to now be working - shall test again in the morning and accept this answer. Thanks in advance. – blackout2063 Aug 27 '13 at 22:27