# MySQL Question
**URL:** https://boards.straightdope.com/t/mysql-question/526400
**Category:** Factual Questions
**Created:** [January 25, 2010, 7:16pm UTC](https://boards.straightdope.com/t/mysql-question/526400 "2010-01-25T19:16:55Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@DanBlather](https://boards.straightdope.com/u/DanBlather)
#### Post date: [January 25, 2010, 7:16pm UTC](https://boards.straightdope.com/t/mysql-question/526400/1 "2010-01-25T19:16:55Z")
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?
---
### Author:  [@BrandonR](https://boards.straightdope.com/u/BrandonR)
#### Post date: [January 25, 2010, 7:37pm UTC](https://boards.straightdope.com/t/mysql-question/526400/2 "2010-01-25T19:37:38Z")
Not sure if it’s exactly what you’re looking for, but MySQL has a “[last insert ID](http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html)” function that will return the ID of the last insert query you did.
---
### Author:  [@friedo](https://boards.straightdope.com/u/friedo)
#### Post date: [January 25, 2010, 8:04pm UTC](https://boards.straightdope.com/t/mysql-question/526400/3 "2010-01-25T20:04:45Z")
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](http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_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.
---
### Author:  [@DanBlather](https://boards.straightdope.com/u/DanBlather)
#### Post date: [January 25, 2010, 8:34pm UTC](https://boards.straightdope.com/t/mysql-question/526400/4 "2010-01-25T20:34:30Z")
> [@friedo](#):
>
> 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](http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_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.
---
### Author:  [@TroutMan](https://boards.straightdope.com/u/TroutMan)
#### Post date: [January 25, 2010, 9:59pm UTC](https://boards.straightdope.com/t/mysql-question/526400/5 "2010-01-25T21:59:30Z")
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](http://dev.mysql.com/doc/refman/5.0/en/commit.html) for more details.
---
### Author:  [@DanBlather](https://boards.straightdope.com/u/DanBlather)
#### Post date: [January 25, 2010, 10:05pm UTC](https://boards.straightdope.com/t/mysql-question/526400/6 "2010-01-25T22:05:11Z")
> [@TroutMan](#):
>
> 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](http://dev.mysql.com/doc/refman/5.0/en/commit.html) for more details.
Thanks, that look perfect.