Reserving mySQL auto-incremented IDs?

Question

We want to obtain an auto-increment ID from mySQL without actually storing it until the other non-mysql related processes are successfully completed, so that the entry is not stored if an exception or application crash happens. We need to use the ID as a key for the other processes. In essence we want to “reserve” the auto-increment and insert the rows into mySQL as the last step. We don’t want to insert any row until we know the entire process has completed successfully.

Is it possible to do this sort of auto-increment reservation in mySQL?

Note: I know about the SQL transactions. But our process contains non-SQL stuff that need to happen outside of the DB. These process may take few mins to several hours. But we don't want any other process using the same auto-increment ID. That is why we want a "reserve" an auto-increment ID without really inserting any data into the DB. –

Can you give more details please? Something along the lines: (1) A process starts. (2) Something happnes to the DB. (3) Something else happnes....etc. — Greeso, Aug 23 '13 at 20:53
You could maybe not use autoincrement. If you stored the current id counter in a small table elsewhere, then when your outside process began it could grab the current id and increment it. In the event of a crash a id might be skipped, but not duplicated. — Alden W., Aug 23 '13 at 20:59
@AldenW. please see Bill Karwin's response. Do you think that is a good approach? — Saqib Ali, Aug 23 '13 at 21:17

score 15 · Accepted Answer · answered Aug 23 '13 at 20:49

15

The only way to generate an auto-increment value is to attempt the insert. But you can roll back that transaction, and still read the id generated. In MySQL 5.1 and later, the default behavior is that auto-increment values aren't "returned" to the stack when you roll back.

START TRANSACTION;
INSERT INTO mytable () VALUES ();
ROLLBACK;
SELECT LAST_INSERT_ID() INTO @my_ai_value;

Now you can be sure that no other transaction will try to use that value, so you can use it in your external processes, and then finally insert a value manually that uses that id value (when you insert a specific id value, MySQL does not generate a new value).

answered Aug 23 '13 at 20:49

Bill Karwin

538,548
86
673
828

Hi Bill. This indeed is a useful response. Sounds like just what we need. Have you tried this approach before? Anything we should look out for? – Saqib Ali Aug 23 '13 at 21:17
1

All the other columns of the table either need to allow NULL, or have default values. Or else you need to specify dummy values for those columns. – Bill Karwin Aug 23 '13 at 21:20
Bill, what if multiple processes are executing this at the same time -- since the select happens after the rollback, is there a possibility that it would retrieve the same number as another process is retrieving? – Saqib Ali Aug 23 '13 at 21:43
2

No, LAST_INSERT_ID() only reports the last generated ID in the *current* session. It will never report ID's generated by other sessions. That function would be pretty useless if it were susceptible to race conditions. – Bill Karwin Aug 23 '13 at 21:45
even if the connections are coming from a Jboss connection pool? – Saqib Ali Aug 23 '13 at 21:48
1

Use com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource and it resets the server-side state on each getConnection(). – Bill Karwin Aug 23 '13 at 22:18
2

It's worth noting that auto-increment values are returned to the stack if MySQL is restarted. – Matthew G May 05 '15 at 02:59
@MatthewG, that's correct, and it makes this solution unreliable, because you *can't* assume the id won't be generated again. There are alternatives that involve storing one row in the table, and updating it to the largest value stored so far. See the manual for [LAST_INSERT_ID()](https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id). – Bill Karwin May 05 '15 at 16:13
@MatthewG As of MySQL 8.0, InnoDB persists the latest auto-increment in the redo log, so if you restart, it remembers not to re-use those values. https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization – Bill Karwin Jan 31 '23 at 01:46

score 1 · Answer 2 · answered Aug 23 '13 at 20:38

1

Have you considred using mysql tranactions?

The essense of it, you start a transaction, if all sql statements are correct and can be complteted, then you commit your transaction. If not, then you rollback as if nothing happened.

More details can be read in this link: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html

answered Aug 23 '13 at 20:38

Greeso

7,544
9
51
77

This is probably the best solution (it's difficult to be sure given the short problem description). An important detail the question-poster should be aware of is that transactions only work on InnoDB tables. – Tomas Creemers Aug 23 '13 at 20:48
Thanks. I know about the SQL transactions. But our process contains non-SQL stuff that need to happen outside of the DB. These process may take few mins to several hours. But we don't want any other process using the same auto-increment ID. That is why we want a "reserve" an auto-increment ID without really inserting any data into the DB. – Saqib Ali Aug 23 '13 at 20:50
@SaqibAli Could you please give more details about your problem? It looks like there are many things that need to be considred but yet are not in the description. – Greeso Aug 23 '13 at 20:52
Greeso I added some details to the original question. Hopefully that help. Looks like @Bill Karwin response will do the trick. What do you think of that approach? – Saqib Ali Aug 23 '13 at 21:15

score 1 · Answer 3 · answered Aug 23 '13 at 20:48

1

you can use temporary table along with transaction

if transaction complete temp table will be gone and move data to real table

http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm

answered Aug 23 '13 at 20:48

zod

12,092
24
70
106

Reserving mySQL auto-incremented IDs?

3 Answers3

Linked