UIDs for data objects in MySQL

Question

Hi there,

I am using C++ and MySQL.

I have data objects I want to persist to the database. They need to have a unique ID for identification purposes. The question is, how to get this unique ID?

Here is what I came up with:

1) Use the auto_increment feature of MySQL. But how to get the ID then? I am aware that MySQL offers this "SELECT LAST_INSERT_ID()" feature, but that would be a race condition, as two objects could be inserted quite fast after each other. Also, there is nothing else that makes the objects discernable. Two objects could be created pretty much at the same time with exactly the same data.

2) Generate the UID on the C++ side. No dice, either. There are multiple programs that will write to and read from the database, who do not know of each other.

3) Insert with MAX(uid)+1 as the uid value. But then, I basically have the same problem as in 1), because we still have the race condition.

Now I am stumped. I am assuming that this problem must be something other people ran into as well, but so far, I did not find any answers.

Any ideas?

Answer 1

The query:

SELECT LAST_INSERT_ID()

will return the last ID inserted on your specific connection, not globally. So there is no race condition, unless your own code is multi-threaded, in which case you would want to surround the INSERT and the SELECT with an MT lock of some sort.