Classic database insert problem

Question

I have a SQL database which i use to store some info and every record has a unique id generated by the database. MY program is written in flash and runs over the web, the program runs fine and it inserts records into the database and pulls the idnumber of the last record and displays it for user, my question is though how do i handle multiple concurrent entries because the program will be used by multiple users and chances are there will be concurrent inserts into the database so how would i be able to identify the correct unique id numbers for users

here is the code for inserting into the database

$query = "INSERT into $tablename (grade,school,country) VALUES ('$datarray[0]','$datarray[1]','$datarray[2]')";
$result = mysql_query($query) 
  or die("no rows selected");

and after that i load another php file on the second in my flash file to pull the id for that record and display it here is the code

$query = "SELECT max(id) from $tablename";
$result = mysql_query($query) 
  or die("no rows selected");
$row = mysql_fetch_array($result); // extracts one row
echo  "id=$row[0]";

what do i need to do to get the correct id for that record?

Answer 1

If your database code returns the Id of the last inserted row, this will definitely cause issues.

If you're using SQL server, you can use:

SELECT SCOPE_IDENTITY()

to select back the correct Id. More information at the MSDN article.

It would be helpful if you gave more information on how you're doing your database inserts and what technology you're using

Answer 2

You can do your insert and following select operations into a same transaction. You can also have access to facilities depending on your database.

Answer 3

You could associate IDs with Users so that the primary key in your table is compound (unique id generated, user-id).

Answer 4

You need an atomic way to get a unique id for your insert. Classic ways are to use a database sequence where you query the database for the next unique number, then use that number as the primary key when you insert, or (if you're using MySQL that generates the unique number on insert) combine the insert and select into one atomic statement.

Answer 5

This depends to some degree on the database you are using.

For example in SQL Server you have a couple of options. You could use SCOPE_IDENTITY() to retrieve the last identity value created in the current scope.

EX: SELECT @NEWID = SCOPE_IDENTITY()

This article gives some examples for SQL Server:

http://databases.aspfaq.com/general/how-do-i-get-the-identity/autonumber-value-for-the-row-i-inserted.html

Answer 6

use transactions - and I personaly would not write it in flash (but that's my personal opionion). unfortuenately I have not done such a thing in flash - so I do not know if there is transaction support from the language - what you could do is use transactions on the database. What I mean is for every connection you make - you open a transaction and within this transaction there is only one user - so you have the correct id to display - but if you are using auto-incremented numbers as ids you will have still the same problem - since the transactions can run concurrent. i hope this helps a bit ;)

Answer 7

Most of databases support UUID/GUID key. Generate it with your backend (or even ActionScript) and use it in database. It's not natural key, but it's perfect for uniqueness even within server farms.

Answer 8

PHP has mysql_insert_id() to return

The ID generated for an AUTO_INCREMENT column by the previous INSERT query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.

Answer 9

Be sure to put the INSERT and SELECT statements into a single query that inserts the data and returns the ID.

It's important NOT to use two separate queries to retrieve the ID because once the first insert is completed another user can (and eventually will) insert another record before you have a chance to get the ID of the first.

If you use separate transactions you will eventually have the following scenario:

User1 : Inserts a record (id = 123)
User2 : Inserts a record (id = 124)
User1 : Requests max id (and gets back 124) <---- WRONG ID
User2 : Requests max id (and gets back 124)

As noted in the posts above the exact syntax for retrieving the inserted ID is DB specific.

Answer 10

First off you are using PHP and MySQL. ID columns should generally be defined as:

id int( 4 ) UNSIGNED NOT NULL AUTO_INCREMENT,
-- Constraints
primary key (id)

Using id as the name is self documenting. A 4 byte integer may be overkill especially as it is unsigned, but space is cheap and its very unlikely you will ever run out of ids.

I also suggest using the InnoDB engine where ever possible. Foreign keys and data integrity are wonderful things.

Your script should be doing a mysql_connect() and this means that each user will get their own connection to the database because each user's session is a separate instance of that script. After your insert you can use mysql_insert_id() as the previous poster pointed out to get the id that was generated for the insert.

Answer 11

insert ....; select last_insert_id()