Transactions and mysql insert it
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 HanedaTel: 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]
Re: Transactions and mysql insert it
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 HanedaTel: 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]
Re: Transactions and mysql insert it
on 7/22/04 3:54 PM, Justin Swanhart at [EMAIL PROTECTED] wrote: 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 Ok, so there can be gaps, but there would never be collisions if id's? -- - Scott HanedaTel: 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]
