I have an application where I want to reserve a group of numbers at at time. For example, the last number I reserved was 12345 and I want to reserve 17 more numbers. I liked to atomically read the DB to get the last number assigned and the update the DB with current value + 17.
Do I need to do a lock table and then do a SELECT and UPDATE or is there a way to do this as a single atomic operation?
Not sure if it’s exactly what you’re looking for, but MySQL has a “last insert ID” function that will return the ID of the last insert query you did.
I don’t understand what you mean by “reserve a group of numbers.” Are you talking about primary keys here? With an auto_increment column? Or something else?
If all you want is for your auto_increment sequence to use an interval value other than one, you can change it with the auto_increment_increment configuration option.
Alternatively, you can poke the counter on a particular table by doing ALTER TABLE foo AUTO_INCREMENT=12345+17, for example.
It’s hard to explain without going into too much detail, but here goes. I am setting up a site that lets people print out coupons with a serial number. They can print a varying number of these coupons, each with a distinct serial number. I am not storing a record for each coupon, so the serial number is not a key.
There may be multiple people printing out coupons at any one time, so just doing a select of the last serial number issued followed by an update of the last serial number issued will not work because another user may get in between the select and update. I’d also like to do it somewhat efficiently and do one select/update for a group rather than one for each coupon.
I know the amount I am going to increment the serial number when I do the select, but it is not the same each time. I’m looking to see if there is an elegant way to do this. I’m learning MySQL as I go, so I’d like to do things the “right way” rather than learn bad habits.
You can’t do this as a single statement, but you can make it atomic by wrapping it in a transaction (assuming you are on MSQL 4 or later).
START TRANSACTION;
SELECT @x:= lastSerialNumber FROM MyTable;
UPDATE MyTable SET lastSerialNumber=@x+[increment] ;
COMMIT;
See here for more details.
Thanks, that look perfect.