MySQL Forums
Forum List  »  PHP

Insert with increment on non auto inc column atomic
Posted by: Frank Thomson
Date: December 03, 2014 06:45PM

Hi,
I have a table with the following structure
id = auto increment
date = we store the date and time for reference
tgn = transactionn group number
field1 = data field
field2 =
field3
etc

This table is used to record user data on a number of 'transactions' (for want of a better word). Each entry ofcourse has a unique id that suto increments however the tgn is the same for all entries made that are associated with the one operation.
One type of transaction involves removing parts from inventory assembling them into another part and then putting the completed assembly back into inventory.
All of these would have the same tgn (transaction group number)

When I issue a new tgn, I do this:
$query = "INSERT INTO transactionstbl(tgn) SELECT max(tgn) + 1 FROM transactionstbl";
$result = mysql_query($query);
if (!($result)) { echo "error tgn did not work. query= $query";};

$id = mysql_insert_id(); // gets the auto increment row id
Then requery the table to get the tgn for this id


THE PROBLEM is that this is not atomic. With more users, the potential exists that inbetween sending the first query to mysql and retrieving the id a second user can initiate a change to the table and we get back a different id.

What I would like to increment a non unique column and recover the unique id all in one atomic operation.

And one little hiccup, the table is myisam and so I can't to a normal mysql transaction on it.

Options: ReplyQuote


Subject
Written By
Posted
Insert with increment on non auto inc column atomic
December 03, 2014 06:45PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.