MySQL doesn't guarantee that there will be no gaps in sequence values. Assigment of the id is always atomic because innodb uses an AUTO_INC lock that lasts for the time of the insert, not the life of the transaction.
lets say your highest order number is 10 transaction begins for client 1 insert into orders (...) values (...) mysql_insert_id = 11 ... user adds stuff to order ... aborts order, transaction rolls back at the same time transaction begins for client 2 insert into orders (...) values (...) mysql_insert_id = 12 ... adds some stuff ... commits order there will be a row with an id of 10 and a row with an id of 12 in your database.. id 11 was rolled back and is gone this is all documented here: http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_column.html --- Scott Haneda <[EMAIL PROTECTED]> wrote: > I have been pulling my hair out trying to get a > solution to something, > assuming idiotically that in a transaction scenario > I would not be able to > get the insert it back out. It seems to work, I am > wondering how and if it > is reliable. > > Give the scenario where I have 2 inserts I want to > make, since I can not > seem to figure out how to make 2 inserts in one > statement, I will make 2, > with the condition that the second one needs to know > the insert id. > > I just don't see how mysql can know the insert id in > a transaction > situation, I am assumeing that mysql does not > actually insert anything at > all untill it sees the COMMIT, so how does it know > the insert id at all, the > records have not been inserted yet? Is this > reliable? > > Here is some pseudo code that shows what I am doing > and commetns where I am > confused: > > mysqlQuery(tConn, "START TRANSACTION"); > > repeat 1000 times > mysqlQuery(tConn, "INSERT into zaddress > SET user_id = '123', > address = > '[EMAIL PROTECTED]'"); > > // How can mysql know about this ???? !!!! > tID = mysqlInsertID(tConn); > > mysqlQuery(tConn, "INSERT INTO zblacklist > SET user_id = '123', > id = " & tID & ", address = " > & tID); > end repeat; > > mysqlQuery(tConn, "COMMIT"); > -- > ------------------------------------------------------------- > Scott Haneda Tel: > 415.898.2602 > http://www.newgeo.com Fax: > 313.557.5052 > [EMAIL PROTECTED] Novato, > CA U.S.A. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
